cancel
Showing results for 
Search instead for 
Did you mean: 

OData producer use read locks

Miro_Marek
Explorer
1,730

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

Breck_Carter
Participant
0 Kudos

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...

ALTER PROCEDURE "dbo"."sp_login_environment"()
begin
  if "connection_property"('CommProtocol') = 'TDS' then
    call "dbo"."sp_tsql_environment"()
  end if
end

Some of those connections then call this evil procedure:

ALTER PROCEDURE "dbo"."sp_tsql_environment"()
begin
  if "db_property"('IQStore') = 'Off' then
    -- SQL Anywhere datastore
    set temporary option "close_on_endtrans" = 'OFF'
  end if;
  set temporary option "ansinull" = 'OFF';
  set temporary option "tsql_variables" = 'ON';
  set temporary option "ansi_blanks" = 'ON';
  set temporary option "chained" = 'OFF';
  set temporary option "quoted_identifier" = 'OFF';
  set temporary option "allow_nulls_by_default" = 'OFF';
  set temporary option "on_tsql_error" = 'CONTINUE';
  set temporary option "isolation_level" = '1';
  set temporary option "date_format" = 'YYYY-MM-DD';
  set temporary option "timestamp_format" = 'YYYY-MM-DD HH:NN:SS.SSS';
  set temporary option "time_format" = 'HH:NN:SS.SSS';
  set temporary option "date_order" = 'MDY';
  set temporary option "escape_character" = 'OFF'
end

If you NEVER want that to happen, try this:

SET OPTION PUBLIC.LOGIN_PROCEDURE = '';

Or substitute your own code:

CREATE PROCEDURE DBA.my_login_procedure()
BEGIN
  -- do whatever you want
END;

SET OPTION PUBLIC.LOGIN_PROCEDURE = 'DBA.my_login_procedure';
VolkerBarth
Contributor
0 Kudos

What version do you use, v16 or v17?

Miro_Marek
Explorer
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

Do you set an isolation level within the OData server's DbConnectionString entry?

Accepted Solutions (0)

Answers (1)

Answers (1)

philippefbertrand
Participant

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?

philippefbertrand
Participant
0 Kudos

Any more on this issue? Please feel free to contact me via email if you are not comfortable posting a more details here.

philippefbertrand
Participant
0 Kudos

firstname dot lastname at sap dot com