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

avatar image

361 18 20 26

(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

avatar image

Grant Fritchey ♦♦
137k 20 46 81

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.

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: 1005 times

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

Copyright 2018 Redgate Software. Privacy Policy