Row Level Locking

[Miners47]

Background
I have a table (Table_Keys) that contains the next unique key to use for certain tables. I am developing an application for a mutli-user environment and there is a high level of probability that this table will get accessed several times (1000's of times) throughout the day.

I have written an algorithm that gets the next key from the table and then writes the next available key back after incrementing the key. The key is alpha-numeric. To ensure data integrity, I need this algorithm to have full control of the row it is accessing in the Table_Keys table. This "lock" should not be released until the next key has been written back to the table.

Question
If two applications submit a SELECT FOR UPDATE against the Table_Keys table, will transaction 2 wait to retrieve the row it is accessing until transaction 1 is completely finished with its read and write execution?

 

[fluteplr]

It should.

However I have a suggestion.

I use a similar method, however what I do is increment the key first, then send it back to the calling procedure, that way I do not have to worry about a locks being held. the only drawback is that in method you can end up skipping keys, if a procedure requests a key but then does not end up writing the record to the table. In my case that skipped key does not matter but it may be an issue for you.

 

[royjimenez]

I have done this


update mytable
set mykey = mykey + 1
where ...

select mykey
into @mykey
from mytable
...



if u use the update u will lock it.

Regards

The life is too short to cry and long enough to try it... God bless us.