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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to Lock Update, Insert by Key

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 @TestTable
SELECT 1, 1, 1, '2013-03-04', 1, 10, 10 UNION
SELECT 2, 1, 1, '2013-03-04', 2, 10, 20 UNION
SELECT 3, 1, 1, '2013-03-05', 1, 10, 30 UNION
SELECT 4, 1, 1, '2013-03-05', 2, 10, 40 UNION
SELECT 5, 2, 1, '2013-03-04', 1, 20, 20 UNION
SELECT 6, 2, 1, '2013-03-04', 2, 20, 40 UNION
SELECT 7, 2, 1, '2013-03-04', 3, 20, 60 UNION
SELECT 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 Table

Thank!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-22 : 02:38:28
you can use ROWLOCK hint in updates and inserts

see

http://www.bennadel.com/blog/477-SQL-Server-NOLOCK-ROWLOCK-Directives-To-Improve-Performance.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 inserts

see

http://www.bennadel.com/blog/477-SQL-Server-NOLOCK-ROWLOCK-Directives-To-Improve-Performance.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 =1
Update @TestTable set Quanty = 10, QtyOnhand = 30 WHERE Id = 2 and StoreID =1 AND ItemID =1
Update @TestTable set Quanty = 10, QtyOnhand = 40 WHERE Id = 3 and StoreID =1 AND ItemID =1
Update @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 =1
Update @TestTable set Quanty = 20, QtyOnhand = 30 WHERE Id = 6 and StoreID =2 AND ItemID =1
Update @TestTable set Quanty = 20, QtyOnhand = 50 WHERE Id = 7 and StoreID =2 AND ItemID =1
Update @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 =1
Update @TestTable set Quanty = 10, QtyOnhand = 60 WHERE Id = 4 and StoreID =1
AND ItemID =1
when exec , I want to session 1 and 2 run same time and session 3 wait 1 complated
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 06:54:03
try using ROWLOCK hint as suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -