Locking question
-
Hi,
I have to take the latest highest value from a control table that is used to assign new unique id's acorss several tables. I have to do the following:
--1 get the next highest ID
Select @last_assigned_id = last_assigned_id
from MyControlTable --this table only has 1 row
begin tran
--2 Update table 1 with next ID
select @last_assigned_id = @last_assigned_id +1
Insert into MyTable1 (ID1) values(@last_assigned_id)
--2 Update table 1 with next ID
select @last_assigned_id = @last_assigned_id +1
Insert into MyTable2 (ID2) values(@last_assigned_id)
--3 update MyControlTable with last assigned id
update MyControlTable set last_assigned_id = @last_assigned_id
commit
What would be the best way to lock the table MyControlTable so that other processes may not select or update it when this script is running.
Any advice very welcome.
Regards,
Eamon
-
The select can have the nolock hin in it. The transaction can be read commited. Any more suggestion read for isolation levels in BOL or the below link
http://www.expresscomputeronline.com/20040426/techspace01.shtml
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Set trigger FOR UPDATE on MyControlTable to perform updates on other tables.
Use value from table "inserted" instead of @last_assigned_id.
And because trigger is a natural tansaction you don't need to declare it.
P.S. If someone would ask my suggestion I would say the whole idea of such updates sucks.
It does not look like relational database at all.
_____________
Code for TallyGenerator -
>>What would be the best way to lock the table MyControlTable so that other processes may not select or update it when this script is running.<<<
Use an UPDATE statement (with rowlock) to *read* LastID into a local variable.
* Noel -
I like Sergiy's solution. Just to add in another option for you, you could use an UPDLOCK hint on your first SELECT from the MyControlTable.
-
Thanks Guys for all the suggestions.
I have went for the following and under the circumstances this works fine.
The table gets locked long enough for my update to take place and doesn't hold up other processes too long.
I'm using the TABLOCKX hint and placing the initial SELECT within the transaction.
begin tran
--1 get the next highest ID
Select @last_assigned_id = last_assigned_id
from MyControlTable (TABLOCKX)--this table only has 1 row
--2 Update table 1 with next ID
select @last_assigned_id = @last_assigned_id +1
Insert into MyTable1 (ID1) values(@last_assigned_id)
--2 Update table 1 with next ID
select @last_assigned_id = @last_assigned_id +1
Insert into MyTable2 (ID2) values(@last_assigned_id)
--3 update MyControlTable with last assigned id
update MyControlTable set last_assigned_id = @last_assigned_id
commit
This works for me
Cheers,
Eamon
-
Heh... we had something similar that a 3rd party built into their part of our database... 640 average deadlocks per day with spikes to 4,000
. Like Serqiy says, this method "sucks" and doesn't resemble a database at all... Serqiy's trigger method is the closest you'll come for absolute prevention of deadlocks using this oddball requirement.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I guess I should explain the other reasons why this is gonna "suck" and why I don't think this resembles a relational database... you're using RBAR (pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row")... what in the world do you think is going to happen when you need to insert more than 1 row or you have more than one user making the same request?
If you absolutely must have synchronus ID's on table 1 and 2, then make the ID on table 1 an identity column and put a trigger on it to do inserts on table 2 based on the INSERTED table (very much like Serqiy suggested). This "control" table smacks of a "sequence" table (use Oracle if you want to use sequences
) and it WILL cause a "hotspot" and it WILL cause deadlocks.There is a method for getting the "NextID" from such a table without causing deadlocks, but then you need to write code everywhere to get the NextID and use it (not to mention you need to program and use an increment if you insert more than 1 row or resort to a bloody slow loop). And, the only time it won't cause a deadlock is if you use that special method outside of any other transaction. The use of a "control" or "sequence" table is absolutely one of the very worst things you could do to a database and to your developers... your developers will hate you for it and your DBA's will be considering your untimely demise
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Just out of curiousity, assuming you have no way to avoid doing this, is there a reason you can't do an insert into the sequence table and store scope_identity into a variable, all outside of your transaction, and use that value when you insert the actual record? Hotspot issues are possible, but that's going to likely be true no matter how you tackle this.
I'm not seeing deadlock issues off the top of my head, but I don't do inserts like this, so might be missing something. If you do the insert/scope_identity outside of the transaction, then even if you do a rollback of the trans, you just skip that number and move on. I didn't see a requirement for them to be incremental with no gaps, so I'm not seeing a downside (other than the downside of doing this at all).
-
>>is there a reason you can't do an insert into the sequence table and store scope_identity into a variable
Yes... the sequence table only has one row according to the OP... so you can't use Scope_Identity because there won't be an IDENTITY column.
The way to do this is to drop the concept of a sequence table, altogether, and put an IDENTITY column in Table1 with a trigger to "replicate" to Table2. (That replication is what really bugs me... even an audit table shouldn't record inserts because that original data is available... audit tables should only record changes to the original data).
Also, (even though it can't be used on a single row sequence table) if you "store Scope_Identity into a variable", whether it be inside a transaction or outside, it's still RBAR. Granted, that is how most GUI's work but cleanup and load scripts that add multiple new rows should not be relagated to doing it a single row at a time. Nor should you have to calculate an increment and all that goes with it (especially creating multiple sequential IDs) when adding multiple rows. IDENTITY columns do that job so much better.
I agree... I'm not worried about a "no gaps" situation (and no one should). This updated scenario may cause fewer (maybe none, if we're lucky, but it's still not the proper way to update a scenario table if you have to use one) deadlocks because the sequence table is selected from before the update and it's done with a table lock (applogies, was still looking at the original post and didn't read the update) so no one else can get in, but why build a sequence table when IDENTITY columns are (should be) available?
By the way, in SQL Server, the proper way to update a sequence table is using the proprietary UPDATE @variable = columnname = columnname+@increment ... guaranteed no deadlocks if not included in a multi-step transaction and greatly reduces the "hotspot"... and because the table and the variable are updated simultaneously, there is no need for an explicit transaction... the data will be new and the inserts to Table1 and Table2 will be new, and if the keys are right, is guaranteed to work so long as the tables are viable. Still, the use of a sequence table is just so wrong in SQL Server because IDENTITY is available (3rd party solutions not-with-standing).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Ah, I missed that part. I saw the line "I have to take the latest highest value from a control table...", but missed the inline comment where he states "this table only has 1 row". In that case, I agree with you completely.
-
Heh... appreciate that, David... now, all I have to do is convince Eamon as to what a terrible mistake using that type of sequence table is
Eamon, don't do it... don't use a "sequence table" in SQL Server... it's just not the right thing to do. World of hurt coming up for everyone if you do.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I'm guessing that Eamon was given the requirement that identifiers had to be unique throughout a database.
On one of my contracts, I was given such a requirement, because someone had read something somewhere about how great it was, so they decided we had to do it. I simply retained my identity columns as they were, but added a GUID column to each table. I never joined on the GUID, nor used it for any purpose whatsoever, other than to prove that each row in each table had a value that was indeed unique across the database. Once that condition was satisfied, no one ever asked for it to be taken advantage of, reported on, or anything else, so it just sort of sat there in the table.
I hated doing it, but it solved the "problem" without introducing any headaches, and other than the effects of the additional column, was fairly benign.
If that's not possible, and if the unique identifiers have to be numeric, another easily implemented option would be different seed values for each table, giving each table a fixed block of values. With the bigint datatype, you could assign blocks large enough to cover just about any needs, without risk of a table's values overlapping some other table's values. To do it programatically, you could do something like set the seed to be the objectid * 10 billion (or 100 billion, a trillion, etc.), for instance, if none of your tables would ever surpass that many rows.
-
Well I have to *Differ* with Jeff statements that IDENTITY is "always" the solution for these cases. If you have to deal with replication you will learn to "hate" identity columns.
In terms of Hotspot the UPDATE statement with a varible solves the problem in 2000 and the OUTPUT clause of it in 2005 does away with even the "trigger" overhead.
SEQUENCE Tables *do* have a place even in MS SQL SERVER
* Noel -
Just curious, Noeld... how many databases have you built with a Sequence table of this nature?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply