row level locking in SQL Server 7

row level locking in SQL Server 7

Post by Robert Wolanczu » Wed, 23 May 2001 17:31:41



Hi,

Anybody tried that? I cannot get SQL Server to lock a single record without
locking a page or a table ...

BOL says that the type of lock is determined and escalated if necessary
automatically:
"Lock escalation thresholds are determined dynamically by SQL Server and require
no configuration."

Consider the following scenario:

create table Test (a int)
insert into Test values (1)
insert into Test values (2)
begin transaction
update b with (rowlock) set a = 1 where a = 1
-- POINT 1
commit transaction

At POINT 1, the following locking occurs:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
22     10     0           0      DB                    S        GRANT
22     10     581173416   0      PAG  1:86             IX       GRANT
22     10     581173416   0      RID  1:86:0           X        GRANT
22     10     581173416   0      TAB                   IX       GRANT

Why is page and table locked as well?
How to achive one record to be locked only?

Thanks,
Robert

 
 
 

row level locking in SQL Server 7

Post by Dejan Sark » Wed, 23 May 2001 18:14:28


Robert,

you got the row-level lock; additional locks are so-called intent locks. Do
read the "Understanding Locking in SQL Server" in BOL.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

 
 
 

row level locking in SQL Server 7

Post by Robert Wolanczu » Wed, 23 May 2001 20:42:43



>>you got the row-level lock; additional locks are so-called intent locks.

I understand what you said, but there is still something wrong I think.
Consider the same simple scenario:

   create table Test (a int)
   insert into Test values (1)
   insert into Test values (2)
   begin transaction
   update Test with (rowlock) set a = 1 where a = 1
   -- POINT 1
   commit transaction

The following statement executed at POINT 1 from OTHER CONNECTION gets on hold:

   update Test with (rowlock) set a = 2 where a = 2

with the following locks:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51     6      0           0      DB                    S        GRANT
51     6      1093578934  0      RID  1:314:0          X        GRANT
51     6      1093578934  0      PAG  1:314            IX       GRANT
51     6      1093578934  0      TAB                   IX       GRANT
53     1      85575343    0      TAB                   IS       GRANT
53     6      0           0      DB                    S        GRANT
54     6      0           0      DB                    S        GRANT

Quote:>>>54     6      1093578934  0      RID  1:314:0          U        WAIT

54     6      1093578934  0      PAG  1:314            IU       GRANT
54     6      1093578934  0      TAB                   IX       GRANT

Why am I getting the WAIT status for update lock on another row. Row-level
locking should allow for update, shouldn't it?

Robert

 
 
 

row level locking in SQL Server 7

Post by Dan Guzma » Wed, 23 May 2001 22:36:42


Try adding a primary key.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------



>>you got the row-level lock; additional locks are so-called intent locks.

I understand what you said, but there is still something wrong I think.
Consider the same simple scenario:

   create table Test (a int)
   insert into Test values (1)
   insert into Test values (2)
   begin transaction
   update Test with (rowlock) set a = 1 where a = 1
   -- POINT 1
   commit transaction

The following statement executed at POINT 1 from OTHER CONNECTION gets on
hold:

   update Test with (rowlock) set a = 2 where a = 2

with the following locks:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51     6      0           0      DB                    S        GRANT
51     6      1093578934  0      RID  1:314:0          X        GRANT
51     6      1093578934  0      PAG  1:314            IX       GRANT
51     6      1093578934  0      TAB                   IX       GRANT
53     1      85575343    0      TAB                   IS       GRANT
53     6      0           0      DB                    S        GRANT
54     6      0           0      DB                    S        GRANT

Quote:>>>54     6      1093578934  0      RID  1:314:0          U        WAIT

54     6      1093578934  0      PAG  1:314            IU       GRANT
54     6      1093578934  0      TAB                   IX       GRANT

Why am I getting the WAIT status for update lock on another row. Row-level
locking should allow for update, shouldn't it?

Robert

 
 
 

row level locking in SQL Server 7

Post by Wayne Snyde » Wed, 23 May 2001 23:17:28


You did NOT get row level lock.... an index is required for row level
locks...try creating a primary key constraint, then re-try your test, it
should work as you expect.

--
Wayne Snyder SQL Server MVP
IKON Education Services, C*te, NC
(Please Respond to newsgroups)

 
 
 

row level locking in SQL Server 7

Post by Dan Guzma » Wed, 23 May 2001 23:25:49


Sorry, I posted in haste.  My previous post should have instructed to add a
primary key or index on column a.

The issue is that the SELECT must perform a table scan when no index is
present and will be blocked even with row-level locking.  Consequently, the
SELECT needs a useful index in order to avoid reading the row being updated.

Hope this helps.


Quote:> Try adding a primary key.

> Hope this helps.

 
 
 

row level locking in SQL Server 7

Post by Lewis Bruc » Thu, 24 May 2001 05:04:32


Well, actually you did get a row lock (the RID 1:86:0 lock).  That is the
only exclusive lock.  The other two locks (TAB and PAG 1:86) are "intent"
locks, not exclusive locks.  Intent locks do not conflict with each other.
See Kalen Delaney's "Inside SQL Server" or any book on database concurrency
for a description of intent locks.

Contrary to what Mr. Guzman and Mr. Snyder posted, you do NOT need an index
to get row-level locking.  However, the use of an index will allow SQL
Server to come up with an execution plan which will not escalate to
exclusive page or table locks.  A clustered index is also needed to
implement the repeatable read and serializable isolation levels without
using a table lock.

