Setting locks in SQL Server 2000

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
Hi all,

Am try to set a readwrite lock on a database table, here is the code that I am using in my procedure:
Code: 
CREATE PROCEDURE dbo.spReserveCredits 

	@Organization as nvarchar,
	@Cost as money,
	@Result as bit output

AS
	
	UPDLOCK

	DECLARE @Amount as money

		SELECT @Amount = Credit 
		FROM SMS_Credit 
		WHERE Org_ID = @Organization

		if @Cost > @Amount
			SET @Result = 0

		else

			begin

				UPDATE SMS_Credit
				SET Credit = @Amount - @Cost
				WHERE Org_ID = @Organization

				SET @Result = 1

				COMMIT

			end
	
		return @Result

GO

I need to lock the table so that I can read and write to it if necessary, while at the same time preventing anyone else from viewing the table.

The error that I am getting is: "Incorrect Syntax near UPDLOCK".

Mike55
 
First I must thank you million times for posting this Q. :D

Like you, I was encountering a locking Q these days and cant figure it out (Details). Only after reading your post, I realized UPDLOCK is exactly what I was looking for. Thanks.

OK, back to your question. Locking hints is not used in this way, it should be like


Code: 
USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors [B]WITH (UPDLOCK)[/B]
GO
Example from SQL Books Online -> locking -> hints
 
Thanks a million, for the reply - have been banging my head against a brick wall for the last half day trying to figure out how to use locks.

Mike55
 
Back
Top