Row locking and unlocking - SQL Server Forums

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)

Row locking and unlocking

Author

Topic

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-03-22 : 02:07:24

Hi all,

Suppose I perform some transactions on a row like this:

begin tran

select * from customers with (holdlock,rowlock)
where customer_id = '7777'

/* perform other transactions on the row */

commit tran

In this way, another transaction cannot update this row customer_id = '7777' until the entire transaction is completed, i.e., the lock is released when the transaction completes.

What I want to ask is, is there any command to manually lock the row and then manually unlock the row? E.g. I would call a stored procedure which locks the row with customer_id='7777', and then call another stored procedure to release this lock. Is there any command or system stored procedure to do this?

Thanks a lot,
delpiero

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 02:37:13

Nothing to stop the "other SProc" doing the COMMIT. But the lock is only maintained between the BEGIN TRANS and the COMMIT.

If you are wanting to lock the record whilst, say, a use works on editing it you should use a different method - e.g. have a table of users + record PK, i.e. a "logical" record locking list showing which record is being edited by whom.

Kristen

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-22 : 02:42:20

I hope we have to implement our own algorithm to acheive that.In my earlier project we maintained a separate table for managing lock and unlock the rows.When we take any row for DML transaction, we need to put an entry to that lock table.if another user try to take the same row for any DML transaction,it won't allow to access. Once the 1st user completed the transaction, then the another user can access that row.

Though we faced so many problems while using this concept.

:) While we stop to think, we often miss our opportunity :)

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-03-22 : 02:58:40

My concern is to fully control when to release the lock, like executing a SProc at any time I want to release the lock.
How can a SProc go into that session to commit the transaction? This is what I am wondering.

Best regards,
delpiero

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-22 : 03:41:39

Yeah.As per my option, you can control fully using stored procedure.but problem is, when any error raises before unlocking that specified locked row,you have to give some more user interfaces to relase those rows... its really tedious job.

:) While we stop to think, we often miss our opportunity :)

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-03-22 : 04:41:41

Then how can we manually unlock the row by calling a stored procedure?

Thanks,
delpiero

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 07:03:24

