Record locking within a stored procedure

microsoft.public.sqlserver.programming

29 Dec 2005 7:03 PM

G18LLO

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, 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?

Regards

29 Dec 2005 11:22 PM

Erland Sommarskog

G18LLO (G18***@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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

30 Dec 2005 1:15 AM

jxstern

On Thu, 29 Dec 2005 11:03:07 -0800, G18LLO
<G18***@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, 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?

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

31 Dec 2005 8:06 PM

Joe from WI

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.

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 try
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

Show quote

"jxstern" wrote:

> On Thu, 29 Dec 2005 11:03:07 -0800, G18LLO
> <G18***@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, 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?
>
> 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
>
>

31 Dec 2005 11:53 PM

Erland Sommarskog

Joe from WI (Joefro***@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.

Show quote

> 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.

1 Jan 2006 10:45 AM

Tibor Karaszi

> 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 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

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns973E924F3F64Yazorman@127.0.0.1...
> Joe from WI (Joefro***@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, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

1 Jan 2006 10:56 AM

Erland Sommarskog

Tibor Karaszi (tibor_please.no.email_kara***@hotmail.nomail.com) writes:
Show quote

>> 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
> 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.

1 Jan 2006 5:45 PM

Joe from WI

Please excuse my ignorance for locking hints but if you have two transactions
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 locks
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

Show quote

"Erland Sommarskog" wrote:

> Tibor Karaszi (tibor_please.no.email_kara***@hotmail.nomail.com) writes:
> >> 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
> > 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, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

1 Jan 2006 7:20 PM

Tibor Karaszi

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/

Show quote

"Joe from WI" <Joefro***@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 transactions
> 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 locks
> 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_kara***@hotmail.nomail.com) writes:
>> >> 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
>> > 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, esq***@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>

1 Jan 2006 11:04 PM

Erland Sommarskog

Joe from WI (Joefro***@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 weeks.

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.

2 Jan 2006 1:11 AM

Joe from WI

sp_setapplock does not exist in SQL Server 2000 books online or the master
database. Doing a search at microsoft.com found no such topic.

If it's a new feature in SQL 2005, I'll have to wait until one of my clients
upgrade as I have no free time at the moment to train on it.

I agree that marking a row as locked while waiting for user input is a
viable technique. However in this case, the poster made no mention of
needing that functionality.

Once you do that, business decisions and complexity sore as you have the
"when is it stale" debate and "what does the application do when the user
hits save after 40 minutes" scenario. A lot more coding and a lot more
headaches. I try to keep things simple whenever possible.

It sounded to me that the poster was looking for a way to single thread
something. SQL Server 2000 does not appear to honor the lock (holdlock,
xlock, updlock) until there is an update. Therefore, you have to update the
row (whether its the actual data row) or a control table. The first
transaction to update the row will keep all others waiting until it either
commits or rolls back. IMO, these lock hints are worthless for that task.

If I misinterpreted what the poster was looking for, I apoligize and
withdraw my suggestions.

Just my two cents,
Joe

Show quote

"Erland Sommarskog" wrote:

> Joe from WI (Joefro***@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 weeks.
>
> 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, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

2 Jan 2006 1:53 AM

Kalen Delaney

Hi Joe

The procedure is sp_getapplock and it is available in SQL Server 2000.

--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com

Show quote

"Joe from WI" <Joefro***@discussions.microsoft.com> wrote in message
news:4E1E4703-FE1F-4C66-B5E1-0027A9C35F44@microsoft.com...
> sp_setapplock does not exist in SQL Server 2000 books online or the master
> database. Doing a search at microsoft.com found no such topic.
>
> If it's a new feature in SQL 2005, I'll have to wait until one of my
> clients
> upgrade as I have no free time at the moment to train on it.
>
> I agree that marking a row as locked while waiting for user input is a
> viable technique. However in this case, the poster made no mention of
> needing that functionality.
>
> Once you do that, business decisions and complexity sore as you have the
> "when is it stale" debate and "what does the application do when the user
> hits save after 40 minutes" scenario. A lot more coding and a lot more
> headaches. I try to keep things simple whenever possible.
>
> It sounded to me that the poster was looking for a way to single thread
> something. SQL Server 2000 does not appear to honor the lock (holdlock,
> xlock, updlock) until there is an update. Therefore, you have to update
> the
> row (whether its the actual data row) or a control table. The first
> transaction to update the row will keep all others waiting until it either
> commits or rolls back. IMO, these lock hints are worthless for that task.
>
> If I misinterpreted what the poster was looking for, I apoligize and
> withdraw my suggestions.
>
> Just my two cents,
> Joe
>
> "Erland Sommarskog" wrote:
>
>> Joe from WI (Joefro***@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 weeks.
>>
>> 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, esq***@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>

2 Jan 2006 5:19 AM

Joe from WI

Kalen, thanks so much for the correction.

I've been working with SQL Server for years and have never heard of this.

sp_getapplock and sp_releaseapplock are quite powerful.

Joe

Show quote

"Kalen Delaney" wrote:

>
> Hi Joe
>
> The procedure is sp_getapplock and it is available in SQL Server 2000.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
>
> "Joe from WI" <Joefro***@discussions.microsoft.com> wrote in message
> news:4E1E4703-FE1F-4C66-B5E1-0027A9C35F44@microsoft.com...
> > sp_setapplock does not exist in SQL Server 2000 books online or the master
> > database. Doing a search at microsoft.com found no such topic.
> >
> > If it's a new feature in SQL 2005, I'll have to wait until one of my
> > clients
> > upgrade as I have no free time at the moment to train on it.
> >
> > I agree that marking a row as locked while waiting for user input is a
> > viable technique. However in this case, the poster made no mention of
> > needing that functionality.
> >
> > Once you do that, business decisions and complexity sore as you have the
> > "when is it stale" debate and "what does the application do when the user
> > hits save after 40 minutes" scenario. A lot more coding and a lot more
> > headaches. I try to keep things simple whenever possible.
> >
> > It sounded to me that the poster was looking for a way to single thread
> > something. SQL Server 2000 does not appear to honor the lock (holdlock,
> > xlock, updlock) until there is an update. Therefore, you have to update
> > the
> > row (whether its the actual data row) or a control table. The first
> > transaction to update the row will keep all others waiting until it either
> > commits or rolls back. IMO, these lock hints are worthless for that task.
> >
> > If I misinterpreted what the poster was looking for, I apoligize and
> > withdraw my suggestions.
> >
> > Just my two cents,
> > Joe
> >
> > "Erland Sommarskog" wrote:
> >
> >> Joe from WI (Joefro***@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 weeks.
> >>
> >> 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, esq***@sommarskog.se
> >>
> >> Books Online for SQL Server 2005 at
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> >> Books Online for SQL Server 2000 at
> >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> >>
> >
>
>
>
>

2 Jan 2006 5:44 PM

Kalen Delaney

I guess you haven't read my book. ;-)

--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com

Show quote

"Joe from WI" <Joefro***@discussions.microsoft.com> wrote in message
news:972A40EE-ACAD-4BC6-BD8B-47D881B255E2@microsoft.com...
> Kalen, thanks so much for the correction.
>
> I've been working with SQL Server for years and have never heard of this.
>
> sp_getapplock and sp_releaseapplock are quite powerful.
>
> Joe
>
> "Kalen Delaney" wrote:
>
>>
>> Hi Joe
>>
>> The procedure is sp_getapplock and it is available in SQL Server 2000.
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>>
>> "Joe from WI" <Joefro***@discussions.microsoft.com> wrote in message
>> news:4E1E4703-FE1F-4C66-B5E1-0027A9C35F44@microsoft.com...
>> > sp_setapplock does not exist in SQL Server 2000 books online or the
>> > master
>> > database. Doing a search at microsoft.com found no such topic.
>> >
>> > If it's a new feature in SQL 2005, I'll have to wait until one of my
>> > clients
>> > upgrade as I have no free time at the moment to train on it.
>> >
>> > I agree that marking a row as locked while waiting for user input is a
>> > viable technique. However in this case, the poster made no mention of
>> > needing that functionality.
>> >
>> > Once you do that, business decisions and complexity sore as you have
>> > the
>> > "when is it stale" debate and "what does the application do when the
>> > user
>> > hits save after 40 minutes" scenario. A lot more coding and a lot more
>> > headaches. I try to keep things simple whenever possible.
>> >
>> > It sounded to me that the poster was looking for a way to single thread
>> > something. SQL Server 2000 does not appear to honor the lock
>> > (holdlock,
>> > xlock, updlock) until there is an update. Therefore, you have to
>> > update
>> > the
>> > row (whether its the actual data row) or a control table. The first
>> > transaction to update the row will keep all others waiting until it
>> > either
>> > commits or rolls back. IMO, these lock hints are worthless for that
>> > task.
>> >
>> > If I misinterpreted what the poster was looking for, I apoligize and
>> > withdraw my suggestions.
>> >
>> > Just my two cents,
>> > Joe
>> >
>> > "Erland Sommarskog" wrote:
>> >
>> >> Joe from WI (Joefro***@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 weeks.
>> >>
>> >> 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, esq***@sommarskog.se
>> >>
>> >> Books Online for SQL Server 2005 at
>> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> >> Books Online for SQL Server 2000 at
>> >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> >>
>> >
>>
>>
>>
>>
>