Exclusive Record Locking of Rows upon Read

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)

Exclusive Record Locking of Rows upon Read

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-02 : 08:58:56

Joe writes "I have a table that is highly contentious. Many users will be trying to access and update this table concurrently. Each user will be trying to select a contiguous group of records within that table based on a keyed order and a free (not taken) status.

If each user reads and updates one record at a time in order, by the time he gets to the next record, it will already have been taken by another user. This prevents anyone from getting a contiguous block.

What I'd like to do in one scenario is read from a fixed list and place an exclusive lock on the list while I am updating the whole list (and allow a rollback if the list is not complete).
Here, I tried using a SCROLL_LOCKS cursor and passing the list into the cursor SELECT statement using IN, but the SQL SP would not accept a varchar inside the IN clause. If I build the IN clause dynamically, the cursor does not seem to allow an EXECUTE(@Select).

In the second scenario, I'd like to read a TOP X and place exclusive lock on those records immediately to allow updating all of them.

While exclusive locks get a bad rap, they seem appropriate here, and I can avoid other locking conflicts by using NOLOCK where appropriate. Any thoughts would be greatly appreciated.

I am using Windows 2000, SQL Server 7 SP3"

Jay99

468 Posts

Posted - 2002-04-02 : 09:01:52

Any chance you can update as a set rather than iterate through a cursor?

Jay
<O>

joe iacoponi
Starting Member

11 Posts

Posted - 2002-04-02 : 12:39:01

I'd prefer to do that, but I also need to retrieve a value from another field (which is actually the identity key).

So, now I have a CURSOR SELECT / FETCH INTO using SCROLL_LOCKS where I use four fields (which identify a unique record) to retrieve the identity key. I then UPDATE using WHERE CURRENT OF and then use the identity key to perform an INSERT on another table. I then loop through contiguous records (using the four fields to find adjacency) to perform additional UPDATES / INSERTS.

It works, but I'm hoping there is a more efficient method.

Joe

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-04-02 : 14:05:10

What you ultimately want is a way to tell SQL Server to reserve rows for a query without causing other SPID's to spin on the locks created to reserve those rows.

You do this with two query hints: (rowlockx) and (readpast). Check them out in BOL.

BTW, never make a habit of using (nolock) ... it will always come back to haunt you.

setBasedIsTheTruepath
<O>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-02 : 14:24:43

I have a similar problem SetBased. I have a "queue" that is accessed by 1000+ users all very close together. Each user needs topick the top record off the queue, mark it as "in use" and do the desired action on that record. I need to make sure that one and ONLY one user gets a record, and tries to perform the action.

Here's my query to "Get the Next item from the Queue." Will this perform like I describe, or am I going to run into isseues down the road?

Thank!
--Created 2/13/2002 MJP
CREATE PROCEDURE [p_Callq_GetNextCallForSchedule] (
@CallNo INT OUTPUT,
@SchedNum INT

)
AS

SELECT @Callno = (
SELECT TOP 1 CallNo FROM Callq
WHERE Schednum = @SchedNum
AND Checked = 0
AND Inuse = 0
AND ((Callstat NOT IN ('Z', 'D', 'P', 'R', 'V')) OR (CallStat IS NULL))
ORDER BY Callno
)

If @CallNo IS NULL
BEGIN
Set @CallNo = 0
END

IF @CallNo <> 0
BEGIN
BEGIN TRANSACTION
UPDATE Callq WITH (HOLDLOCK, ROWLOCK)
SET Checked = 1, Inuse = 1
WHERE SchedNum = @SchedNum
AND CallNo = @CallNo
AND Checked = 0
AND Inuse = 0

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

END

SELECT @CallNo
GO

joe iacoponi
Starting Member

11 Posts

Posted - 2002-04-02 : 14:56:20

You will run into contention issues in high volume. If you want to do it this way, you should make it SELECT ... FROM Callq WITH (ROWLOCK, UPDLOCK, READPAST), so no one else can read or update that record until you have completed the update and / or transaction.

You also don't need HOLDLOCK on the UPDATE. That is automatic.

My problem goes a step further and requires that the transaction get several contiguous records.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-04-02 : 15:08:18

Does anyone know how to simulate "contention" so that I can test all of my code and make sure it works? I don't think I have enough hardware to do it, but I could be wrong.

BTW Joe, thx for your suggesstions. I've implimented those, so we'll see how it goes.

Michael

joe iacoponi
Starting Member

11 Posts

Posted - 2002-04-02 : 18:20:51

Microsoft's Web Application Stress Tool

http://webtool.rte.microsoft.com/

It's a bit pesky, but it's free. There are limitations if you use alot of Request variables, but it works well with Session variables.

The best program I've seen is the Astra Load Test, but it's very pricey.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources