3

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.

marc_s
  • 8,932
  • 6
  • 45
  • 51
Boris
  • 131
  • 2
  • 4
    Use the OUTPUT clause on your UPDATE command. Your client code would still open the query as a dataset, since the UPDATE will now return one. –  May 16 '14 at 17:11
  • afrazier, thank you for the comment. I'm going to implement UPDATE with OUTPUT INTO tmp-table, which is connection independent, I suppose. –  May 16 '14 at 17:27
  • 1
    You could just use OUTPUT. No need for a temp table, unless you want to reuse the results in further queries/statements. – ypercubeᵀᴹ May 16 '14 at 17:32
  • I tried to use OUTPUT without INTO (to intercept as a select result) and failed. Ok, I will do one more try. Thank you. – Boris May 16 '14 at 17:40
  • 1
    It should be UPDATE tbl SET value = value+1 OUTPUT inserted.value WHERE name = some_name; if I'm not mistaken. – ypercubeᵀᴹ May 16 '14 at 17:53
  • Guys, there were some problems with OTL + SQL Server, but your solution works well. Thank you very much indeed! – Boris May 19 '14 at 10:53

0 Answers0