Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

asked 15 Feb '18, 16:00

Miro%20Marek's gravatar image

Miro Marek
1214512
accept rate: 0%

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';
(16 Feb '18, 07:15) Breck Carter
Replies hidden

What version do you use, v16 or v17?

(16 Feb '18, 09:00) Volker Barth

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.

(16 Feb '18, 09:33) Miro Marek

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

(16 Feb '18, 11:12) Volker Barth

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?

permanent link

answered 16 Feb '18, 11:30

PhilippeBertrand%20_SAP_'s gravatar image

PhilippeBert...
1.8k42139
accept rate: 22%

edited 20 Feb '18, 16:40

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...
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×16

question asked: 15 Feb '18, 16:00

question was seen: 1,357 times

last updated: 28 Feb '18, 15:21