Lewis Bruck


Hi,

Anybody tried that? I cannot get SQL Server to lock a single record without
locking a page or a table ...

BOL says that the type of lock is determined and escalated if necessary
automatically:
"Lock escalation thresholds are determined dynamically by SQL Server and
require
no configuration."

Consider the following scenario:

create table Test (a int)
insert into Test values (1)
insert into Test values (2)
begin transaction
update b with (rowlock) set a = 1 where a = 1
-- POINT 1
commit transaction

At POINT 1, the following locking occurs:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
22     10     0           0      DB                    S        GRANT
22     10     581173416   0      PAG  1:86             IX       GRANT
22     10     581173416   0      RID  1:86:0           X        GRANT
22     10     581173416   0      TAB                   IX       GRANT

Why is page and table locked as well?
How to achive one record to be locked only?

Thanks,
Robert

 
 
 

row level locking in SQL Server 7

Post by Robert Wolanczu » Thu, 24 May 2001 08:54:13


Dan, Wayne, Lewis, Dejan - Thank you, it works.
Indeed, an index (or simply primary key) is needed to prevent blocking table scan
which gets blocked.

For everybody else, a complete scenario below.
The 'with (rowlock)' clause is not neccessary in SQL Server 7/2000.

Cheers,
Robert
______________________________

CONNECTION 1:
   create table Test (a int not null)
   alter table Test add primary key (a)

   insert into Test values (1)
   insert into Test values (2)

   begin transaction
   update Test with (rowlock) set a = 1 where a = 1
   -- POINT 1
   commit transaction

CONNECTION 2:
   -- executed at POINT 1 of connection 1 does not get blocked.
   update Test with (rowlock) set a = 2 where a = 2

 
 
 

row level locking in SQL Server 7

Post by Dejan Sark » Thu, 24 May 2001 15:41:44


Sorry,

I looked only at the sp_lock output (
spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
22     10     0           0      DB                    S        GRANT
22     10     581173416   0      PAG  1:86             IX       GRANT
22     10     581173416   0      RID  1:86:0           X        GRANT
22     10     581173416   0      TAB                   IX       GRANT
) in the post, and from there can be seen only one X row-lock and the intent
locks.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

 
 
 

row level locking in SQL Server 7

Post by Lewis Bruc » Thu, 24 May 2001 16:38:19


OK, this problem is because you don't have an index on the "a" column.  The
server needs to do a table scan to execute the query.  Before it can
evaluate the predicate for each row, it needs to get a shared or update lock
on the row (in your case, an update lock).  This conflicts with the
exclusive row lock the other transaction has.

The suggestions by Dan Guzman and Wayne Snyder avoid the scan blocking and
would allow you to take advantage of the row level locking.

Lewis Bruck



>>you got the row-level lock; additional locks are so-called intent locks.

I understand what you said, but there is still something wrong I think.
Consider the same simple scenario:

   create table Test (a int)
   insert into Test values (1)
   insert into Test values (2)
   begin transaction
   update Test with (rowlock) set a = 1 where a = 1
   -- POINT 1
   commit transaction

The following statement executed at POINT 1 from OTHER CONNECTION gets on
hold:

   update Test with (rowlock) set a = 2 where a = 2

with the following locks:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51     6      0           0      DB                    S        GRANT
51     6      1093578934  0      RID  1:314:0          X        GRANT
51     6      1093578934  0      PAG  1:314            IX       GRANT
51     6      1093578934  0      TAB                   IX       GRANT
53     1      85575343    0      TAB                   IS       GRANT
53     6      0           0      DB                    S        GRANT
54     6      0           0      DB                    S        GRANT

Quote:>>>54     6      1093578934  0      RID  1:314:0          U        WAIT

54     6      1093578934  0      PAG  1:314            IU       GRANT
54     6      1093578934  0      TAB                   IX       GRANT

Why am I getting the WAIT status for update lock on another row. Row-level
locking should allow for update, shouldn't it?

Robert

 
 
 

1. Row-level locking in SQL Server 7

Has anybody had any expereince with row-level locking with SQL Server 7?
Here is my problem:

I have a table that will be accesses by multiple users at the same time.
Once the first user (whoever that might be) accesses the first row of
that table, I need to lock that row so that the next user will get the
second row, and the third user will get the third row and so forth. I
have tried multiple ways to do row level locking but to no avail (this
is part of the SP so I can't set the isolation level unfortnatelly). Any
insight on how to solve this issue or in general how to resolve the
issue of locking a row under these conditions would be more then
appreciated.

TIA.

-Albina

2. 17066-NC-RALEIGH/DURHAM-MVS-NetWare-Novell-ORACLE-TCP/IP-UNIX-Windows-Oracle/MVS DBA

3. Row-level locking in sql-server 6.5

4. Move an Database from an SQL 7.0 server to an other SQL 7.0 server

5. How do I apply row-level locking in SQL server with ADO

6. Q: B-TREE FILER with BDE ?

7. Row Level locking at SQL server level..... Please Help .........!!

8. Distributed DBs

9. table-level lock or row-level lock

10. Row Level Locking in SQL Server 7.0 (Please help)..........!!!!!!!!!!!

11. Row level lock in MS SQL Server 6.5 ?

12. SQL Server 7 - enforce row-level locking?

13. Row level locking -- SQL Server 7