On what basis system converts the Shared lock to Exclusive lock in SQL server?


Can any one please explain me, On what basis system converts the S/ U/ X locks to X /IS / IX locks in SQL server.

Thanks in advance.
more ▼

asked Apr 23, 2012 at 05:14 PM in Default

satya gravatar image

361 18 19 22

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
Exclusives locks are required when modifications are going to be made. This can include INSERT, UPDATE or DELETE. Shared locks are for reads. Peple talk about the locks being "converted" but that's not accurate. What happens is a shared lock is in place while reads are being done to find the right row, for an UPDATE as an example. Then, when the right row is found, an exclusive lock will be placed, usually in addition to the shared lock, to update the data.
more ▼

answered Apr 23, 2012 at 05:29 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

Thaank you Grant Fritchey,

But I observed below query runs in two processes and caused dead lock,

       CASE WHEN st_date = CAST(CONVERT(varchar(20), GETDATE(), 101) AS datetime) 
            THEN 'a'
            ELSE 'b'
       END end_dt
FROM   [table]
WHERE  cvalue = 100

with one process having 'S' lock on table object and another one is having 'X' lock table.

why select query showing Exclusive lock.
Apr 24, 2012 at 03:35 AM satya
@Satya - what isolation level are you using on the database/connection? Are you only running this select or doing some work before doing the select? Is anything else querying/accessing the table?
Apr 24, 2012 at 07:01 AM WilliamD
I'm with @WilliamD. Something else is going on. Just a SELECT statement with no other activities, statements, processes, etc., involved will take out exclusive locks.
Apr 24, 2012 at 11:09 AM Grant Fritchey ♦♦
@Grant Fritchey Is that a typo? Just a bare select statement should not take out an exclusive lock.
Apr 24, 2012 at 04:20 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 23, 2012 at 05:14 PM

Seen: 850 times

Last Updated: Apr 24, 2012 at 04:20 PM