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

Question

Hi,

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.

Answer 1

0

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.

answered Apr 23 '12 at 05:29 PM

Grant Fritchey ♦♦
62.7k ● 12 ● 20 ● 66

Thaank you Grant Fritchey,

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

SELECT id,
       ind,
       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 '12 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 '12 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 '12 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 '12 at 04:20 PM TimothyAWiseman

(comments are locked)

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

Follow this question

Related Questions