473,890 Members | 1,381 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 #1
16 8947
Nid (kp***@purdue.e du) writes:
How do I do row-level locking on SQL Server?


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Nid
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" <es****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.0.0.1...
Nid (kp***@purdue.e du) writes:
How do I do row-level locking on SQL Server?


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3
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" <kp***@purdue.e du> wrote in message
news:ch******** **@mozo.cc.purd ue.edu...

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

Jul 20 '05 #4
N
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:41******** **************@ news.rcn.com...
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" <kp***@purdue.e du> wrote in message
news:ch******** **@mozo.cc.purd ue.edu...

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



Jul 20 '05 #5

"N" <kp***@purdue.e du> wrote in message
news:ch******** **@mozo.cc.purd ue.edu...
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?


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.
Jul 20 '05 #6
Nid
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)" <mo************ ****@greenms.co m> wrote in message
news:x%******** **********@twis ter.nyroc.rr.co m...

"N" <kp***@purdue.e du> wrote in message
news:ch******** **@mozo.cc.purd ue.edu...
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?


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.

Jul 20 '05 #7

"Nid" <kp***@purdue.e du> wrote in message
news:ch******** **@mozo.cc.purd ue.edu...
Greg, obviously it's not done automatically (at least not through my CF
appl.). That's why I asked for help. ^_^

It is done automatically.. . unless it gets escalated to a "bigger" lock,
i.e. a extant, table, etc.

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


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

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).
Ok, it sounds like the issue you're having is you don't want a shared lock?

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)" <mo************ ****@greenms.co m> wrote in message news:x%******** **********@twis ter.nyroc.rr.co m...

"N" <kp***@purdue.e du> wrote in message
news:ch******** **@mozo.cc.purd ue.edu...
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?


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.


Jul 20 '05 #8
On Fri, 10 Sep 2004 09:02:14 -0500, Nid wrote:
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


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)
Jul 20 '05 #9
Nid
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)" <mo************ ****@greenms.co m> wrote in message
news:zP******** *********@twist er.nyroc.rr.com ...

"Nid" <kp***@purdue.e du> wrote in message
news:ch******** **@mozo.cc.purd ue.edu...
Greg, obviously it's not done automatically (at least not through my CF
appl.). That's why I asked for help. ^_^

It is done automatically.. . unless it gets escalated to a "bigger" lock,
i.e. a extant, table, etc.

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.


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.


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?


No.

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


Ok, it sounds like the issue you're having is you don't want a shared

lock?

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)" <mo************ ****@greenms.co m> wrote in

message
news:x%******** **********@twis ter.nyroc.rr.co m...

"N" <kp***@purdue.e du> wrote in message
news:ch******** **@mozo.cc.purd ue.edu...
> 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?
>

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.



Jul 20 '05 #10

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
16181
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
1723
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
2861
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
6016
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
9829
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
10836
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
9643
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5856
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
6064
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4683
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
4278
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3287
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.