473,903 Members | 4,817 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Row-Level Locking

Nid
How do I do row-level locking on SQL Server?

Thanks,
Nid
Jul 20 '05
16 8948
Nid
Hello Hugo,

This is reall helpful and clear explanation. Thanks!

Please see my response below.


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

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

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

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.
Jul 20 '05 #11
Hi Nid,

Some comments below:

On Fri, 10 Sep 2004 14:51:59 -0500, Nid wrote:

(snip)
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.It doesn't. I used different browsers to open the same record and I could
update the record on both browsers.


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

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. (snip)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.


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)
Jul 20 '05 #12
Nid (kp***@purdue.e du) writes:
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.
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.


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


This sounds like a much better solution. However, the honour for the
idea goes to Adam, not me.


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #13

"Nid" <kp***@purdue.e du> wrote in message
news:ch******** *@mozo.cc.purdu e.edu...
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. ^_^
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...




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


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!

Well, none of us HAVE to, but we do like to. Helps keep us on our toes to.

Hope this helps and good luck.


Jul 20 '05 #14
Nid (kp***@purdue.e du) writes:
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.


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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #15

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

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.
Can use a SQL Server Agent job for this, as well.
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.


That would be fine if it's okay to keep persistent connections; in a web
environment this would be difficult to maintain (have to keep track of
sessions and sticky servers), potentially dangerous (certain kinds of
connection objects are not well-suited for keeping in session memory), and
would ruin benefits of connection pooling. I would strongly recommend
against keeping connections open in between requests.
Jul 20 '05 #16
Adam Machanic (amachanic@hotm ail._removetoem ail_.com) writes:
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
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.


That would be fine if it's okay to keep persistent connections; in a
web environment this would be difficult to maintain (have to keep track
of sessions and sticky servers), potentially dangerous (certain kinds of
connection objects are not well-suited for keeping in session memory),
and would ruin benefits of connection pooling. I would strongly
recommend against keeping connections open in between requests.


Good point.

It goes without saying the original intended scheme by locking rows through
SQL Server is also a failure in this context.

Application lock is more like to be a solution for a non-web application
where you cannot accept pessimistic locking. But I will have to admit that
I would think twice before I took that route.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #17

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2274
by: lawrence | last post by:
I had some code that worked fine for several weeks, and then yesterday it stopped working. I'm not sure what I did. Nor can I make out why it isn't working. I'm running a query that should return 3 items from the database. I then get a count of the return, which correctly tells me that I've got 3 items. I then go into a for loop which I expect to loop 3 times and print out the 3 items. Here is where things get strange: it loops 3 times and...
10
22244
by: AdamG | last post by:
I am trying hard for days now to add and delete rows to a table. I could really use some help... Each row contains two buttons (images) - 'add row' and 'delete row'. When the user clicks add row within a specific cell/row, the index of that row should be passed to a function that creates a new row using that index (the new row should be added directly below the row where the user clicked. The new row should contain all of the cells and...
7
16182
by: Micha? | last post by:
Hello this is my problem: <script language="javascript"> function Show() { RowNumber="???"; // I trying this.rowIndex CellNumber="???"; // TableID="???"; // :-( alert(RowNumber);
0
3137
by: Dave Elliott | last post by:
After inserting a new data row to a DataTable that is bound to a datagrid, I am unable to change data in a row that is after the newly added row without getting bizarre results. I have added the full code for the test below. Create a project drop in the code and run. Any help would be appreciated. Cheers,
2
9936
by: Chris Plowman | last post by:
Hi all, I was wondering if anyone can help me with a really annoying problem I have been having. I made a derived datagrid class that will select the row when a user clicks anywhere on a cell (multi-select without modifier keys). I got that working fine, but I also wanted to keep rows selected after a sort, which I do by storing the row's id in an arraylist. The idea was to do the sort and then go back and re-select the rows with that...
2
1724
by: NDady via DotNetMonster.com | last post by:
Hi, I have a datagrid populated from a dataset. On every row in the grid I have a delete button. When the user presses on the delete button I remove the row from the dataset and rebind the datagrid. The problem is that after a couple of delete the index in the dataset does not match the index in the grid and the wrong record i deleted from the dataset. How can I solve this Problem? I am using the following procedure: Private Sub...
2
14704
by: michael sorens | last post by:
I tried to do a simple operation on a Windows Form in VS2005 inside a key_down handler: if (e.Control && e.Shift && e.KeyCode == Keys.V) { int selectedRowIndex = dataGridView.SelectedCells.RowIndex; dataGridView.Rows.AddCopy(selectedRowIndex); } So when the user presses Ctrl-Shft-V, a copy of the first row of a user's
6
2295
by: RoomfulExpress | last post by:
Here's the problem that I'm having- I'm trying to pull in 2 fields from my database and place them in the title of the HTML. I'm connecting to the db and selecting everything exactly the same as I am doing below, and it works fine. For some reason it's not pulling in the fields. Any ideas? Here's the link to the actual page I'm working on. http://www.roomfulexpress.com/newsite/php/familyprofile.php?FAMILY_CD=558167959 Please see below...
6
2862
by: Miro | last post by:
Sorry for the cross post. I am stuck. I have a datagridview for poker rounds. Basically there are 3 columns in this datagridview. "Round" "SmallBlind" "BigBlind" I have an issue when I tab through the new row being added. It does not 'Add' that row, nor setup the 'next blank add row' so I can continue to tab
4
6017
by: btreddy | last post by:
Hii experts, I've been trying for this but i didn succeeded.The problem is I've a datagird which is having 2 cols displaying name and emial id .wht i want is when i select a paricular row from the gridview i want to capture the emial id value of tht paricular row. I've added this code in the even onrowdatatbound. protected void GridViewParticipants_OnRowDataBound(object sender, GridViewRowEventArgs e) / . . if (e.Row.RowType ==...
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10872
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5893
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6085
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4725
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3323
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.