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 |
|
sunrain
Starting Member
13 Posts |
 Posted - 2004-11-01 : 14:16:39
|
| HelloI have a transaction that goes something like this:-check to see if "Stock.quantity > @quantity" (wher @quantity is a parameter of the procedure in which I have my transaction - CREATE PROCEDURE .... BEGIN TRANSACTION...).-update Stock table by setting the new quantity value: "Stock.quantity= Stock.quantity - @quantity"My problem is if I have multiple 'threads' that execute the same procedure so all of them have access to the Stock table. I have to use something else beside isolation levels, more specific I have to use locks. What kind of locks should I use on table Stock so that once I read data from it I make sure that nobody can access that data until I update it with the new value. Regards |
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-11-01 : 14:34:12
|
Well to make sure that no one can access that data that your touching, you'd need to use an Xlock, to make sure no one touches the table, use a TABLOCKX. Creating exclusive locks though and running multiple statements against the same data can have consequences.-JonShould still be a "Starting Member"  . |
 |
|
|
sunrain
Starting Member
13 Posts |
Posted - 2004-11-01 : 15:07:10
|
| Hello JonThank you for the reply.Should I use this types of locks when I read data from Stock table or when I update the data in the Stock table? I would like for others to be able to read that data but not modify it. Hpw do I do that?Regards |
|
|
|
|
|
|
Using Locking Hints