Transaction Locks

Question

Post reply

Transaction Locks

Stefan Mizzi

SSC Enthusiast

Points: 194
More actions
November 6, 2006 at 7:05 am

#90751

Dear All,
I have a small problem and hopefully someone can help me.
Basically, I have a table with PIN numbers. When a client buys a PIN online, i select the top PIN into a variable and then delete it from the table, like below:
begin transaction
set @get_PIN = (select top 1 serialNo from card_pins order by serialNo)
delete from card_pins where serialNo= @get_PIN
commit transaction
The problem is that when two clients request a PIN at the same time, he gets the same PIN number.
How can I LOCK the select until the transaction is ready so that the second client waits for a few seconds to get the next PIN?
Thanks for your help!
regards
Stefan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 1

I have tried this, and it seems to work.

SELECT COUNT(*) FROM card_pins WITH (TABLOCK, HOLDLOCK)

Put this as the first line after BEGIN TRAN. On Commit the lock is released.

Stefan Mizzi SSC Enthusiast Points: 194 More actions · Answer 2

Thanks man!

I will try it out and let us know how it went.

Cheers

Stefan

Ken McKelvey SSCoach Points: 18863 More actions · Answer 3

I think you need to use either an UPDLOCK or an XLOCK. An UPDLOCK should be alright here.

SET XACT_ABORT ON

BEGIN TRANSACTION

SELECT @get_PIN = MIN(serialNo)

FROM card_pins WITH (UPDLOCK)

DELETE card_pins

WHERE serialNo= @get_PIN

COMMIT

Making the whole table serializable seems a bit excessive.

Stefan Mizzi SSC Enthusiast Points: 194 More actions · Answer 4

The most important thing is to completely block the 2nd client from even doing a select on the table until the first one is ready so that he will get the next PIN.

Maybe XLOCK will only do this or HOLDLOCK/UPDLOCK will do the job?

Cheers

Stefan

Stefan Mizzi SSC Enthusiast Points: 194 More actions · Answer 5

I did some research but everywhere is not vert clear. Looks like UPDLOCK does not hold the select too. TABLOCK is not clear.

From what I understood I have to use WITH (TABLOCKX, HOLDLOCK). TABLOCKX will ALSO block the select while HOLDLOCK will be locked till the end of the transaction.

Its hard to test it, but I hope I'm right

Thanks.

Cheers

Stefan

Ken McKelvey SSCoach Points: 18863 More actions · Answer 6

Do the following test with UPDLOCK:

1. Open Query Analyser twice selecting your db in both.

2. In one copy of QA run:

BEGIN TRAN

SELECT MIN(serialNo)

FROM card_pins WITH (UPDLOCK)

3. In the other copy of QA run:

BEGIN TRAN

SELECT MIN(serialNo)

FROM card_pins WITH (UPDLOCK)

Nothing should appear as it will be blocked by the UPDLOCK placed by the first QA transaction.

4. Switch back to the first copy of QA and run:

ROLLBACK

This will remove the UPLOCK.

5. Switch to the second copy of QA and the statement should now have completed as the UPDLOCK has been removed.

Run ROLLBACK to clear the transaction.

The following is my understanding of locking in this sort of scenario. If you can produce tests that show this is not the case I would interested in knowing.

-- 1. Original

begin transaction

-- This allows multiple transactions to get the same serialNo

set @get_PIN = (select top 1 serialNo from card_pins order by serialNo)

delete from card_pins where serialNo= @get_PIN

commit transaction

-- 2. HOLDLOCK/SERIALIZABLE Option

begin transaction

-- This will stop any other transactions ALTERing card_pins

-- It will do an index or table scan.

SELECT COUNT(*) FROM card_pins WITH (HOLDLOCK)

-- It will NOT stop them doing the read so other transactions can get here.

-- This could DEADLOCK the first transaction

set @get_PIN = (select top 1 serialNo from card_pins order by serialNo)

-- Multiple transactions could get to here.

-- If a second transaction has serialized the transaction, a deadlock will occur.

delete from card_pins where serialNo= @get_PIN

commit transaction

-- 3. UPDLOCK

SET XACT_ABORT ON

BEGIN TRANSACTION

-- This will stop any other transaction from obtaining an UPDLOCK or XLOCK.

-- Shared locks can still be obtained so increasing concurrency.

SELECT @get_PIN = MIN(serialNo)

FROM card_pins WITH (UPDLOCK)

-- Only this transaction can get here with an UPDLOCK.

-- Other transactions trying to get an UPLOCK will have to wait.

-- This promotes the UPDLOCK to an XLOCK

-- Will have to wait for SHARED locks from other transactions to be released.

DELETE card_pins

WHERE serialNo= @get_PIN

-- This commits the transaction and removes locks.

COMMIT

-- 4. XLOCK

SET XACT_ABORT ON

BEGIN TRANSACTION

-- This will stop any other transaction from obtaining SHARED, UPDLOCK or XLOCK.

SELECT @get_PIN = MIN(serialNo)

FROM card_pins WITH (XLOCK)

-- Only this transaction can get here with an XLOCK.

-- Other transactions trying to get an XLOCK will have to wait.

-- No need to obtain an XLOCK here.

DELETE card_pins

WHERE serialNo= @get_PIN

-- This commits the transaction and removes locks.

COMMIT

Paul Clark-165098 SSC Veteran Points: 263 More actions · Answer 7

I have used the following algorithm with success:

1. Create a table called <<name of transaction>>_lock. Table should have a single integer column in it. Insert one row in the table Example

CREATE TABLE mytrans_lock (myfield int)

INSERT INTO mytrans_lock VALUES (1)

2. In your transaction, do the following:

BEGIN TRANSACTION

UPDATE mytrans_lock SET myfield = myfield + 0

COMMIT TRANSACTION

Each person running the transaction is blocked at the UPDATE statement if a another user has already started running the transaction. The commit transaction releases the lock and lets the next user run the transaction