Row locking

Hi,

I am looking for a locking advice.

I am looking to implement a queue like mechanism where in multiple connections / users connect to the database to get a single row. I do it as follows.

I have two tables A & B. I have a stored procedure which joins the two tables and returns only one row whose status flag is N [N - not read before]. The returned row is the oldest whose status is N. The data returned is mainly from table A, and only one column from B. As we return the selected row its status should be update to T in table A.

As I said above this procedure is executed simultaneously by multiple users / connections.

How to use locking to make sure that each connection has a unique row and updates that row to set the status?

Will simply setting isolation level to repeatable read at the beginning of the procedure would do the magic?

Thanks

Saravanan K

If fetching & updating on single hit/scope then it is possible & acceptable with out any vulnerability. If these 2 operations are on separate hit/scope then it may cause deadlock/permanent lock on the SELECTED data.

You can get the flavor of sp_getapplock & sp_releaselock which is one of the good exercise for your requirement.

Sample,

Create proc LockedProcess(@.Data ..)

as

Begin

Exec sp_getapplock 'My Lock Name', 'Exclusive'

--Do Your Operation here

Exec sp_releaseapplock 'My Lock Name'

End

|||

Thanks a lot Manivannan.

The following is the outline of my proc.

CREATE PROC SVP_SELECTREPORTS

@.SVCTYPE VARCHAR(50)

AS

SET NOCOUNT ON

DECLARE @.ERRORCODE INT

DECLARE @.TEMPTABLE TABLE(ID INT,

FIELD1 VARCHAR(256),

FIELD2 CHAR(8),

FIELD3 VARCHAR(40),

FIELD4 VARCHAR(50),

FIELD5 CHAR(4),

FIELD6 TEXT,

FIELD7 TEXT,

FIELD8 DATETIME,

FIELD9 CHAR(1),

FIELD10 VARCHAR(40),

FIELD11 CHAR(1),

FIELD12 INT,

FIELD13 VARCHAR(40))

BEGIN

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--90 DAYS

BEGIN TRAN

INSERT INTO @.TEMPTABLE

SELECT TOP 1 UR.* FROM

(

SELECT

ID,

FIELD1,

FIELD2,

FIELD3,

FIELD4' = CASE WHEN ISNULL(LTRIM(RTRIM(FIELD4)),'') = '' THEN '' ELSE '(' + FIELD4 + ')' END,

FIELD5,

FIELD6,

FIELD7,

FIELD8,

FIELD9,

'FIELD10' = 'MYTYPE',

FIELD11,

FIELD12,

FIELD13 = FIELD10

FROM MYORG

WHERE FIELD10 = '1'

AND FIELD9 = 'N' AND FIELD11 = 'N'

UNION ALL

SELECT

A.ID,

A.FIELD1,

A.FIELD2,

A.FIELD3,

A.FIELD4,

A.FIELD5,

A.FIELD6,

A.FIELD7,

A.FIELD8,

A.FIELD9,

'FIELD10' = R.NM,

A.FIELD11,

A.FIELD12,

'FIELD13' = A.TYPE_NM

FROM MYRPT R, MYORG A

WHERE A.FIELD10 NOT IN ('1','3') AND

R.ID = SUBSTRING(A.FIELD10,3,LEN(A.FIELD10) - 2)

AND A.FIELD9 = 'N' AND A.FIELD11 = 'N'

) UR ORDER BY UR.ID ASC

SELECT @.ERRORCODE = @.@.ERROR

IF (@.ERRORCODE <> 0)

GOTO ERROR

UPDATE MYORG SET FIELD9 = 'T' WHERE

ID = (SELECT ID FROM @.TEMPTABLE)

SELECT @.ERRORCODE = @.@.ERROR

IF (@.ERRORCODE <> 0)

GOTO ERROR

SELECT * FROM @.TEMPTABLE

END

IF THERE IS A PROBLEM ROLLBACK

ERROR:

IF (@.ERRORCODE <> 0)

BEGIN

ROLLBACK TRAN

PRINT 'UNEXPECTED ERROR OCCURED'

RETURN -100

END

--IF THERE ARE NO ERRORS AND THE EXECUTION REACHES THIS LINE COMMIT THE TRANSACTION

COMMIT TRAN

END

Do you think the locking is sufficient enough so that all users gets one unique row?

|||

Personally, I don't like it. I do recognize, however, that serialization is sometimes a necessary evil.

You are artificially creating a single point of 'blocking' behavior. This could cause major problems as the usage increases.

Normal 'best practice' is to remove and decrease points of blocking.

But if you must continue down the path of serialization, this thread, especially the code example, may help you.

You will want to minimize the code (including switching) that must execute while the AppLock is set.

In the second SELECT (of the UNION statement) there is what appears to be a cartesian JOIN -that may not be efficient.

It seems that the @.TempTable can be eliminated with a single UPDATE statetment with a WHERE clause wherein the [ID] is validated against the entire [SELECT TOP 1 UR.* FROM...] structure.

|||

Thanks Arnie .

If I eliminate the @.TempTable and update in a single statement, how do I return the

specific row to the calling application? [I am using SQL – 2000].

I am working on your other suggestions.

|||

If you were using SQL 2005, it would be easy.

Then you could just refer to Books Online about using the OUTPUT capability of an UPDATE statement.

Refer to Books Online, Topic: UPDATE

Scroll down to sub-topic: E. Using UPDATE with the OUTPUT clause.

However, since you are using SQL 2000, you will have to do it a bit differently.

The following code example will allow you to see a potential method that doesn't require a Temp table:

Code Snippet

USE Northwind
GO

BEGIN TRAN

DECLARE
@.LastName varchar(20),
@.FirstName varchar(20)

SELECT City
FROM Employees
WHERE EmployeeID = 5

UPDATE Employees
SET
City = 'MyTown',
@.LastName = LastName,
@.FirstName = FirstName
WHERE EmployeeID = 5

SELECT
@.LastName,
@.FirstName

SELECT City
FROM Employees
WHERE EmployeeID = 5

ROLLBACK TRAN

City
London

-- --
Buchanan Steven

City
MyTown