Table Locking
-
I'm wanting to have a unique reference number in my table as follows:
update TableX set Reference = (select max(Reference)+1 from TableX) where id = @MyId
Obviously to ensure it is unique I need to lock the table...
update TableX with (tablock) set Reference = (select max(Reference)+1 from TableX) where id = @MyId
However this is one of those things I've never quite worked out, looking at the execution plan there is a whole load of queries, find the record to update, find the value to insert etc etc.
So my question is, does the lock cover the entire process? And hence is my SQL above always going to ensure I have a unique reference?
Thanks
-
Looks like that would work, although I'd rather use a TABLOCKX to be certain. I'd also write it out slightly differently.
BEGIN TRAN
DECLARE @NextReference int
--taking out the exclusive lock here ensures that the table remains locked from the moment you do the --select. Also, it's better to do it once here than embedding it within the update statement.
SELECT @NextReference = MAX (Reference)+1 FROM TableX WITH (TABLOCKX)
UPDATE TableX SET Reference= @NextReference WHERE id= @@MyId
COMMIT TRAN
-
Thanks - I appreciate it.
-
Ohh.. if what you are trying to achive is something like the "auto-number" feature in Access, then you really want an IDENTITY column in SQL Server.
Using a Max+1 will almost certainly end in duplicate values at some point (a good stress test will quickly show this).
An IDENTITY column will result in each row having a new value, and SQL Server won't issue the same number to two rows, no matter how many people are inserting to the table at once.
J
Julian Kuiters
juliankuiters.id.au -
Can't say I've ever used Access... cut my teeth on SQL Server
I guess you know that you can't have more than one identity column in a table? And that identity columns are prone to having gaps in them?
For these and many other reasons it is frequently necessary to calculate them oneself, and I think if you inspect the code provided by the other respondant you will see that it will nicely avoids duplicates by simply locking the entire table with an exclusive lock.
-
Though, using SELECT MAX() to find the next number isn't very scalable, and it's really aggressive on concurrency. If you get any significant number of rows in the table, you're going to see some heavy performance hits with this method, and the code will in that respect then be broken. In this case, the table is also locked on purpose, so any other user will have to wait until the entire transaction is done.
If you decide to roll your own counter, then it's better to do it in a structured way, with a designated counter-table holding the value for each table/column or whatever you need.
I don't recommend using SELECT MAX() at all for this purpose due to it's poor scalability.
On the note of identity - it's not as 'flaky' in SS 2000 as in earlier versions of SQL Server.
..just my .02's anyways
/Kenneth
-
You'll get no arguement from me on that one
I needed a quick fix, and one that would have minimal side effects otherwise I would have indeed placed it in a seperate table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply