July 12, 2002 at 5:21 am
I'm experiencing locking problems using SQL Server 2000. I have a stored procedure that increments a column then returns the incremented value. However, I tested the procedure using 20 client applications that calls the procedure 50,000 times each, and it resulted in duplicate values. Here's my table and stored procedure.
CREATE TABLE tblOrderNumber
(
OrderNumber int
)
CREATE PROCEDURE procGetOrderNumber
AS
BEGIN TRANSACTION
UPDATE tblOrderNumber WITH (XLOCK, TABLOCK)
SET OrderNumber = OrderNumber + 1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
SELECT OrderNumber
FROM tblOrderNumber
COMMIT TRANSACTION
I don't understand why the procedure procGetOrderNumber still generates duplicate values, when the lock is acquired at the start of the transaction, and is released at the end of the transaction. Does anyone have an idea regarding this?
July 12, 2002 at 6:55 am
The following lines are going to update every row in the table:
UPDATE tblOrderNumber WITH (XLOCK, TABLOCK)
SET OrderNumber = OrderNumber + 1
You've not included any code which would only update part of the table. This updates every row. Could the initial INSERT statements have put in duplicate values?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 15, 2002 at 8:21 am
Maybe a different approach...
Suppose an error occurs in the 'Update' statement in your procedure. In that case you return an empty recordset from the procedure.
So I am wondering if maybe the problem is in your client-side code? Do you handle the errorcondition appropriately?
If you only want to return a single value (and not the complete recordset), I would use a return value (or an output parameter) to get the result from the procedure.
July 15, 2002 at 6:43 pm
tblOrderNumber will only contain one row. Initially I insert a value, say OrderNumber=1. Then after that, all access to the data would be update and select - this would be in the procedure procGetOrderNumber.
bkelley,
The update statement should be able to lock the data, right?
NPeeters,
My client application detects errors and empty recordsets. I suppose on this procedure, it would never occur.
July 16, 2002 at 8:10 pm
pwellink,
but in theory it should lock the table until the end of the transaction, right? so there should be no problem.
Another thing is that this works on a single processor machine, but does not on a dual processor machine.
Is this a loophole or what?
July 16, 2002 at 8:58 pm
You may need to look at in Profiler to see if anything is going on under the hood. Look at SQL:StmtBegin and SQL:StmtComplete to see if extra transactions are occurring and look at locks acquired and released to make sure occurrs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 16, 2002 at 9:13 pm
ok i'll take a look at it.
July 18, 2002 at 6:16 am
there are recognized lock problems with sql 7.0 and sql2000sp1. make sure you apply sp2.
July 18, 2002 at 6:19 am
if it is sql 7.0 -- there are many documented bugs with regard to dual processors...so many in fact, that when running 7.0 its advisable to limit it to running on one processor (server/properties)
quote:
pwellink,but in theory it should lock the table until the end of the transaction, right? so there should be no problem.
Another thing is that this works on a single processor machine, but does not on a dual processor machine.
Is this a loophole or what?
July 25, 2002 at 11:41 am
I recommend creating an IDENTITY column in the parent table requiring the Order Number. Let SQL Server do the work in calculating the next value. The process of adding 1 to a column to calculate a unique value has always caused locking problems, usually deadlocks. This is not just a SQL Server issue, but generic relational database issue.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply