Record locking within a stored procedure

Hi
I'd like to be able to lock a record in a table, row locking, update some
fields, then release the lock when finished. I'd also like to be able to
attempt the row lock for a specified amount of time, if, for example, anothe
r
session is already locking this record. The session would only be locking th
e
record for a minute amount of time, however I need to ensure that no
conflicts occur.
If it helps, I'm running the stored procedures via VFP9 so I know a
reasonable amount of SQL syntax, parsing etc.
What's the SQL syntax to complete something like this?
RegardsG18LLO (G18LLO@.discussions.microsoft.com) writes:
> I'd like to be able to lock a record in a table, row locking, update
> some fields, then release the lock when finished. I'd also like to be
> able to attempt the row lock for a specified amount of time, if, for
> example, another session is already locking this record. The session
> would only be locking the record for a minute amount of time, however I
> need to ensure that no conflicts occur.
> If it helps, I'm running the stored procedures via VFP9 so I know a
> reasonable amount of SQL syntax, parsing etc.
> What's the SQL syntax to complete something like this?
There is not really explicit syntax for this. The locking that SQL Server
uses for it's purposes is not intended for application use, nor is it
suitable for it.
There are a couple of ways to go. One is to add a column to the table
saying that it is locked. Such a column should probably have some sort
of a time stamp, and some rules to tell whether the lock can considered
to still be valid or to be stale.
Another way is to use application locks. In this case you are using
the lock manager in SQL Server, but you are not interferring with SQL
Server's internal business. An application is lock on a named resource.
Assuming the the table is called Widgets and has a numeric id as its
primary key, you could create an application lock on the resource
"Widget16" to lock the row with WidgetId = 16.
One thing to consider here is that you should not run too long transactions.
For instance, if the record is locked, because a user is about to update
it, you should have a transaction while waiting for user input. This
does not rule out application lock, as they can be on session level.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 29 Dec 2005 11:03:07 -0800, G18LLO
<G18LLO@.discussions.microsoft.com> wrote:
>I'd like to be able to lock a record in a table, row locking, update some
>fields, then release the lock when finished. I'd also like to be able to
>attempt the row lock for a specified amount of time, if, for example, anoth
er
>session is already locking this record. The session would only be locking t
he
>record for a minute amount of time, however I need to ensure that no
>conflicts occur.
>If it helps, I'm running the stored procedures via VFP9 so I know a
>reasonable amount of SQL syntax, parsing etc.
>What's the SQL syntax to complete something like this?
It's politicallly incorrect to do this "pessimistic locking" in
SQLServer, although it is possible and works reasonably well, if you
know what you're doing.
Something like:
begin transaction
-- lock record from other writers, they can still read
select <anyfield> from <yourtable> with (updlock)
-- with modification and default isolation levels,
-- others will probably not be able to read, either
update <yourtable> set <fields>
commit transaction
-- now changes are made and record(s) unlocked
Actually, you may not even need the select, unless you want to lock in
advance of the update.
Also look at lock_timeout, if you play with pessimistic locking you're
going to need it!
Note that in SQLServer2005 it will become a little bit less
politically incorrect because of the new "look-aside" isolation level
(I forget the Microsoft name for it ...)
Good luck.
Josh|||Josh with all due respect, SQL Server will not block until there is an updat
e
that occurs in a database transaction. A select with holdlock or updlock,
will allow another user to also select the same data with or without a
holdlock or updlock. IMO, SQL Server shouldn't do that but it does.
If you want users to line up, single file in a queue, the following code
will cause it to happen.
begin transaction
--basically a bogus update by setting a column to itself
--this causes a lock on that row
--any other T-SQL code that tries to update the same row will wait in line
--effectively creating a queue
update MyControlTable
set <column> = <column>
where <condition>
<do your work>
commit transaction
If there is only one situation, MyControlTable can be a single column,
single row table. In my case, I had a multiple column, multipler row table
so I could block users based on company id and functional area. For example
,
company 1 and loading customer data to ensure two different people didn't tr
y
to run a data load of customer information for company 1 at the same time as
the program code and business rules did not support concurrent data loading.
Another example, a person could queue up the loading of sales data for
different periods without having to wait for one to finish before starting
the next one. SQL Server became the traffic cop.
NOTE: This does not address the waiting for a specified amount of time. You
should be able to use the connection's timeout property.
Just my two cents,
Joe
"jxstern" wrote:

> On Thu, 29 Dec 2005 11:03:07 -0800, G18LLO
> <G18LLO@.discussions.microsoft.com> wrote:
> It's politicallly incorrect to do this "pessimistic locking" in
> SQLServer, although it is possible and works reasonably well, if you
> know what you're doing.
> Something like:
> begin transaction
> -- lock record from other writers, they can still read
> select <anyfield> from <yourtable> with (updlock)
> -- with modification and default isolation levels,
> -- others will probably not be able to read, either
> update <yourtable> set <fields>
> commit transaction
> -- now changes are made and record(s) unlocked
> Actually, you may not even need the select, unless you want to lock in
> advance of the update.
> Also look at lock_timeout, if you play with pessimistic locking you're
> going to need it!
> Note that in SQLServer2005 it will become a little bit less
> politically incorrect because of the new "look-aside" isolation level
> (I forget the Microsoft name for it ...)
> Good luck.
> Josh
>|||Joe from WI (JoefromWI@.discussions.microsoft.com) writes:
> Josh with all due respect, SQL Server will not block until there is an
> update that occurs in a database transaction. A select with holdlock or
> updlock, will allow another user to also select the same data with or
> without a holdlock or updlock. IMO, SQL Server shouldn't do that but it
> does.
Then you have misunderstood the meaning of these hints.
HOLDLOCK simply means "use serializable isolation level". That is, ensure
that if I run this SELECT in the same transaction, that it will return
the same result. No rows modified, deleted or added.
UPDLOCK means "I am about to update this row". UPDLOCK is a shared lock,
in so far that it does not prevent other readers, but only one process
can have an UPDLOCK on a resource.

> If you want users to line up, single file in a queue, the following code
> will cause it to happen.
> begin transaction
> --basically a bogus update by setting a column to itself
> --this causes a lock on that row
> --any other T-SQL code that tries to update the same row will wait in line
> --effectively creating a queue
> update MyControlTable
> set <column> = <column>
> where <condition>
><do your work>
That is a poor solution. (Not the least since the DB engine may outsmart
you, and not take out a lock, since nothing was changed.) There are at least
two that are better.
One is to use the XLOCK hint to get an exclusive lock.
But the best in my opinion is to use application locks, as then you have
more control over the resources you lock.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> One is to use the XLOCK hint to get an exclusive lock.
Just be careful with the XLOCK hint. There's an optimization where SQL Serve
r doesn't respect a row
level XLOCK if the row hasn't been modified since the earliest open transact
ion (or something to
that effect):
--Connection 1
USE pubs
BEGIN TRAN
SELECT *
FROM authors (xlock)
WHERE au_lname = 'White'
--Connection 2
USE pubs
EXEC sp_lock
SELECT *
FROM authors
WHERE au_lname = 'White'
--Query is not blocked
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973E924F3F64Yazorman@.127.0.0.1...
> Joe from WI (JoefromWI@.discussions.microsoft.com) writes:
> Then you have misunderstood the meaning of these hints.
> HOLDLOCK simply means "use serializable isolation level". That is, ensure
> that if I run this SELECT in the same transaction, that it will return
> the same result. No rows modified, deleted or added.
> UPDLOCK means "I am about to update this row". UPDLOCK is a shared lock,
> in so far that it does not prevent other readers, but only one process
> can have an UPDLOCK on a resource.
>
> That is a poor solution. (Not the least since the DB engine may outsmart
> you, and not take out a lock, since nothing was changed.) There are at lea
st
> two that are better.
> One is to use the XLOCK hint to get an exclusive lock.
> But the best in my opinion is to use application locks, as then you have
> more control over the resources you lock.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
> Just be careful with the XLOCK hint. There's an optimization where SQL
> Server doesn't respect a row level XLOCK if the row hasn't been modified
> since the earliest open transaction (or something to that effect):
> --Connection 1
> USE pubs
> BEGIN TRAN
> SELECT *
> FROM authors (xlock)
> WHERE au_lname = 'White'
>
> --Connection 2
> USE pubs
> EXEC sp_lock
> SELECT *
> FROM authors
> WHERE au_lname = 'White'
> --Query is not blocked
Thanks, Tibor.
Just stresses my point that you should use application locks for this
purpose.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please excuse my ignorance for locking hints but if you have two transaction
s
issuing a select with a xlock, updlock, or holdlock on the same row of data,
how can SQL Server guarntee that the transaction will be able to repeat the
read? The only combination that worked in my testing is the holdlock when
tran1 selected data with a holdlock, tran2 could select the data with a
holdlock but it could not update it until tran1 completed. If tran1 had a
updlock or xlock, tran2 was able to read and update the data ignoring tran1'
s
lock.
So I'd recommend having a datetime column so that there is a real update
just in case the optimizer gets too smart. ;) Optionally, add connection
information.
begin transaction
update MyControlTable
set LastLock = getdate(), SPID = @.@.SPID, Username = SYSTEM_USER,
ApplicationName = APP_NAME, Workstation = HOST_NAME ( ) , DBUser = USER_NAME
()
where <condition>
<do other work here>
commit transaction
Personally, I would NOT use an application lock such as updating a column on
the data row indicating that it is locked. Because sooner or later, there
will be an application error, dropped connection, or whatever and you're
stuck with a logical lock on the row. And using a datetime to deterimine
whether the lock is stale can be dangerous, in my opinion. How long do you
let other users wait--seconds? minutes? hours? days? Sooner or later,
someone will come along with a longer-than-expected job and the logical lock
s
become worthless.
With my solution, as soon as the connection drops one way or another the
lock is released automatically (either through a commit or a rollback) and
the next user has immediate access.
Just my two cents,
Joe
"Erland Sommarskog" wrote:

