How do I do row-level locking on SQL Server?
Thanks,
Nid
Thanks,
Nid
-
-
Re: Row-Level Locking
Nid (kpnid@purdue.e du) writes:[color=blue]
> How do I do row-level locking on SQL Server?[/color]
The question is to unspecific to get a good answer. If all you want
is to be sure that table accesses in general uses row-locking, be
sure that you have indexes on the columns included in the WHERE clause.
If you say:
UPDATE tbl SET p = 0 WHERE col = 12
and there is no index on tbl.col, then this query may lock the entire
table until the transaction commits. If there is an index on col, SQL
Server can find the row directly, and only needs to lock the row.
If you mean something else, please be more specific.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
Books Online for SQL Server SP3 at
-
Re: Row-Level Locking
Hello Erland,
First of all, thank you for the response. I have to confess that I know just
a little bit about SQL Server.
Anyway, I have a ColdFusion application retrieving a record from a table
called ASSIGNMENT. Currently multiple users can update the same assignment
at the same time (which is really bad) although I used
SELECT * FROM ASSIGNMENT WITH (ROWLOCK)
WHERE Assignment_ID = <value>
Assignment_ID is an identity number and it's the primary key of this table.
I think SQL Server automatically created an index for this guy since it's
the primary key.
I'm looking for anything like MS Access form which an editing row is locked
automatically. I don't want to do the lock via the application, but the
table itself if there is a way (I hope).
I found some sites talking about EXEC sp_tableoption '<table name>', 'insert
row lock', 'true'. I guess it's the same thing I'm looking for, but it
works on 6.5, not 2000. :(
I hope I don't confuse you.
Nid
"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns956013A 5AC5CYazorman@1 27.0.0.1...[color=blue]
> Nid (kpnid@purdue.e du) writes:[color=green]
> > How do I do row-level locking on SQL Server?[/color]
>
> The question is to unspecific to get a good answer. If all you want
> is to be sure that table accesses in general uses row-locking, be
> sure that you have indexes on the columns included in the WHERE clause.
> If you say:
>
> UPDATE tbl SET p = 0 WHERE col = 12
>
> and there is no index on tbl.col, then this query may lock the entire
> table until the transaction commits. If there is an index on col, SQL
> Server can find the row directly, and only needs to lock the row.
>
> If you mean something else, please be more specific.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]
Comment
-
Re: Row-Level Locking
Nid,
It's not a good idea to attempt to hold row locks like this for a long
period of time. It can cause blocking of other types of operations and will
help to ruin scalability of a system.
Instead, you're going to have to create your own locking scheme. This might
involve a column on your table indicating whether a row is locked (or by
whom), a column indicating when it was locked, and a job to expire locks on
a regular basis. You might also want to consider a TIMESTAMP column. This
will allow you to still do updates even if the lock has expired, if no other
users have updated the row in question. You can send the timestamp back to
the application and before the app writes the new data, it can check to make
sure the timestamp is the same.
Finally, you should consider using an INSTEAD OF trigger for UPDATEs to the
table, to ensure that every app follows the locking rules, so that you won't
have to duplicate the code in many stored procedures or applications.
"Nid" <kpnid@purdue.e du> wrote in message
news:chqna8$s9j $1@mozo.cc.purd ue.edu...[color=blue]
>
> I'm looking for anything like MS Access form which an editing row is[/color]
locked[color=blue]
> automatically. I don't want to do the lock via the application, but the
> table itself if there is a way (I hope).[/color]
Comment
-
Re: Row-Level Locking
Thanks Adam, I like you idea. I sort of thinking about having my own
locking system, but didn't have time to think about the design yet and your
suggestion is so great!
I still want to know whether or not we can set the row-level locking in SQL
Server 2000 though. If so, can anyone tell me how to do it and can we set
the locking time?
Thanks,
Nid
"Adam Machanic" <amachanic@hotm ail._removetoem ail_.com> wrote in message
news:4140eef0$0 $6909$61fed72c@ news.rcn.com...[color=blue]
> Nid,
>
> It's not a good idea to attempt to hold row locks like this for a long
> period of time. It can cause blocking of other types of operations and[/color]
will[color=blue]
> help to ruin scalability of a system.
>
> Instead, you're going to have to create your own locking scheme. This[/color]
might[color=blue]
> involve a column on your table indicating whether a row is locked (or by
> whom), a column indicating when it was locked, and a job to expire locks[/color]
on[color=blue]
> a regular basis. You might also want to consider a TIMESTAMP column.[/color]
This[color=blue]
> will allow you to still do updates even if the lock has expired, if no[/color]
other[color=blue]
> users have updated the row in question. You can send the timestamp back[/color]
to[color=blue]
> the application and before the app writes the new data, it can check to[/color]
make[color=blue]
> sure the timestamp is the same.
>
> Finally, you should consider using an INSTEAD OF trigger for UPDATEs to[/color]
the[color=blue]
> table, to ensure that every app follows the locking rules, so that you[/color]
won't[color=blue]
> have to duplicate the code in many stored procedures or applications.
>
>
>
> "Nid" <kpnid@purdue.e du> wrote in message
> news:chqna8$s9j $1@mozo.cc.purd ue.edu...[color=green]
> >
> > I'm looking for anything like MS Access form which an editing row is[/color]
> locked[color=green]
> > automatically. I don't want to do the lock via the application, but the
> > table itself if there is a way (I hope).[/color]
>
>[/color]
Comment
-
Re: Row-Level Locking
"N" <kpnid@purdue.e du> wrote in message
news:chr4p8$5f1 $1@mozo.cc.purd ue.edu...[color=blue]
> Thanks Adam, I like you idea. I sort of thinking about having my own
> locking system, but didn't have time to think about the design yet and[/color]
your[color=blue]
> suggestion is so great!
>
> I still want to know whether or not we can set the row-level locking in[/color]
SQL[color=blue]
> Server 2000 though. If so, can anyone tell me how to do it and can we set
> the locking time?
>[/color]
It is done automatically. There's nothing to set.
You can provide lockhints on your selects though.
Not sure what you mean by "locking time". Once you lock a row, it's locked
until you release it.
Seriously though, the method you want to go with I don't think is good.
Comment
-
Re: Row-Level Locking
Greg, obviously it's not done automatically (at least not through my CF
appl.). That's why I asked for help. ^_^
SELECT * FROM ASSIGNMENT WITH (ROWLOCK) WHERE Assignment_ID = <value> <-----
Is this the lockhints you are referring to? If yes, I did try but it didn't
work.
About the locking time, I wonder if we can lock a record when it's open, can
we set to lock it for 10 minutes, something like that?
What you said about automatically release, I guess (again I guess) it would
work for exclusive lock (UPDATE/INSERT/DELETE), but not shared lock
(SELECT).
Anyway, please ignore what I will do or not do; it is good or bad idea, etc.
I just want to find out the answers to the questions below. Is there anyone
can help me? Thanks in advance!
------------------------------------------------------------------------
I still want to know whether or not we can set the row-level locking in SQL
Server 2000 though. If so, can anyone tell me how to do it and can we set
the locking time?
------------------------------------------------------------------------
Nid
"Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co m> wrote in message
news:x%80d.2052 2$2s.10099@twis ter.nyroc.rr.co m...[color=blue]
>
> "N" <kpnid@purdue.e du> wrote in message
> news:chr4p8$5f1 $1@mozo.cc.purd ue.edu...[color=green]
> > Thanks Adam, I like you idea. I sort of thinking about having my own
> > locking system, but didn't have time to think about the design yet and[/color]
> your[color=green]
> > suggestion is so great!
> >
> > I still want to know whether or not we can set the row-level locking in[/color]
> SQL[color=green]
> > Server 2000 though. If so, can anyone tell me how to do it and can we[/color][/color]
set[color=blue][color=green]
> > the locking time?
> >[/color]
>
> It is done automatically. There's nothing to set.
>
> You can provide lockhints on your selects though.
>
> Not sure what you mean by "locking time". Once you lock a row, it's[/color]
locked[color=blue]
> until you release it.
>
> Seriously though, the method you want to go with I don't think is good.
>
>[/color]
Comment
-
Re: Row-Level Locking
"Nid" <kpnid@purdue.e du> wrote in message
news:chsc5a$l4k $1@mozo.cc.purd ue.edu...[color=blue]
> Greg, obviously it's not done automatically (at least not through my CF
> appl.). That's why I asked for help. ^_^
>[/color]
It is done automatically.. . unless it gets escalated to a "bigger" lock,
i.e. a extant, table, etc.
[color=blue]
> SELECT * FROM ASSIGNMENT WITH (ROWLOCK) WHERE Assignment_ID = <value>[/color]
<-----[color=blue]
> Is this the lockhints you are referring to? If yes, I did try but it[/color]
didn't[color=blue]
> work.[/color]
Define didn't work. That should work.
Or better yet, include some DDL and a repro script here so folks can test
out what you're seeing.
[color=blue]
>
> About the locking time, I wonder if we can lock a record when it's open,[/color]
can[color=blue]
> we set to lock it for 10 minutes, something like that?[/color]
No.
[color=blue]
>
> What you said about automatically release, I guess (again I guess) it[/color]
would[color=blue]
> work for exclusive lock (UPDATE/INSERT/DELETE), but not shared lock
> (SELECT).[/color]
Ok, it sounds like the issue you're having is you don't want a shared lock?
[color=blue]
>
> Anyway, please ignore what I will do or not do; it is good or bad idea,[/color]
etc.[color=blue]
> I just want to find out the answers to the questions below. Is there[/color]
anyone[color=blue]
> can help me? Thanks in advance![/color]
[color=blue]
> ------------------------------------------------------------------------
> I still want to know whether or not we can set the row-level locking in[/color]
SQL[color=blue]
> Server 2000 though. If so, can anyone tell me how to do it and can we set
> the locking time?
> ------------------------------------------------------------------------
> Nid
>
>
> "Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co m> wrote in[/color]
message[color=blue]
> news:x%80d.2052 2$2s.10099@twis ter.nyroc.rr.co m...[color=green]
> >
> > "N" <kpnid@purdue.e du> wrote in message
> > news:chr4p8$5f1 $1@mozo.cc.purd ue.edu...[color=darkred]
> > > Thanks Adam, I like you idea. I sort of thinking about having my own
> > > locking system, but didn't have time to think about the design yet and[/color]
> > your[color=darkred]
> > > suggestion is so great!
> > >
> > > I still want to know whether or not we can set the row-level locking[/color][/color][/color]
in[color=blue][color=green]
> > SQL[color=darkred]
> > > Server 2000 though. If so, can anyone tell me how to do it and can we[/color][/color]
> set[color=green][color=darkred]
> > > the locking time?
> > >[/color]
> >
> > It is done automatically. There's nothing to set.
> >
> > You can provide lockhints on your selects though.
> >
> > Not sure what you mean by "locking time". Once you lock a row, it's[/color]
> locked[color=green]
> > until you release it.
> >
> > Seriously though, the method you want to go with I don't think is good.
> >
> >[/color]
>
>[/color]
Comment
-
Re: Row-Level Locking
On Fri, 10 Sep 2004 09:02:14 -0500, Nid wrote:
[color=blue]
>Greg, obviously it's not done automatically (at least not through my CF
>appl.). That's why I asked for help. ^_^
>
>SELECT * FROM ASSIGNMENT WITH (ROWLOCK) WHERE Assignment_ID = <value> <-----
>Is this the lockhints you are referring to? If yes, I did try but it didn't
>work.
>
>About the locking time, I wonder if we can lock a record when it's open, can
>we set to lock it for 10 minutes, something like that?
>
>What you said about automatically release, I guess (again I guess) it would
>work for exclusive lock (UPDATE/INSERT/DELETE), but not shared lock
>(SELECT).
>
>Anyway, please ignore what I will do or not do; it is good or bad idea, etc.
>I just want to find out the answers to the questions below. Is there anyone
>can help me? Thanks in advance!
>------------------------------------------------------------------------
>I still want to know whether or not we can set the row-level locking in SQL
>Server 2000 though. If so, can anyone tell me how to do it and can we set
>the locking time?
>------------------------------------------------------------------------
>Nid[/color]
Hi Nid,
You seem to have some misunderstandin gs about locking.
The default behaviour of lockings is as follows:
* When reading data (SELECT), a shared lock is issued; this prevents
modification of the data but allows simultaneous reading by others. This
lock is only held during the execution of the statement - as soon as the
data is returned, the lock will be released.
* When modifying data (INSERT, UPDATE, DELETE), an exclusive lock is
issued; this prevents both modification and reading to the data. This lock
will be held for the entire duration of the transaction - the lock will
only be released after a COMMIT TRANSACTION or ROLLBACK TRANSACTION has
been processed.
I'm pretty sure that your application DOES lock the data it's reading. But
I'm also pretty sure that these locks are only present during the read
operation and released afterwards, as that is the default behaviour.
There are various settings that force a shared lock to remain active for
the entire duration of a transaction, but these should really only be used
in short running transactions, not while a row is on the screen and the
program awaits user input. Note that locks will block other connections
that request the same data!
The usual way to control concurrency when users enter data is to read the
data without locking it, wait for user input and then verify that the data
in the table is unchanged before doing the update. If it's changed,
someone else has performed an update before you did; the application
should inform the user, reread the data and allow the user to re-enter the
update. This is called "optimistic concurrency control".
A small sample, showing how an employee is read and how the update may be
performed:
SELECT @FirstName = FirstName, @LastName = LastName, @Function = Function
FROM Employees
WHERE EmpID = @EmpID
SET @oldFirstName = @FirstName
SET @oldLastName = @LastName
SET @oldFunction = @Function
(show data on screen; await user input)
(if user has changed any data, attempt to update)
UPDATE Employees
SET FirstName = @FirstName, LastName = @LastName, Function = @Function
WHERE EmpID = @EmpID
AND FirstName = @oldFirstName -- Check if nobody else
AND LastName = @oldLastName -- changed any of these
AND Function = @oldFunction -- before we had a chance.
IF @@ROWCOUNT = 0
BEGIN
here comes error handling in case someone else
changed the row before we did
END
This becomes unwieldy if there's a lot of columns. Also, columns that
allow NULL values require messier code than this. An alternative is to add
one special column with the ROWVERSION (older name: TIMESTAMP) datatype.
SQL Server will automatically change the value in this column every time a
row is changed; instead of comparing all columns, a simple compare of the
current value in the ROWVERSION column to the version when the row was
read will suffice to detect if somebody else touched the row after it was
read.
You also ask about keeping a lock for a fixed time, e.g. 10 minutes. The
only way to achieve this is to issue a command that takes a lock, then use
WAITFOR to force the connection to wait for a while. But I fail to see any
use for such a construction!
USE pubs
BEGIN TRANSACTION
UPDATE authors
SET phone = ''
WHERE au_id = '172-32-1176'
-- This will take an exclusive row lock
WAITFOR DELAY '00:10:00'
-- Wait 10 minutes before proceeding
ROLLBACK TRANSACTION
-- Undo the change and release the lock
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Comment
-
Re: Row-Level Locking
Greg,
With all respect, I won't answer your questions because it's not my interest
to do the row-level locking like I originally planned. So, it's not a good
use of time talking about what problem I got; what didn't work, etc. ^_^
Now, I'm just curious about whether or not we can set the row-level locking
in SQL Server 2000 by using SQL Enterprise Manager. If so, how to do it?
That's all I would like to know.
By the way, you didn't have to reply to my message in the first place, but
you did because you wanted to help and I do really appreciate your time and
everyone's also. Thanks!
Nid
"Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co m> wrote in message
news:zPi0d.2142 7$2s.5370@twist er.nyroc.rr.com ...[color=blue]
>
> "Nid" <kpnid@purdue.e du> wrote in message
> news:chsc5a$l4k $1@mozo.cc.purd ue.edu...[color=green]
> > Greg, obviously it's not done automatically (at least not through my CF
> > appl.). That's why I asked for help. ^_^
> >[/color]
>
> It is done automatically.. . unless it gets escalated to a "bigger" lock,
> i.e. a extant, table, etc.
>
>[color=green]
> > SELECT * FROM ASSIGNMENT WITH (ROWLOCK) WHERE Assignment_ID = <value>[/color]
> <-----[color=green]
> > Is this the lockhints you are referring to? If yes, I did try but it[/color]
> didn't[color=green]
> > work.[/color]
>
> Define didn't work. That should work.
>
> Or better yet, include some DDL and a repro script here so folks can test
> out what you're seeing.
>
>[color=green]
> >
> > About the locking time, I wonder if we can lock a record when it's open,[/color]
> can[color=green]
> > we set to lock it for 10 minutes, something like that?[/color]
>
> No.
>[color=green]
> >
> > What you said about automatically release, I guess (again I guess) it[/color]
> would[color=green]
> > work for exclusive lock (UPDATE/INSERT/DELETE), but not shared lock
> > (SELECT).[/color]
>
> Ok, it sounds like the issue you're having is you don't want a shared[/color]
lock?[color=blue]
>[color=green]
> >
> > Anyway, please ignore what I will do or not do; it is good or bad idea,[/color]
> etc.[color=green]
> > I just want to find out the answers to the questions below. Is there[/color]
> anyone[color=green]
> > can help me? Thanks in advance![/color]
>
>[color=green]
> > ------------------------------------------------------------------------
> > I still want to know whether or not we can set the row-level locking in[/color]
> SQL[color=green]
> > Server 2000 though. If so, can anyone tell me how to do it and can we[/color][/color]
set[color=blue][color=green]
> > the locking time?
> > ------------------------------------------------------------------------
> > Nid
> >
> >
> > "Greg D. Moore (Strider)" <mooregr_delete th1s@greenms.co m> wrote in[/color]
> message[color=green]
> > news:x%80d.2052 2$2s.10099@twis ter.nyroc.rr.co m...[color=darkred]
> > >
> > > "N" <kpnid@purdue.e du> wrote in message
> > > news:chr4p8$5f1 $1@mozo.cc.purd ue.edu...
> > > > Thanks Adam, I like you idea. I sort of thinking about having my[/color][/color][/color]
own[color=blue][color=green][color=darkred]
> > > > locking system, but didn't have time to think about the design yet[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > your
> > > > suggestion is so great!
> > > >
> > > > I still want to know whether or not we can set the row-level locking[/color][/color]
> in[color=green][color=darkred]
> > > SQL
> > > > Server 2000 though. If so, can anyone tell me how to do it and can[/color][/color][/color]
we[color=blue][color=green]
> > set[color=darkred]
> > > > the locking time?
> > > >
> > >
> > > It is done automatically. There's nothing to set.
> > >
> > > You can provide lockhints on your selects though.
> > >
> > > Not sure what you mean by "locking time". Once you lock a row, it's[/color]
> > locked[color=darkred]
> > > until you release it.
> > >
> > > Seriously though, the method you want to go with I don't think is[/color][/color][/color]
good.[color=blue][color=green][color=darkred]
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
Comment
-
Re: Row-Level Locking
Hello Hugo,
This is reall helpful and clear explanation. Thanks!
Please see my response below.
[color=blue]
> You seem to have some misunderstandin gs about locking.[/color]
[color=blue]
> The default behaviour of lockings is as follows:
> * When reading data (SELECT), a shared lock is issued; this prevents
> modification of the data but allows simultaneous reading by others. This
> lock is only held during the execution of the statement - as soon as the
> data is returned, the lock will be released.
> * When modifying data (INSERT, UPDATE, DELETE), an exclusive lock is
> issued; this prevents both modification and reading to the data. This lock
> will be held for the entire duration of the transaction - the lock will
> only be released after a COMMIT TRANSACTION or ROLLBACK TRANSACTION has
> been processed.[/color]
I think my biggest problem is my English. I know what you are saying, but I
can't explain what I meant by locking! So, it's my bad that I confused
others. Please accept my apology!
[color=blue]
> I'm pretty sure that your application DOES lock the data it's reading. But
> I'm also pretty sure that these locks are only present during the read
> operation and released afterwards, as that is the default behaviour.[/color]
It doesn't. I used different browsers to open the same record and I could
update the record on both browsers. Unfortunately, I'm not a DBA; I am a
web programmer. For SQL Server, I only know how to create tables, indexes,
constraint, relationship, transfer data, backup, and restore by using
Enterprise tool. That's pretty much it.
[color=blue]
> There are various settings that force a shared lock to remain active for
> the entire duration of a transaction, but these should really only be used
> in short running transactions, not while a row is on the screen and the
> program awaits user input. Note that locks will block other connections
> that request the same data!
> The usual way to control concurrency when users enter data is to read the
> data without locking it, wait for user input and then verify that the data
> in the table is unchanged before doing the update. If it's changed,
> someone else has performed an update before you did; the application
> should inform the user, reread the data and allow the user to re-enter the
> update. This is called "optimistic concurrency control".[/color]
[color=blue]
> A small sample, showing how an employee is read and how the update may be
> performed:
> SELECT @FirstName = FirstName, @LastName = LastName, @Function = Function
> FROM Employees
> WHERE EmpID = @EmpID
> SET @oldFirstName = @FirstName
> SET @oldLastName = @LastName
> SET @oldFunction = @Function
>
> (show data on screen; await user input)
> (if user has changed any data, attempt to update)
>
> UPDATE Employees
> SET FirstName = @FirstName, LastName = @LastName, Function = @Function
> WHERE EmpID = @EmpID
> AND FirstName = @oldFirstName -- Check if nobody else
> AND LastName = @oldLastName -- changed any of these
> AND Function = @oldFunction -- before we had a chance.
> IF @@ROWCOUNT = 0
> BEGIN
> here comes error handling in case someone else
> changed the row before we did
> END
>
> This becomes unwieldy if there's a lot of columns. Also, columns that
> allow NULL values require messier code than this. An alternative is to add
> one special column with the ROWVERSION (older name: TIMESTAMP) datatype.
> SQL Server will automatically change the value in this column every time a
> row is changed; instead of comparing all columns, a simple compare of the
> current value in the ROWVERSION column to the version when the row was
> read will suffice to detect if somebody else touched the row after it was
> read.
> You also ask about keeping a lock for a fixed time, e.g. 10 minutes. The
> only way to achieve this is to issue a command that takes a lock, then use
> WAITFOR to force the connection to wait for a while. But I fail to see any
> use for such a construction!
> USE pubs
> BEGIN TRANSACTION
> UPDATE authors
> SET phone = ''
> WHERE au_id = '172-32-1176'
> -- This will take an exclusive row lock
> WAITFOR DELAY '00:10:00'
> -- Wait 10 minutes before proceeding
> ROLLBACK TRANSACTION
> -- Undo the change and release the lock[/color]
I'm going to try this when I'm more familiar with SQL Server or have more
access to the DB. (I'm not a DBA. I don't have full privilege).
For now, I go with Erland's suggestion. It's simple enough for amateur like
me and I think I have more control over it. I simply added two columns to
keep track of time open and close of a record. When user selects an
assignment, I check the time close filed, if it is not empty (someone has
this record open), I will redirect him/her to read-only screen. If there is
a release time in this field, I will write to the time open field. I have
JavaScript code to call an action page to write the time to the time close
field when user leaves the page or closes the window. I also have simple
Meta tag to refresh a page every ten minutes.
Well, the way I do might sound stupid to someone, but so far it works like I
want and I'm happy. Thanks again everyone for your information and good
intention.
Comment
-
Re: Row-Level Locking
Hi Nid,
Some comments below:
On Fri, 10 Sep 2004 14:51:59 -0500, Nid wrote:
(snip)[color=blue][color=green]
>> I'm pretty sure that your application DOES lock the data it's reading. But
>> I'm also pretty sure that these locks are only present during the read
>> operation and released afterwards, as that is the default behaviour.[/color]
>It doesn't. I used different browsers to open the same record and I could
>update the record on both browsers.[/color]
Yes, that's the expected behaviour when you use SQL Server's standard
isolation level setting. The details of locks taken and released in this
sceario are as follows:
1. You open a browser and request to show the data of row #17. SQL Server
requests and gets a shared lock for row 17, reads the data, sends the data
to the client (your browser) and releases the lock.
2. You open a second browser and request to show row #17. SQL Server will
(again) request a shared lock for row 17; as the former lock was already
released, this lock will be given, the data is read and sent to the
browser and the lock gets released again.
3. Now you update data in one of the browsers and send it back to SQL
Server. This time, SQL Server will request an exclusive lock. Since all
previous locks have been released, SQL Server will grant you this lock and
update the data for row 17. I assume that you either begin and commit a
transaction when you perform the update or that you don't use explicit
transactions (in which case each statement is treated as a seperate
transaction) - in both cases, the transaction gets committed after the
update is processed and SQL Server will release the exclusive row lock on
row 17.
4. Finally, you change the data in the other browser and send it to the
server. And again, since all previous locks were already released, the
requested exclusive row lock will be taken, the data updated and the lock
will be released again.
If you had implemented optimistic concurrency control (as I described in
my previous message), then there would be no change to the locking, but
the last update would fail to find any rows matching the WHERE clause,
resulting in no update at all - and your application could take any action
you'd like upon seeing that no rows were affected by the update.
It is possible to change this behaviour. If you start a transaction before
reading the row and don't commit it until the update has been performed
(or cancelled by the user), AND you set the transaction isolation level to
repeatable read or serializable, then you'll see that the lock taken when
the first browser reads the row will "stick" for the rest of the
transaction. But that will probably harm your application more than it
will help, because the same scenario (reading the same row in two broweser
windows) will, depending on the locking hints used in the read operation
a) cause a deadlock that will result in the abortion of one of the
processen by SQL Server, or
b) cause a blocking situation where the second browser will not show the
data requested until the dialog in the first window is completely
finished.
(snip)[color=blue]
>I'm going to try this when I'm more familiar with SQL Server or have more
>access to the DB. (I'm not a DBA. I don't have full privilege).[/color]
Please ask your DBA to at least give you access to Query Analyzer. Making
tables, views etc in QA gives you much more control than EM gives you. And
QA is better for debugging - not only because it has a debugging facility
built in, but also because it doesn't try to do any of the creative stuff
with error messages that some clients seem to do.
[color=blue]
>For now, I go with Erland's suggestion. It's simple enough for amateur like
>me and I think I have more control over it.[/color]
(snip)[color=blue]
>Well, the way I do might sound stupid to someone, but so far it works like I
>want and I'm happy. Thanks again everyone for your information and good
>intention.[/color]
If this way makes it work as you wish, you understand it enough to be able
to control it and it doesn't cause any errors, then there should be no
reason not to go for it.
On the other hand ... did you consider the possibility that someone might
just close the browser window, experience power loss or find some other
way to disconnect from the database in a dramatic and unexpected way? You
should have some way to clean up the open and close time of the affected
rows after such a scenario!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Comment
-
Re: Row-Level Locking
Nid (kpnid@purdue.e du) writes:[color=blue][color=green]
>> I'm pretty sure that your application DOES lock the data it's reading.
>> But I'm also pretty sure that these locks are only present during the
>> read operation and released afterwards, as that is the default
>> behaviour.[/color]
>
> It doesn't. I used different browsers to open the same record and I
> could update the record on both browsers. Unfortunately, I'm not a DBA;
> I am a web programmer. For SQL Server, I only know how to create
> tables, indexes, constraint, relationship, transfer data, backup, and
> restore by using Enterprise tool. That's pretty much it.[/color]
This is because if you have not started a transaction, the default
behaviour is auto-commit, which means that once the update has been
performed, the update is committed.
This has the effect that if two users get the same record from the
database, and each performs an update, the last update may overwrite
the first.
This is a little different from ANSI, which mandates implicit transactions.
That is, as soon as you submit an UPDATE, INSERT or DELETE, a new
transaction starts, and you must explicitly commits. SQL Server is able
to perform in this way, if you issue the command SET IMPLICIT_TRANSA CTIONS
ON (which has to be done per connection).
This does not really change things, though, since you have committed the
first user, the second user can still overwrite the first update.
[color=blue]
> For now, I go with Erland's suggestion. It's simple enough for amateur
> like me and I think I have more control over it. I simply added two
> columns to keep track of time open and close of a record. When user
> selects an assignment, I check the time close filed, if it is not empty
> (someone has this record open), I will redirect him/her to read-only
> screen. If there is a release time in this field, I will write to the
> time open field.[/color]
This sounds like a much better solution. However, the honour for the
idea goes to Adam, not me.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
Books Online for SQL Server SP3 at
Comment
-
Re: Row-Level Locking
"Nid" <kpnid@purdue.e du> wrote in message
news:chsuei$63$ 1@mozo.cc.purdu e.edu...[color=blue]
> Greg,
>
>
>
> With all respect, I won't answer your questions because it's not my[/color]
interest[color=blue]
> to do the row-level locking like I originally planned. So, it's not a[/color]
good[color=blue]
> use of time talking about what problem I got; what didn't work, etc. ^_^[/color]
Ok, after reading your other posts, I understand what the problem is.
SQL Server IS doing row-level locking. For the length of your select, etc.
Just as it's designed.
However, you want application level locking which is a completely different
beast. Now that I understand what you want, I can only answer below...
[color=blue]
>
>
>
>
>
> Now, I'm just curious about whether or not we can set the row-level[/color]
locking[color=blue]
> in SQL Server 2000 by using SQL Enterprise Manager. If so, how to do it?
> That's all I would like to know.[/color]
You can NOT do what you want to (which is essentially application locking)
within SQL Server. You have to design your schema and application to lock
rows using some sort of semaphore which the application can check and modify
as appropriate.
One method is to have a timefield which gets checked by the calling
application. If NULL, it updates teh timefield with the current time, does
what you want and when done updates the timefield with a NULL value again.
Meanwhile if the application running on another computer selects the same
row and sees the time is NOT NULL, it then goes on to a different row.
You can handle "timeouts" one of two ways. The calling application can check
to see if the time in the timefield is more than X minutes old and if so,
treat it the same as NULL. I would not do it this way. (btw, if you do, make
sure all the time comparisions are done on the server side using the server
time, otherwise a client X minutes out of synch will cause problems.)
Otherwise, I might have a scheduled task on the server running every X
minutes looking for rows that have not been set to NULL. It would check if
the calling app still had an open connection. If so, it may leave it,
otherwise it sets it to NULL.
In any case, what you want can not be done automatically by SQL Server. In
SQL Server terms, the locks last the length of the transaction.
So a "select * from foo where ID='bar'" the shared locks just the length of
time it takes for SQL Server to retrieve the data. This is why you can
update the row from either client. So basically, to sum up and reiterate,
you'll have to roll your own.
[color=blue]
>
>
>
> By the way, you didn't have to reply to my message in the first place, but
> you did because you wanted to help and I do really appreciate your time[/color]
and[color=blue]
> everyone's also. Thanks!
>[/color]
Well, none of us HAVE to, but we do like to. Helps keep us on our toes to.
Hope this helps and good luck.
Comment
-
Re: Row-Level Locking
Nid (kpnid@purdue.e du) writes:[color=blue]
> For now, I go with Erland's suggestion. It's simple enough for amateur
> like me and I think I have more control over it. I simply added two
> columns to keep track of time open and close of a record. When user
> selects an assignment, I check the time close filed, if it is not empty
> (someone has this record open), I will redirect him/her to read-only
> screen. If there is a release time in this field, I will write to the
> time open field. I have JavaScript code to call an action page to write
> the time to the time close field when user leaves the page or closes the
> window. I also have simple Meta tag to refresh a page every ten
> minutes.[/color]
There might still be problems if the server shuts down expectedly or
whatever reason that causes the user to lose connection without the
Javascript code to fire. This can be handled with some logic that does
deems a lock as stale - for instance a lock being more than 30 minutes
old.
Yet an alternative, and closer to your original approach is to use
application locks. Application locks are handled by the SQL Server
Engine, but you are the one who define the locked resources. The main
advantage with application locks is that they will go away when something
disconnects.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
Books Online for SQL Server SP3 at
Comment
Comment