Sybase 12 to behave like Oracle and SQL Server 2014 – non blocking transaction read

lockingsybase

We utilize an ODBC connection so our code can access various databases – namely Oracle, Sybase 12 and SQL Server 2014.

NOTE : We have a table called which we use for locks. This table only ever has one record in it and it is either empty or populated with whom has the lock.

I have a scenario where whilst the application has an ODBC connection to Oracle or SQL Server 2014 the application runs fine, the test scenario being – two processes accessing the db – one doing a long running task the other doing short tasks. If I connect to Sybase and do the same activities one of the processes blocks (the short task one). I am guessing here that in Sybase the lock table is written to, locked grabbed, and released within a transaction and the other process is awaiting the commit\rollback on the transaction to use the table next.

Why does it work in SQL Server 2014 and Oracle but block in Sybase 12? What option needs to be ON\OFF in Sybase 12 to get the behavior of Oracle and SQL Server 2014?

Best Answer

I finally found the options of the database in Sybase Central and had a play with various options and I got it to work with turning ON "allow_snapshot_isolation" it was OFF by default.

Related Solutions

Sql-server – SQL Server – what isolation level for non-blocking select statements

In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.

This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.

Sql-server – SQL Server Transaction Timeout

Extending Mark's answer...

When a client timeout event occurs (.net CommandTimeout for example), the client sends an "ABORT" to SQL Server. SQL Server then simply abandons the query processing. No transaction is rolled back, no locks are released.

Now, the connection is returned to the connection pool, so it isn't closed on SQL Server. If this ever happens (via KILL or client reboot etc) then the transactions+locks will be cleared. Note that sp_reset_connection won't or doesn't clear them, even though it is advertised to do so

This detritus from the abort will block other processes.

The way to make SQL Server clear transactions+locks on client timeout (strictly, ABORT events) is to use SET XACT_ABORT ON.

You can verify this be opening 2 query windows in SSMS:

Window 1:

In menu Query..Query Options set a timeout of 5 seconds then run this

BEGIN TRAN
UPDATE sometable WITH (TABLOCKX) SET foo = foo WHERE 1 = 0;
WAITFOR DELAY '00:00:10' -- just has to be longer then timeout

Window 2, this will wait forever (or hit your timeout)

SELECT * FROM sometable

SET XACT_ABORT ON has interesting side effects too:

@@TRANCOUNT is set to zero on the implicit rollback but error 266 is suppressed (this happens if @@TRANCOUNT is different on entry and exit from a stored proc)
XACT_STATE will be -1 (it's "doomed")

The combination of this means that you can't use SAVEPOINTS (although, I can't recall exact behaviour) for partial commits/rollbacks. Which suits me

SO links on SET XACT_ABORT:

https://stackoverflow.com/a/1539686/27535 (me)
https://stackoverflow.com/a/919279/27535 (me)
https://stackoverflow.com/a/1150350/27535 (Remus Rusanu)

On nested stored procs:

https://stackoverflow.com/a/2074139/27535 (me)

On sp_reset_connection:

https://stackoverflow.com/a/2924456/27535

Related Question