> Tibor Karaszi (tibor_please.no.email_karaszi@.hotmail.nomail.com) writes:
> Thanks, Tibor.
> Just stresses my point that you should use application locks for this
> purpose.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||See my post about XLOCK hint being essentially useless.
UPDLOCK work, but both connections need to use UPDLOCK. Update lock doesn't
block shared locks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:6E32CDD2-ECB5-44F4-A2E6-249794010E06@.microsoft.com...
> Please excuse my ignorance for locking hints but if you have two transacti
ons
> issuing a select with a xlock, updlock, or holdlock on the same row of dat
a,
> how can SQL Server guarntee that the transaction will be able to repeat th
e
> read? The only combination that worked in my testing is the holdlock when
> tran1 selected data with a holdlock, tran2 could select the data with a
> holdlock but it could not update it until tran1 completed. If tran1 had a
> updlock or xlock, tran2 was able to read and update the data ignoring tran
1's
> lock.
> So I'd recommend having a datetime column so that there is a real update
> just in case the optimizer gets too smart. ;) Optionally, add connection
> information.
> begin transaction
> update MyControlTable
> set LastLock = getdate(), SPID = @.@.SPID, Username = SYSTEM_USER,
> ApplicationName = APP_NAME, Workstation = HOST_NAME ( ) , DBUser = USER_NA
ME()
> where <condition>
> <do other work here>
> commit transaction
> Personally, I would NOT use an application lock such as updating a column
on
> the data row indicating that it is locked. Because sooner or later, there
> will be an application error, dropped connection, or whatever and you're
> stuck with a logical lock on the row. And using a datetime to deterimine
> whether the lock is stale can be dangerous, in my opinion. How long do yo
u
> let other users wait--seconds? minutes? hours? days? Sooner or later,
> someone will come along with a longer-than-expected job and the logical lo
cks
> become worthless.
> With my solution, as soon as the connection drops one way or another the
> lock is released automatically (either through a commit or a rollback) and
> the next user has immediate access.
> Just my two cents,
> Joe
> "Erland Sommarskog" wrote:
>|||Joe from WI (JoefromWI@.discussions.microsoft.com) writes:
> Personally, I would NOT use an application lock such as updating a
> column on the data row indicating that it is locked. Because sooner or
> later, there will be an application error, dropped connection, or
> whatever and you're stuck with a logical lock on the row.
No, an application lock is handled by lock manager in SQL Server.
Application locks on either be on transaction level or session level.
Application locks on transaction level are releasd when the transaction
is committed or rolled back. Session-level locks are released when
the process disconnects. (There is a bug in SQL 2005 RTM, though, so
that a session application lock survives the reuse of a connnection
from the connection pool. I expect this bug to be fixed in SP1 of SQL 2005.
For more info, see sp_setapplock in Books Online.

> And using a datetime to deterimine whether the lock is stale can be
> dangerous, in my opinion. How long do you let other users
> wait--seconds? minutes? hours? days? Sooner or later, someone will come
> along with a longer-than-expected job and the logical locks become
> worthless.
Using a column to mark a row as lock is also a viable technique.
Particularly, this solution is necessary if the row is to be
locked while waiting for user input. Locking resources while waiting
for user input is simply admissible. What if user goes to lunch? Or
for holidays in two ws.
For how long to wait before such a lock is defined stale, is a
business decision, but maybe 30 minutes is reasonable. Of course
the application must be able to handle if the user presses Save after
40 minutes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx