Transaction/ Lock Question
-
Hi,
I have a table that has among others, two columns that make up a unique index on a table. The first: ParentID, is the foreign key from another table, the second: ItemID, is a sequential counter for that ParentID. So, I will have the following in my table:
ParentID ItemID
1 1
1 2
1 3
2 1
2 2
etc.
When I want to add a new row to the table, I need to calculate the new ItemID to insert into the table. When this is calculated, I want to ensure that no other records are inserted in the table until I insert mine. My stored proc currently does roughly the following:
BEGIN TRAN...
Set @ItemID = (SELECT Max(ItemID) From Table1 (TABLOCKX) WHERE ParentID..... + 1
INSERT Table1 ......
If No Error
COMMIT TRAN
else
ROLLBACK TRAN
The sp works but I don't know how the lock works: does it last until the transaction is committed/ rolled back or just for the duration of the select? If it doesn't last for the duration of the transaction, how do I make it last?
Is this the best way to tackle the problem? Is there a way of calculating the next ID during the execution of the INSERT statement e.g. INSERT Table1 (A, B, C) SELECT Max(ItemID) + 1 AS E1, ... FROM Table1
Thanks in advance.
-
wow! It will work because you are blocking ALL the Table1. A HOLDLOCK would be enough. Your COMMIT/ROLLBACK will release all locks.
For my personal taste, I like identity cols, even for cols like ItemId. It wont start at 1 (and maybe it wont be consecutive), but your app for sure, should not depend on this. It wolud be automatic.
In fact, I really really prefer not-business -rules based keys. If the business rules change... we are in trouble.
So, big fun of identity for PK around here.
-
This code does it in one pass.
declare @InsertParentID int
set @InsertParentID = 2 --or whatever value you need to insert
insert into table1(parentID, itemID)
select @InsertParentID, (select max(t1.itemID) + 1 from table1 t1 where t1.parentID = @InsertParentID)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks guys,
Phil's method works. It seems much more sensible that putting a lock on the table. Thanks for the lock info as well.
-
Just one last question!!
When using Phil's solution, I need to return the Max value to my code. Is there any way to assign the Max value to an output parameter or do I need to do a select on the table afterwards to get the value?
-
A HOLDLOCK would be enough.... No it would not, HOLDLOCK will not stop another user using HOLDLOCK
I prefer to use UPDLOCK in these situations
When using Phil's solution, I need to return the Max value to my code. Is there any way to assign the Max value to an output parameter or do I need to do a select on the table afterwards to get the value? You would have to do a select afterwards to find the ID used
I use your first solution (with UPDLOCK instead) for this type of situation
Far away is close at hand in the images of elsewhere.
Anon. -
Sorry my mistake, you need both in the first case!
A HOLDLOCK (also know as SERIALIZABLE) is a proper lock. And UPDLOCK is also correct; you also need an Exclusive Lock, (i.e a UPDLOCK).
The SELECT is executed at a SERIALIZABLE isolation level with HOLDLOCK, so the result set gets a range lock preventing any changes. Another way to see this is: ‘Keep my select constant while the transaction is un-committed’. The object: no one else adds a row for the same parent.
UPDATELOCK will not prevent modifying the result set, just others from reading the actual set. UPDATELOCK will work only if, by good luck, is blocking the same page that may be modified to change the set, but there is always a chance to insert a new row for the same parent.
So you need both for the first case! Said that, the best simplest solution (so the best one) would really be:
BEGIN TRAN
insert into table1(parentID, itemID)
select @InsertParentID, max(itemID) + 1 from table1 (HOLDLOCK) where parentID = @InsertParentID
select @ItemId=max(itemID) from table1 where parentID = @InsertParentID
COMMIT...
Or...
The other proper way, should be:
BEGIN TRAN...
Set @ItemID = (SELECT Max(ItemID) From Table1 (HOLDLOCK,UPDLOCK) WHERE = @InsertParentID
INSERT Table1 ......
If No Error
COMMIT TRAN
else
ROLLBACK TRAN
-
May I suggest you use a separate table to account for the IDs.
No lock will be needed watsoever!!!
Cheers!
* Noel -
That depends....
Unless you use Antares magic bit of code you will still need a transaction for the SELECT/UPDATE and the lock will be implicit anyway
BTW I use this method for some ID's such as Invoice Numbers
Far away is close at hand in the images of elsewhere.
Anon. -
David ,you lost me with Antares magic bit of code. Do you have a link?
Mike
-
Would have to search for it, Antares mentioned it on several posts a while ago and it has to do with controlling ID's making sure only one user can get an ID at any one time to avoid duplicates.
So, assume we have a table
CREATE TABLE ControlTable (
ID int
)
One way to get the next number would be
DECLARE @NextID int
BEGIN TRAN
UPDATE ControlTable SET ID = ID + 1
SELECT @NextID = ID FROM ControlTable
INSERT INTO
.....
COMMIT TRAN
However you can get the next number and update all in one statement (without the need of a transaction) with the assurance on no other user getting the same number by
DECLARE @NextNo int
UPDATE ControlRecord SET @NextID = ID = ID + 1
INSERT INTO
.....
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David, I now remember the post, 2 cups of coffee and your information did the trick
Mike
-
Apparently you just answered your own question.
I have used this method for years and with a more general Method:
CREATE TABLE ControlTable (
TableName
ID int
)
-- This bit can be coded in a stored Proc named something like p_get_next_ids
DECLARE @NextNo int, @AmountToReserve int, @TableName varchar(128)
set @AmountToReserve = 1 , @TableName = 'Mine' -- just for example purposes
UPDATE ControlRecord SET @NextID = ID = ID + @AmountToReserve where TableName = @TableName
One less thing to worry about
insert into ...
* Noel
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply