Select Statement Is Causing Insert statement to block

Question

How Intended shared lock can impact inserts

Hi All,

i have gone through some blogs and find out that the Select statement (shared lock) can also block the insert and update statement. i understand that this is true and i am try to replicate this. but somehow i am not able to replicate this. can anyone please help me to replicate this issue

This is what i am trying to do

Connection 1

BEGIN TRAN 
Select * from myBigTable 
COMMIT

Connection 2

WHILE (1=1)
BEGIN 
    BEGIN TRAN 
        INSERT INTO MyBigTable(.....)
        SELECT ......
    COMMIT 
END

But both are running fine. can someone guide me on replicating this issue.

Thanks Atul

why do you have an infinite loop? this may cause locking happen so fast that select statement be block — FLICKER, Mar 17 '16 at 04:44
`READ COMMITTED` does not hold shared lock after statement finished. — Ivan Starostin, Mar 17 '16 at 05:11

score 0 · Answer 1 · answered Mar 17 '16 at 04:24

0

select statement will not block inserts

Same way,selects will not block updates (unless you are in serializable isolation level).In other isolation levels ,select will take shared lock on row only till duration of read,but not till end of transaction.

Remember Isolation levels only is for select statements to tell SQL on how long a lock is taken while selecting data or should I even take a lock.DDL statements will always take exclusive locks in heirarchial order on data it modifies

answered Mar 17 '16 at 04:24

TheGameiswar

27,855
8
56
94

I think select with shared lock can block update as well as insert as both require exclusive lock. here is the reference for the same http://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries – Atul Bansal Mar 18 '16 at 11:44

Select Statement Is Causing Insert statement to block

1 Answers1