🔒 Locked

[web] Lock SQL Server tables.

Started by GuyWithBeard Feb 17, 2009 at 7:23 AM 3 replies 2.3k views

Original Post

GuyWithBeard

347

GuyWithBeard

Feb 17, 2009 at 7:23 AM

Hi! I have a few (three) SQL statements that I need to run to completion before another user starts running the same three statements. The first checks for availability in the database and I want to return early if the db has been updated (by the two last statements). Let's say I have products in a table, and they have a quantity. The first of the three statements checks the quantity, and if there are any products left, runs the two other statements to buy this product, thus reducing the quantity. But I need all other users to have to wait before they can run these statements if they are currently being run by someone, so that two users cannot run the first statement at the same time and believe that there are two products left, even though there is only one. I think I should use table locking for this but it is unclear exactly how. I have SQL Server 2005 and ADO.NET at my disposal. Can you help me? Thanks!

evolutional

Mod

393

evolutional

Feb 17, 2009 at 7:28 AM

What you're after is transactions. Everything in the transaction will take a lock out on the table until the transaction is complete (either commit or rollback). This lock will block incoming queries, unless the read uncommitted isolation level is used (or the nolock query hint).

In your SQL code...

begin tran -- do work commit tran

Or using ADO.NET, here

Guthur

Feb 17, 2009 at 7:29 AM

The following method seems to use a timestamp, I only glanced at it but it seems like it would at least give you some idea

here

Try googling Concurrent table updates Sql server, for the possibility of some other techniques, I think concurrent keyword will get you the most relevant.

Edit: Evolutionals is a better idea :)

ID Merlin

Feb 23, 2009 at 2:07 PM

Quote:
Original post by evolutional
What you're after is transactions. Everything in the transaction will take a lock out on the table until the transaction is complete (either commit or rollback). This lock will block incoming queries, unless the read uncommitted isolation level is used (or the nolock query hint).

In your SQL code...

begin tran -- do work commit tran

Or using ADO.NET, here

Hopefully, the lock will be a row lock, so other products can be purchased. SQL Server will do row locking most of the time, so you'll probably be fine, since that transaction is likely going to be very quick to complete.

ROBERTREAD1

Feb 23, 2009 at 4:37 PM

You could do something like this

CREATE PROCEDURE sp_Purchase
@ItemID bigint = 0,
@SessionID bigint = 0
AS
BEGIN
BEGIN TRANS

UPDATE t_stock
SET Quantity = Quantity - 1
FROM t_stock
WHERE t_stock.ItemID = @ItemID
AND t_stock.Quantity > 0

IF @@ROWCOUNT = 1
INSERT INTO t_Basket
(ItemID, SessionID)
SELECT t_Stock.ItemID
, t_Session.SessionID
FROM t_Stock
CROSS JOIN t_Session
WHERE t_Stock.ItemID = @ItemID
AND t_Session.SessionID = @SessionID

IF @@ROWCOUNT = 1 COMMIT TRANS
ELSE ROLLBACK TRANS

END

@@ROWCOUNT is internal and returns the number of rows changed(INSERTed or UPDATEd) by the previously run command, very handy.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.