All geek questions in one place

Should tablockx be specified in every session?

I have two test transactions in two sessions, respectively. Assuming that these two transactions will be executed simultaneously. I am trying to make one transaction insert the account number correctly after the execution of another transaction. No duplicates. I did it as shown below. But if I delete from (tablockx) in session 2, they will no longer work. I checked in the line of the book, but did not answer. Anyone help? Serializable will not work as the two SELECTs want to be exclusive to each other here. Thanks.

In session 1:

begin transaction declare @i int select @i=MAX(InvNumber) from Invoice with(tablockx) where LocName='A' waitfor delay '00:00:10' set @ i=@i +1 insert into Invoice values('A',@i); commit

In session 2:

 begin transaction declare @i int select @i=MAX(InvNumber) from Invoice with(tablockx) where LocName='A' set @ i=@i +1 insert into Invoice values('A',@i); commit
+4
source share
1 answer

This will work, but also completely blocks all access to the table.

If you do WITH(UPDLOCK, HOLDLOCK) , you can block a lower level of detail (than a table) and a mode (than an exclusive).

HOLDLOCK gives serializable semantics, so you can simply lock the range at the top of the index (if you have one on LocName,InvNumber ).

UPDLOCK ensures that two simultaneous transactions cannot contain the same lock, but, unlike the exclusive one, they do not block other (normal) readers that do not use the prompt.

 BEGIN TRANSACTION DECLARE @i INT SELECT @i = MAX(InvNumber) FROM Invoice WITH(UPDLOCK, HOLDLOCK) WHERE LocName = 'A' WAITFOR delay '00:00:10' SET @ i=@i + 1 INSERT INTO Invoice VALUES ('A', @i); COMMIT

Alternatively, you can simply use sp_getapplock to serialize access to this code.

+1
source

Source: https://habr.com/ru/post/1499258/

More articles:


All Articles