[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

rowlock question

Posted on 2005-04-29
2
Medium Priority
?
1,484 Views
Last Modified: 2008-02-26
I'm not sure I fully understand rowlock, even after reading about it in the BOL and all the web references I could find.

Does rowlock lock a single row just as it would appear?

If so would this only be appropriate for updates or deletes that only access a single row?

If not, when are the correct times to use with (rowlock)?
0
Comment
Question by:sifuhall
2 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 13894344
rowlock is appropriate for simultaneous access of multiple users to small number of different of rows in the same table.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13894541
>> Does rowlock lock a single row just as it would appear? <<
Not necessarily.  It causes a row-level lock (shared lock) on all rows read by the statement, rather than just on the page or table as SQL might normally do.


>> If so would this only be appropriate for updates or deletes that only access a single row? <<
It's actually not necessary in that case.  If you do an UPDATE or DELETE, SQL will always lock the row, so you don't have to specify it.


>> If not, when are the correct times to use with (rowlock)? <<
Similar to isplaney, plus a little clarification:
For read access of multiple, simultaneous users to small number of different rows in the same table.
Also, if used with other hints, such as XLOCK (exclusive lock), to limit the scope of the exclusive locks as much as possible.  For example, if you wanted to read a row and then have *no one* else be able to read that row until you are done with it, do this:

SELECT ...
FROM ... WITH (XLOCK, ROWLOCK)
WHERE id = 55  --only 1 row selected

If you don't, SQL might apply the XLOCK to more than just the row (for example, the whole page), and you would lock many rows unnecessarily.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question