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 |
thangnc
Starting Member
7 Posts |
Posted - 2013-04-21 : 21:23:55
|
Hi all!DECLARE @TestTable TABLE (id INT, StoreID int, ItemID int, VchDate date, DateNum int, Quanty float, QtyOnhand float)INSERT INTO @TestTableSELECT 1, 1, 1, '2013-03-04', 1, 10, 10 UNIONSELECT 2, 1, 1, '2013-03-04', 2, 10, 20 UNIONSELECT 3, 1, 1, '2013-03-05', 1, 10, 30 UNIONSELECT 4, 1, 1, '2013-03-05', 2, 10, 40 UNIONSELECT 5, 2, 1, '2013-03-04', 1, 20, 20 UNIONSELECT 6, 2, 1, '2013-03-04', 2, 20, 40 UNIONSELECT 7, 2, 1, '2013-03-04', 3, 20, 60 UNIONSELECT 8, 2, 1, '2013-03-06', 1, 20, 80 How to I Insert/ Update, Sql only lock Row by key (id , StoreID, ItemID).I can create 2 secssion by key(id , StoreID, ItemID) to update value TableThank! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
thangnc
Starting Member
7 Posts |
Posted - 2013-04-23 : 07:01:48
|
quote: Originally posted by visakh16 you can use ROWLOCK hint in updates and insertsseehttp://www.bennadel.com/blog/477-SQL-Server-NOLOCK-ROWLOCK-Directives-To-Improve-Performance.htm------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Thank visakh16!I'm sorry, my key(StoreID, ItemID). When update I want to lock rows same key. Example :<b>session 1:</b>Update @TestTable set Quanty = 20, QtyOnhand = 20 WHERE Id = 1 and StoreID =1 AND ItemID =1Update @TestTable set Quanty = 10, QtyOnhand = 30 WHERE Id = 2 and StoreID =1 AND ItemID =1Update @TestTable set Quanty = 10, QtyOnhand = 40 WHERE Id = 3 and StoreID =1 AND ItemID =1Update @TestTable set Quanty = 10, QtyOnhand = 50 WHERE Id = 4 and StoreID =1 AND ItemID =1<b>session 2:</b>Update @TestTable set Quanty = 10, QtyOnhand = 10 WHERE Id = 5 and StoreID =2 AND ItemID =1Update @TestTable set Quanty = 20, QtyOnhand = 30 WHERE Id = 6 and StoreID =2 AND ItemID =1Update @TestTable set Quanty = 20, QtyOnhand = 50 WHERE Id = 7 and StoreID =2 AND ItemID =1Update @TestTable set Quanty = 20, QtyOnhand = 70 WHERE Id = 8 and StoreID =2 AND ItemID =1<b>session 3:</b>Update @TestTable set Quanty = 30, QtyOnhand = 50 WHERE Id = 3 and StoreID =1 AND ItemID =1Update @TestTable set Quanty = 10, QtyOnhand = 60 WHERE Id = 4 and StoreID =1 AND ItemID =1when exec , I want to session 1 and 2 run same time and session 3 wait 1 complated |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-25 : 06:54:03
|
try using ROWLOCK hint as suggested------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|