SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to Lock Update, Insert by Key
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thangnc
Starting Member

7 Posts

Posted - 04/21/2013 :  21:23:55  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/22/2013 :  02:38:28  Show Profile  Reply with Quote
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 - 04/23/2013 :  07:01:48  Show Profile  Reply with Quote
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

Edited by - thangnc on 04/25/2013 05:55:45
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/25/2013 :  06:54:03  Show Profile  Reply with Quote
try using ROWLOCK hint as suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000