Hi, is there possibility to set isolation level for OData producer to snapshot? The isolation level parameter in ODBC is ignored and level is always set to 1. This use a read locks and we get deadlocks on concurrent update operations. thanks Miro |
OData producer checks and sets its isolation level depending on the request. For example, updates and deletes use "repeatable read". The default is "read committed". At present there is no way to request a minimum isolation level. If it is causing deadlocks, we would consider that a problem. OData requests are atomic in that they always end in either a commit or a rollback. Can you give me a simple repro or a more detailed explanation on how the deadlock is occurring? Any more on this issue? Please feel free to contact me via email if you are not comfortable posting a more details here.
(28 Feb '18, 15:19)
PhilippeBert...
firstname dot lastname at sap dot com
(28 Feb '18, 15:21)
PhilippeBert...
|
This is just a guess: Do you specify DSN=, or use a DSN-less connection string? If the former, try the latter with explicit Driver= and IsolationLevel= parameters in the string.
Another possibility is your connection is going through the nightmare called sp_tsql_environment which does a SET TEMPORARY OPTION isolation_level='1';
If so, then take control by coding your own login_procedure that does NOT call sp_tsql_environment.
Full story:
The default login_procedure option is this: SET OPTION PUBLIC.login_procedure = 'sp_login_environment';
That forces all your connections to call this...
Some of those connections then call this
evilprocedure:If you NEVER want that to happen, try this:
Or substitute your own code:
What version do you use, v16 or v17?
first thanks for your help. To check and set the isolation level in login procedure was also my idea yesterday before I made this request. OData connection comes with level 0. I set this to snapshot in my login procedure. But after this it has level 2. So this has to be done directly in OData. I´m using Server 17 but OData running on separate Server with DBOSRV 16.
Do you set an isolation level within the OData server's DbConnectionString entry?