Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
baja_yu
Starting Member
24 Posts |
 Posted - 2010-03-23 : 18:05:44
|
| Hi guys,I have a scenario like this. Two tables: Tab1, Tab2. I insert a new record into Tab1, then have a Select statement to grab the highest ID (the one just added in previous step), then add a value based on that ID to Tab2.What I need is a lock to prevent a scenario where, for example, User1 and User2 at the same time grab the same ID and try to insert identical values into Tab2. Or User1 grabs the ID from Tab1, but in the mean time User2 added a new record to Tab1 so User1 gets the wrong ID.I've been searching around and reading up on Locks, and TABLOCKX seems to be what I need. But how do I switch it on, and later switch the lock off (since the entire procedure goes through 2-3 DB transactions).P.S: Using MS SQL 2000. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-23 : 18:14:20
|
If your ID is an identity column then look here:http://msdn.microsoft.com/en-us/library/aa259185(SQL.80).aspx
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
baja_yu
Starting Member
24 Posts |
Posted - 2010-03-23 : 18:27:41
|
| ID is the identity column, but I have one more scenario to lock. Just one table:1. Get the highest ID2. Increment by one and do some calculations on it.3. Insert it back in the same table (in some other field)Without colission this works fine, because when the item is re-inserted the auto-incremented ID matches the calculation because I incremented the ID from step 1 before doing calculations.The problem here is if two users at the same time do step 1, they will both get the same ID, but when they do step 3, one of them will have a misscalculated value because his ID will be incremented by 2 not by 1. Or is there maybe a better way of achieving this than the method I'm using?Thanks for the reply. |
|
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2010-03-23 : 22:44:51
|
1. Get the highest ID2. Increment by one and do some calculations on it.3. Insert it back in the same table (in some other field)In this scenario, select statement is the first command, you should use with(updlock). This will lock the source (even with read) until the end transaction. So the user2 has to wait until the user1 finish his/her job. (some cases, it is better to use synchronize to control this from your application)ex:begin tran--select ... from table1 with(updlock)--calculation ..--insert into table1...commit tranFor your first scenario, using repeatable read isolation level. With this, the source of table1 will be locked until the end of transaction. User2 can not reach the select statement unless the user1 had done.ex:set transaction isolation level repeatable readbegin tran--insert into table1 ...--select ...--calculation ..--insert into table2...commit tranbaja_yu, lock is confusing (me too  ). So test it carefully before use. |
|
|
|
baja_yu
Starting Member
24 Posts |
Posted - 2010-03-24 : 07:46:21
|
| Thanks namman. I will give your suggestions a try. In the mean time I had the idea of creating a user function that will do scenario 1. CREATE FUNCTION GetData RETURNS varchar(256) ASBEGIN DECLARE @Return varchar(256) --INSERT INTO... --SELECT MAX --calculation --UPDATE RETURN (@Return)ENDBut I'm not sure if a function will be atomic (prevent collision). |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-03-24 : 07:59:39
|
| functions can't change data so..... it won't work anyway.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
baja_yu
Starting Member
24 Posts |
Posted - 2010-03-24 : 08:38:27
|
| Oh... Then I guess I'll have to stick with Begin/Commit transaction and using locks. |
|
|
|
|
|
|
|
|
Need help with Locks