PDA

View Full Version : transactions and locks

cfsavant
02-09-02, 14:32
I have in excerpt from some db manual which explains the pro's and con's of different methods of primary key generation. The particular method explained uses a separate table named next_key to hold the next value for the primary key.
I have three questions about the code in the excerpt below.

1. why is it that the update increments the next key in the next_key table and then the select increments it as well.
2. In this approach, isn't it possible that after the update occurs, the lock on the row is released and when the select statement below executes some other process already has a lock on the table?

The following is an excerpt from an article from Sybase found at:
http://www.wallaceis.com/resource/article/Sybase/Surrogate%20Primary%20Key%20Generation.htm

---------------------------------------------------------------
Next Key Table
A more versatile approach is to introduce a separate table to store the next key value.

1> begin tran
2> declare @next_key
3> update next_key set next_key = next_key + 1
4> select @next_key = next_key + 1
5> from next_key
6> insert into data_table (key_column, ...)
7> values (@next_key, ...)
8> commit tran
9> go

The SQL is atomic and uniqueness is guaranteed. Notice that the update statement precedes the select statement. The update first acquires an exclusive lock on the table ensuring that no other processes can obtain a next key value from the table.
In the above example, the transaction includes both the generation of the key and the insert into the data table. The transaction is completely single-threaded. Consequently, there are no gaps in the sequences of key values inserted into the data table.
MattR
02-10-02, 12:19
It is either a bug or they are assuming something that we don't understand... What I think they're doing is assuming that you already have a row with the next_val + 1 in your table.

That is to say,
next_val contains ( 1 )
your table id values contain
(1, 2)

So that way your next insert will produce:
next_val( 2 )
your table id values
(1, 2, 3)

So you could remove the +1 in the select statement and ignore that. I think it is due to the way they were counting.
cfsavant
02-12-02, 13:56
I wrote code to test this out and sure enough. The select statement does need to increment again because the changes that the update made are not yet commited. That was my suspion.

For example, if the table contained a value of 1. The update increments to 2, but that change is not committed yet so the select gets a 1 back. Then increments it to 2. When the transaction has completed. The changes are committed and the value is now 2.


As I understand, the update would put an exclusive lock on the table; when the update is finished, the lock is released; Then depending on the isolation level, the select will then put a shared lock.

So my question still lies. Does the exclusive lock that the update puts on the table last for the entire transaction or only for the update? ( or worded differently) Is the exclusive lock still on the table when the select is run?