Record Locking for new user!!!

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Record Locking for new user!!!

Author

Topic

rljoness
Starting Member

1 Post

Posted - 2002-02-21 : 10:08:55

My problem is simple. I have a number in a table. I select the number, add 1 to it and update the table immediately. This is not fast enough for the user volume on our DB (SQL 7.0 by the way). I have see topics on updates on ROWLOCK and HOLDLOCK I'm just not sure the correct way to do this. Any help would be most appreciatted. Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-21 : 10:14:06

Why not do it in one statement

update tbl set fild = fld + 1

if you need to get the result into a variable then

update tbl set fld = fld + 1, @fld = fld + 1

This will lock the row for the duration of the sattement so yuo don't get any problems with other spids.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-02-21 : 13:31:45

I've had the same problem. This is how I solved it.
Is this a bad solution?
(Tablenames and vars changed to protect the solution)

SELECT @MyVar= (
SELECT TOP 1 Field1 FROM myTable
WHERE field2 = @Field2
AND field3 = 0
AND field4 = 0
ORDER BY Field1
)
IF @MyVar <> 0
BEGIN
BEGIN TRANSACTION
UPDATE MyTable WITH (HOLDLOCK, ROWLOCK)
SET field3 = 1, field4 = 1
WHERE field2 = @field2
AND CallNo = @CallNo
AND field3 = 0
AND field4 = 0

IF @@ROWCOUNT = 0
BEGIN
SET @MyVar= -1
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

END

SELECT @MyVar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources