record, page, table locking

Hello,
I have set up a sql server database with three tables, and I am using MS Acc
ess as the front end for user view, inserts, and updates.
The transaction properties for the database are set as follows:
lock_timeout = 1800
Session isolation level = serializable
access mode is multiuser.
Recently, one person had the MS Access database open and made deletions with
out any problem, closed the database and then opened it later to delete more
records from a table but recieved this message "the microsoft jet db engine
stopped the process becaus
e you and another user are attempting to change the same data at the same ti
me." error 2197. but they are the only one's who have the db open. Would any
one be able to tell me which setting I have misconfigured?
Thanks,
ChiekoThis is an Access issue. I suggest you post the question to an Access group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Chieko Kuroda" <anonymous@.discussions.microsoft.com> wrote in message
news:22D5A1D3-6B23-4E53-9809-F13CA9C97A20@.microsoft.com...
> Hello,
> I have set up a sql server database with three tables, and I am using MS
Access as the front end for user view, inserts, and updates.
> The transaction properties for the database are set as follows:
> lock_timeout = 1800
> Session isolation level = serializable
> access mode is multiuser.
> Recently, one person had the MS Access database open and made deletions
without any problem, closed the database and then opened it later to delete
more records from a table but recieved this message "the microsoft jet db
engine stopped the process because you and another user are attempting to
change the same data at the same time." error 2197. but they are the only
one's who have the db open. Would anyone be able to tell me which setting I
have misconfigured?
> Thanks,
> Chieko
>

record, page table locking

Hello,
I have set up a sql server database with three tables, and I am using MS Acc
ess as the front end for user view, inserts, and updates.
The transaction properties for the database are set as follows:
lock_timeout = 1800
Session isolation level = serializable
access mode is multiuser.
Recently, one person had the MS Access database open and made deletions with
out any problem, closed the database and then opened it later to delete more
records from a table but recieved this message "the microsoft jet db engine
stopped the process becaus
e you and another user are attempting to change the same data at the same ti
me." error 2197. but they are the only one's who have the db open. Would any
one be able to tell me which setting I have misconfigured?
Thanks,
ChiekoThis can happen with a few different scenarios. Basically,
ODBC will use a timestamp to determine if the record has
been updated. If there is no timestamp column, it compares
all column values to see if the record has been updated.
Some situations can cause Access to become "confused" as to
whether the record has been update - such as having float
data types. Try adding a timestamp column to the SQL table
and see if the fixes the problem. There should be some
Access knowledge base articles on this subject that you may
want to search for.
-Sue
On Thu, 18 Mar 2004 11:21:05 -0800, "chieko Kuroda"
<anonymous@.discussions.microsoft.com> wrote:

>Hello,
>I have set up a sql server database with three tables, and I am using MS Ac
cess as the front end for user view, inserts, and updates.
>The transaction properties for the database are set as follows:
>lock_timeout = 1800
>Session isolation level = serializable
>access mode is multiuser.
>Recently, one person had the MS Access database open and made deletions without any
problem, closed the database and then opened it later to delete more records from a
table but recieved this message "the microsoft jet db engine stopped the process be
cau
se you and another user are attempting to change the same data at the same time." error 219
7. but they are the only one's who have the db open. Would anyone be able to tell me which
setting I have misconfigured?
>Thanks,
>Chieko
>
>
>

Record locking? Well... not exactly...

Hi... I have just read tons of articles on the (blessed optimistic and
condemned pessimistic) record locking and... I feel like I know nothing ;-)
Actually, it is not only the record locking, what I need, and nobody seems
to descibe this.
Imagine the following scenario.
There is a database with, say 10000 records with some unvalidated data. And
there is an Intranet ASP.NET application which is an interface to the
database in question... and there are 100 pretty girls eager to... uhmm...
use the application and validate the data of course ;-).
The task is to enable the data validation in such a way that two girls NEVER
get the same record for validation (it is just a waste of time). When a girl
presses 'next' or something like that, she gets the access to an unvalidated
(and not being validated) record. This is more than pessimistic locking,
actually. And I missed the most important part - all this has to work
smoothly ;-).
I am thinking about a few solutions to this problem, I developed one once,
actually, basing on transactions with highest isolation level, but this was
not very smooth. Anyway, if anybody can give me a good advice or direct to a
good article, I will be very grateful.
Keep in mind that I might use synchronisation not on the DB level, but on
the level of the web application... maybe this would be faster?
Any comments will be welcome
DW.A combination of OUTPUT and TOP can get you part way there. Your ASP.NET
app would have to clean things up if for some reason the row was not fully
processed...
create table #justone
(
id int identity primary key,
data varchar(max),
inProcess bit default 0,
processed bit default 0
)
insert into #justone values ('asdfawerf', default, default)
insert into #justone values ('1asdfawerf', default, default)
insert into #justone values ('2asdfawerf', default, default)
insert into #justone values ('3asdfawerf', default, default)
insert into #justone values ('4asdfawerf', default, default)
-- this will get a new row every time
update top(1) #justone
set inProcess = 1
OUTPUT deleted.id, deleted.data
where inProcess = 0 and processed = 0
-- do processing of row here in ASP.NET app
-- do another update or whatever to indicate
-- the results of the user validating the data
Dan

> Hi... I have just read tons of articles on the (blessed optimistic and
> condemned pessimistic) record locking and... I feel like I know
> nothing ;-)
> Actually, it is not only the record locking, what I need, and nobody
> seems to descibe this.
> Imagine the following scenario.
> There is a database with, say 10000 records with some unvalidated
> data. And
> there is an Intranet ASP.NET application which is an interface to the
> database in question... and there are 100 pretty girls eager to...
> uhmm...
> use the application and validate the data of course ;-).
> The task is to enable the data validation in such a way that two girls
> NEVER get the same record for validation (it is just a waste of time).
> When a girl presses 'next' or something like that, she gets the access
> to an unvalidated (and not being validated) record. This is more than
> pessimistic locking, actually. And I missed the most important part -
> all this has to work smoothly ;-).
> I am thinking about a few solutions to this problem, I developed one
> once, actually, basing on transactions with highest isolation level,
> but this was not very smooth. Anyway, if anybody can give me a good
> advice or direct to a good article, I will be very grateful.
> Keep in mind that I might use synchronisation not on the DB level, but
> on the level of the web application... maybe this would be faster?
> Any comments will be welcome
> DW.|||Provided I understood you correctly, I tried a similar solution in fact.
All my tables have an 'ID' field, so I added an additional tLocks table
whose records contained a user name, a table name and a record ID. Then
handling the 'next' button was something like:
- begin a transaction,
- delete a previous tLocks record for a current user, if it existed,
- select first not yet validated record from the required table for which no
record exists in tLocks,
- add a new tLocks record for the current user, the requested table and the
appropriate record ID,
- commit the transaction.
Looks OK, doesn't it?
Well, while using the default transaction level, it was very commonplace
that two users happened to lock the same record. Changing the level to the
highest possible value seemed to help... for 2-4 users. When 10 users used
the database simultaneously, they again happened to get the same records at
a time :-/.
Well, I do not understand why this happened, but it did ;-).
Frankly speaking, I would be glad to read some article providing a few
possible solutions to choose from. Or maybe a description of some system
that was tested and worked under a real stress...
DW.|||If you are using TABLOCK then it won't work, it only holds a shared lock
on the table, not an exclusive one.
Between here

>- select first not yet validated record from the required table for
> which no
> record exists in tLocks,
and here

> - add a new tLocks record for the current user, the requested table
someone else might read the same row.
Try using UPDLOCK or TABLOCKX something like
BEGIN TRAN
DECLARE @.id INT
DECLARE @.data VARCHAR(MAX)
SELECT top(1) @.id=id, @.data=data from justone with (UPDLOCK)
where inprocess=0 and processed = 0
SELECT @.id
UPDATE justone set inprocess = 1 where id = @.id
COMMIT TRAN
Dan

> Provided I understood you correctly, I tried a similar solution in
> fact.
> All my tables have an 'ID' field, so I added an additional tLocks
> table whose records contained a user name, a table name and a record
> ID. Then handling the 'next' button was something like:
> - begin a transaction,
> - delete a previous tLocks record for a current user, if it existed,
> - select first not yet validated record from the required table for
> which no
> record exists in tLocks,
> - add a new tLocks record for the current user, the requested table
> and the
> appropriate record ID,
> - commit the transaction.
> Looks OK, doesn't it?
> Well, while using the default transaction level, it was very
> commonplace that two users happened to lock the same record. Changing
> the level to the highest possible value seemed to help... for 2-4
> users. When 10 users used the database simultaneously, they again
> happened to get the same records at a time :-/.
> Well, I do not understand why this happened, but it did ;-).
> Frankly speaking, I would be glad to read some article providing a few
> possible solutions to choose from. Or maybe a description of some
> system that was tested and worked under a real stress...
> DW.
>|||I have written a few workflow type applications and I think my solution
is a bit more simple. I maintain a status column and an optional
deltauser column to keep track of who has "locked" an item to be
worked. Your stored procedure would execute something like this to
retrieve an item:
DECLARE @.LockedID int
SET ROWCOUNT 1
UPDATE yourtable SET Status = 50, deltauser = @.username WHERE status =
0 -- and additional criteria for where clause
SET ROWCOUNT 0
--return results
SELECT * FROM yourtable WHERE id_column = @.LockedID
Because this code updates an item to locked before it selects it, you
will never have 2 users able to retrieve the same record.
When you update the worked item, you update the status to 100 (or
whatever is previously determined for complete). With this numerical
status, you can track multiple steps of work on the same item (150 - in
progress/locked for step 2, 200 = complete for step 2)
By using the deltauser (or lockeduser) you can query the database for
items that that user left locked (by crashing, closing down, etc) and
return the already locked item instead of a new item.
I have been using this locking mechanism for years now - even with
automated applications that continuously hit the DB to retrieve new
work and have never had a problem with users getting the same record.
HTH
Jason|||oops, the update statement below should read:
UPDATE yourtable SET Status = 50, @.LockedID = id_column, deltauser =
@.username WHERE status =
0 -- and additional criteria for where clause
Jason wrote:
> I have written a few workflow type applications and I think my solution
> is a bit more simple. I maintain a status column and an optional
> deltauser column to keep track of who has "locked" an item to be
> worked. Your stored procedure would execute something like this to
> retrieve an item:
> DECLARE @.LockedID int
> SET ROWCOUNT 1
> UPDATE yourtable SET Status = 50, deltauser = @.username WHERE status =
> 0 -- and additional criteria for where clause
> SET ROWCOUNT 0
> --return results
> SELECT * FROM yourtable WHERE id_column = @.LockedID
> Because this code updates an item to locked before it selects it, you
> will never have 2 users able to retrieve the same record.
> When you update the worked item, you update the status to 100 (or
> whatever is previously determined for complete). With this numerical
> status, you can track multiple steps of work on the same item (150 - in
> progress/locked for step 2, 200 = complete for step 2)
> By using the deltauser (or lockeduser) you can query the database for
> items that that user left locked (by crashing, closing down, etc) and
> return the already locked item instead of a new item.
> I have been using this locking mechanism for years now - even with
> automated applications that continuously hit the DB to retrieve new
> work and have never had a problem with users getting the same record.
> HTH
> Jason

Record locking...

Hi Experts,
Here's the scenario of the case...
User1 does a retrieve of information, with the intention of updating it
later. At the same time, User2 does a retrieve of information, with the
intention of updating it later. User1 updates some of the row on the web
server, and then calls an update stored procedure.Then user2 updates some of
the row on the web server, and then calls an update stored procedure.
Some of user1s updates are lost because user2 had "stale" data.
So what a lot of applications do is to implement explicit locking, so that
when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
update it later". The rule is in that case that a locked record cannot also
be locked by someone else
and update can only be called if the record was locked by that user.
We can of course add a column to every table that has to support this
"locking", but I was wondering if SQL Serversystems support some kind of
"lock this record please"?Here's an old archive on this type of issue:
http://tinyurl.com/5qevd
-oj
"Lia" <Lia@.discussions.microsoft.com> wrote in message
news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
> Hi Experts,
> Here's the scenario of the case...
> User1 does a retrieve of information, with the intention of updating it
> later. At the same time, User2 does a retrieve of information, with the
> intention of updating it later. User1 updates some of the row on the web
> server, and then calls an update stored procedure.Then user2 updates some
> of
> the row on the web server, and then calls an update stored procedure.
> Some of user1s updates are lost because user2 had "stale" data.
> So what a lot of applications do is to implement explicit locking, so that
> when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
> update it later". The rule is in that case that a locked record cannot
> also
> be locked by someone else
> and update can only be called if the record was locked by that user.
> We can of course add a column to every table that has to support this
> "locking", but I was wondering if SQL Serversystems support some kind of
> "lock this record please"?|||Hi Experts,
What is usually the system? Is it the person who updates gets the priority
or the person who retrieves (for update) gets a priority.
I have been debating on this for some time and after reading a few books i
got the impression that LOCKING records was the practice in the olden days
and in the current situation ( adLockOptimistic ) the person who clicks
update first get a priority and the other person gets an ERROR! Which can be
trapped using ON Error GOTO statement
Please clarify and help me resolve this issue.
Thanks
Manish
"oj" wrote:

> Here's an old archive on this type of issue:
> http://tinyurl.com/5qevd
>
> --
> -oj
>
> "Lia" <Lia@.discussions.microsoft.com> wrote in message
> news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
>
>|||Hi Manish,
Thanks for your reply on my post. :-) The system will supposed to give the
priority to person who retrieves(for update) in such a way that upon
retrieving, the record will be locked. The lock will then be released after
updating the record or canceling the transaction. And if the other person
select the same record that intend to update it also, message will be given
to inform the person that the record can only be viewed and cannot be update
d
because it is locked for update by another user.
Your suggestions and comments will be highly appreciated.
Thanks,
Lia
"Manish Sawjiani" wrote:
> Hi Experts,
> What is usually the system? Is it the person who updates gets the priority
> or the person who retrieves (for update) gets a priority.
> I have been debating on this for some time and after reading a few books i
> got the impression that LOCKING records was the practice in the olden days
> and in the current situation ( adLockOptimistic ) the person who clicks
> update first get a priority and the other person gets an ERROR! Which can
be
> trapped using ON Error GOTO statement
> Please clarify and help me resolve this issue.
> Thanks
> Manish
> "oj" wrote:
>

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