We take a different view on this (we used to have a Keys-Locked-By-User table, but you had to clear that out when a user's PC crahsed etc.)

We ahve an EditNumber column on all tables. This is incremented (by trigger) whenever it is updated. The UPDATE says:

UPDATE MyTable
SET MyColumn1='foo, MyColumn2='bar'
WHERE MyPK = 'ABC123' AND MyEditNo = 999

so if the MyEditNo is no longer 999 then the edit will fail.

OK, so its a bit tough if a user has just spent and hour constructing an edit, but two people editing the same record happens very rarely in our application.

Kristen

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-03-22 : 23:45:06

Thanks. I will think about setting up this field on all tables.

I have another question on my transaction:

begin tran

select * from customers with (holdlock,rowlock)
where customer_id = '7777'

/* perform some updates on the row */

commit tran

Q1: If the transaction above contains only a select statement, and no insert/udpate/delete, is it possible to use a locking hint to prevent other sessions from reading the same row? I found that if I run only "begin tran" and the "select" statement, and without running "commit tran", other sessions are still able to select the row even if I already use hold lock and row lock hints.

Q2: Suppose there are some updates to the row after the select statement in the above transaction. If a workstation runs the above transaction (e.g. it is stored in a stored procedure), and before the transaction/procedure completes, the application on the user's workstation crashes. Will the lock be released? Can other sessions access the row? Or we need to wait for it to timeout?

Many thx,
delpiero

Kristen
Test

22859 Posts

Posted - 2005-03-23 : 00:06:10

"I will think about setting up this field on all tables"

If you wind up modifing every table, and if you don't already ahve them, I would consider Create User & Date, and Update User and Date. Those 4, plus EditNo, we have on every table (give or take a couple).

You could also consider Archive tables. We have those or a high proportion of tables. Basically the same columns, everything NOT NULL, no constraints. We add two columns: Update or Delete and a Date, and put a clustered index on the PK Fields + Date.

Q1: Would Isolation Level get you what you want?

Q2: I *think* the server doesn't care about the Workstation, so will continue its work until the SProc is done, and _then_ discover that it can't get the results to the workstation [because it isn't there any more]. However, I suppos its possible that the process is interactive, and therefore if the server struggles getting the results to the workstation half way through the Sproc then it may sit tight waiting.

Someone else will know the answer to Q2 better than I do!

Kristen

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-03-23 : 02:17:21

Thanks Kristen.

For Q1, let me elaborate like this:

begin tran
declare @number int
select @number=age from customers where id='5'

/* do a lot of queries here which do not involve customers table */

update customers set age=@number+1 where id='5'

commit tran

In the above transaction, I used "a lot of queries" to illustrate my problem. Suppose the "a lot of queries" would take more than 10 seconds to run.

Then I opened 2 windows in Query Analyzer and ran the above transaction, with the second window transaction starting about 1 second later than the first window. Eventually, deadlock would occur. This deadlock occurs no matter I use holdlock, rowlock or transaction isolation level serializable in the first select statement in the transaction.

If I use :

select @number = age from customers with (xlock) where ID = '5'

deadlock does not occur and the results returned are correct. (e.g. if the first transaction updates the age from 30 to 31, then the 2nd transaction would update the age from 31 to 32).

What I want to do is to prevent others from reading the data before the current transaction finishes. Is exclusive lock the only way to do it and will it cause a lot of performance problems?

Thanks,
delpiero

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-03-23 : 02:42:22

For the above transaction, deadlock occurs when setting lock hints to holdlock or rowlock, or set transaction isolation level to "serializable".

Deadlock does not occur when we use "xlock", or set transaction isolation level to "read committed", which is the default.

However, even when there is no deadlock, there is a difference in the results. When we use "xlock", the results are correct, i.e. session 1 updates the age from 30 to 31, and session 2 updates it from 31 to 32. But when we set to "read committed", both session would update it from 30 to 31, which is logically incorrect.

Is that we must use "xlock" in case our transaction may be long-running?

Thanks,
delpiero

Kristen
Test

22859 Posts

Posted - 2005-03-23 : 03:43:41

Any reason NOT to do the update at the point where you do the SELECT? It won't be committed until the COMMIT happens - maybe its important to the
/* do a lot of queries here which do not involve customers table */
that the original value is in place?

Or change the update to:


update customers set age=@number+1 where id='5' AND age=@number
IF @@ROWCOUNT <> 1
BEGIN
    ROLLBACK
END
ELSE
BEGIN
    COMMIT
END

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-23 : 11:06:28

Wouldn't it be simpler to use a column of datatype ROWVERSION for optimistic locking, since SQL Server updates it automatically, and you don't have to use a trigger?

UPDATE MyTable
SET
	MyColumn1='foo,
	MyColumn2='bar'
WHERE
	MyPK = 'ABC123' AND
	MYRoversion = @saved_rowversion

quote:
Originally posted by Kristen

We take a different view on this (we used to have a Keys-Locked-By-User table, but you had to clear that out when a user's PC crahsed etc.)

We ahve an EditNumber column on all tables. This is incremented (by trigger) whenever it is updated. The UPDATE says:

UPDATE MyTable
SET MyColumn1='foo, MyColumn2='bar'
WHERE MyPK = 'ABC123' AND MyEditNo = 999

so if the MyEditNo is no longer 999 then the edit will fail.

OK, so its a bit tough if a user has just spent and hour constructing an edit, but two people editing the same record happens very rarely in our application.

Kristen

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 13:49:35

I didn't read this whole thing, but...

quote:

begin tran

select * from customers with (holdlock,rowlock)
where customer_id = '7777'

/* perform other transactions on the row */

commit tran

You should not be beginning a transaction until you are ready to modify the database. The first thing in your example is a select. This should be outside of the transaction so that you can keep the transactions as fast as possible so that you don't lock for too long.

Tara

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-24 : 00:14:48

Absolutely correct...

:) While we stop to think, we often miss our opportunity :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources