cancel
Showing results for 
Search instead for 
Did you mean: 

v12 isolation levels and row locking

Former Member
8,265

Hi, I am working on testing a migration from sql anywhere 8.0.3 to 12.0.1.3152. The row level locking does not seem to work quite the same. I have a situation where I will have one connection working on a table performing inserts and/or updates in a transaction. In parallel, a second connection may be opened in another thread and attempt to do a SELECT MAX(...) on the table being updated in the first thread. My isolation level is set to zero. This approach works fine in 8.0.2 but in v12 I get a row locked error on the attempt to SELECT MAX(...) from the table being worked. I've pasted the exception at the bottom of this question.

I reviewed the isolation level definitions and from what I understand I believe this should be allowed - iso level 0 should allow dirty reads. "Read permitted on row with or without write lock". Am I interpreting this correctly, and if so could this be a bug either in the engine or the JDBC driver?

com.sybase.jdbc3.jdbc.SybSQLException: SQL Anywhere Error -210: User 'newzware' has the row in 'subscrip' locked
    at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
    at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
    at com.sybase.jdbc3.tds.TdsResultSet.nextResult(Unknown Source)
    at com.sybase.jdbc3.tds.TdsResultSet.next(Unknown Source)
    at com.sybase.jdbc3.jdbc.SybResultSet.next(Unknown Source)
    at org.jboss.resource.adapter.jdbc.WrappedResultSet.next(WrappedResultSet.java:1196)

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member

If you're using jConnect over TDS, the server modifies connection options under-the-covers to try to mimic ASE behaviour as much as possible. One of the settings that is modified is the connection's isolation level, which is set to 1. Unless you do something explicit in your application to reset the isolation level back to 0, you're running at 1 and can hence block on write locks.

That said, there are minor differences between v8 and v12 with respect to locking, due to changes in the underlying execution model in the server.

Former Member
0 Kudos

Ah thanks to all for the comments. That explains it. I AM using JConnect over TDS via a connection pool, and overriding the login procedure does solve the issue. My follow-up question, then, is "Is it possible to set the isolation level via the JDBC URL?" - I'll do the stored procedure override if I must but I'd prefer something a bit more clear and explicit

Former Member
0 Kudos

It is not possible to override the changes to these options via the JDBC URL.

I would suggest that unless you have a compelling reason to use jConnect, you should use the iAnywhere JDBC driver, which is a Type 2 driver:

http://iablog.sybase.com/paulley/2009/10/differences-between-jconnect-and-the-ianywhere-jdbc-driver-...

and does not use TDS but instead uses the native SQL Anywhere wire protocol.

VolkerBarth
Contributor
0 Kudos

Glenn, is it possible to use the Connection.setTransactionIsolation() with jConnect, too?

For the SQL Anywhere JDBC drivers, it's documented here.

Former Member
0 Kudos

As far as I'm aware, yes. There is no need to restrict setting these options within a stored procedure; from the application, one can issue "SET OPTION" statements using the appropriate JDBC API call to explicitly set the required options.

Breck_Carter
Participant
0 Kudos

Did you change the way you are connecting at the same time as upgrading? e.g., from ODBC to JDBC

It's not really under the covers, and it is not magic...

If connection_property('CommProtocol') used to be 'CmdSeq' (which is good) and now it is = 'TDS' (bad), the sp_tsql_environment stored procedure may be doing you great harm... IMO that stored procedure is evil evil evil evil evil (that's five evil's, the maximum possible score).

Here's what happens by default, if you haven't changed anything else:

The default setting

SET OPTION PUBLIC.login_procedure = 'sp_login_environment';

causes the following stored procedure to be called for every connection:

create procedure dbo.sp_login_environment()
begin
  if connection_property('CommProtocol') = 'TDS' then
    call dbo.sp_tsql_environment()
  end if
end

If 'TDS' is being used then the following procedure is called:

create procedure dbo.sp_tsql_environment()
begin
  if db_property('IQStore') = 'OFF' then
    -- ASA datastore
    set temporary option Automatic_timestamp = 'ON'
  end if;
  set temporary option Ansinull = 'OFF';
  set temporary option Tsql_variables = 'ON';
  set temporary option Ansi_blanks = 'ON';
  set temporary option Tsql_hex_constant = '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 Float_as_double = 'ON';
  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';
  set temporary option Close_on_endtrans = 'OFF'
end

The easiest way to stop all this foolishness is to turn it off... remember that NONE OF THAT CRAP is done for 'CmdSeq' connections, and if you want the same behavior now then the following will be just fine for you... just because you are using TDS doesn't mean your application is SUPPOSED to work incorrectly:

SET OPTION PUBLIC.login_procedure = NULL; 

Another option is to slightly modify the evil-doer to JUST fix the stupid setting for isolation level:

alter procedure dbo.sp_tsql_environment()
begin
  if db_property('IQStore') = 'OFF' then
    -- ASA datastore
    set temporary option Automatic_timestamp = 'ON'
  end if;
  set temporary option Ansinull = 'OFF';
  set temporary option Tsql_variables = 'ON';
  set temporary option Ansi_blanks = 'ON';
  set temporary option Tsql_hex_constant = '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 Float_as_double = 'ON';
  set temporary option Isolation_level = '0';
  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';
  set temporary option Close_on_endtrans = 'OFF'
end

Some people write a different procedure to replace sp_login_environment, and change the value of login_procedure... a bunch of make-work if you ask me but I'll show you how if you want 🙂

VolkerBarth
Contributor
0 Kudos

I agree - but if one goes from ODBC to JDBC, choosing the SQL Anywhere instead of jConnect would be the easier option, methinks. And it would let you get rid of the TDS compatibility stuff completely.

VolkerBarth
Contributor
0 Kudos

As you are using jConnect (instead of the SQL Anywhere JDBC driver): Do you set the isolation level explicitly to 0? What does

select connection_property('isolation_level')

return?

AFAIK, jConnect uses special defaults values when connecting, among others a default isolation level of 1 - cf. the docs.

Besides that, I would agree with you on the behaviour you expect.

VolkerBarth
Contributor
0 Kudos

...sigh, Glenn was a some seconds faster...sometimes a serialization/pessimistic blocking were fine here, too:

"Gless is already writing an answer - do you really want to interfere?"

VolkerBarth
Contributor
0 Kudos

...and we know what the default button should be:)