I have a problem with a transaction isolation or concurrent atomic select+update.
SQL Server, C++ application, OTL/ODBC, and I can't use any stored procedures.
Here is the thing. The task is to update some field to a new value, and get (select) this value (it must be the last value, from update). However, there are several applications, which must do it concurrently.
My last code does something like:
SET IMPLICIT_TRANSACTIONS OFF SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE tbl WITH (UPDLOCK, HOLDLOCK) SET value = value + 1 WHERE name = some_name; SELECT value FROM tbl WITH (SERIALIZABLE) WHERE name = some_name; COMMIT TRAN
If we have two concurrent applications, which do the same thing (above) in rate 50-100 per sec, it leads to (~ 0.2% cases):
OTL_EXCEPTION: 1205 : [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.[ stm_text: update ...
SNAPSHOT for DB:
SNAPSHOT ISOLATION is always enabled in this database.
Also there is the same results for
SERIALIZABLE isolation level.
Is it possible to handle it? Currently, I have only one way - re-run query.