2

If I am performing SELECTS on a table, is it possible that a SELECT query on a table can block INSERTS into the same table?

A SELECT will take a shared lock on the rows, but it shouldn't effect inserts correct?

The query is a LIKE clause - will this block an insert? Does it have the potential?

SELECT * FROM USERS WHERE Description LIKE '%HELLO%'

Reference: I read this response SQL Server SELECT statements causing blocking and I am confused how this would block an insert.

Dale K
  • 25,246
  • 15
  • 42
  • 71
cool breeze
  • 4,461
  • 5
  • 38
  • 67

2 Answers2

4

A SELECT will take a shared lock on the rows, but it shouldn't effect inserts correct?

No, it's not exact. When you make a SELECT, it can acquire shared locks on pages and even on the whole table, you can test it by yourself by using paglock or tablock hints (of course you should use repeatable read or serializable to see them, as all the shared locks in read committed are released as soon as they are no needed anymore).

The same situation can be modelled this way:

if object_id('dbo.t') is not null drop table dbo.t;

select top 10000 cast(row_number() over(order by getdate()) as varchar(10)) as col
into dbo.t
from sys.columns c1 
     cross join sys.columns c2;

set transaction isolation level serializable;

begin tran
    select *
    from dbo.t 
    where col like '%0%';

    select resource_type,
           request_mode,
           count(*) as cnt
    from sys.dm_tran_locks
    where request_session_id = @@spid
    group by resource_type,
             request_mode;

enter image description here

Here you see lock escalation result, my query wanted more than 5000 locks per statement so instead of them server took only one lock, shared lock on the table.

Now if you try to insert any row in this table from another session, your INSERT will be blocked.

This is because any insert first need to acquire IX on a table and IX on a page, but IX on a table is incompatible with S on the same table, so it will be blocked.

This way your select could block your insert.

To see what exactly happens on your server you should use sys.dm_tran_locks filtered by both your session_id.

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • ok so that 5K lock is because the table has millions of rows..so a WHERE LIKE '%hello%' will perform a table scan since the column isn't indexed either...do you agree? – cool breeze Sep 17 '19 at 15:05
  • 1
    No. First of all your condition WHERE Description LIKE '%HELLO%' has '%' as the begin of string so no index can be used (at least for seek). Second, if you are operating under READ COMMITTED, lock escalation will not trigger as any lock is released as soon as the row is read. It was only an example. To find the cause in your case you should look into sys.dm_tran_locks to find out conflicting locks – sepupic Sep 17 '19 at 15:13
1

General info - this is called SQL Server Concurrency and in SQL Server you will find two models:

  • Pessimistic;
  • Optimistic.

Answering your question - yes, you can block any insert during read and this is called "Pessimistic Concurrency". However, this model comes with specific properties and you have to be careful because:

  • Data being read is locked, so that no other user can modify the data;
  • Data being modified is locked, so that no other user can read or modify the data;
  • The number of locks acquired is high because every data access operation (read/write) acquires a lock;
  • Writers block readers and other writers. Readers block writers.

The point is that you should use Pessimistic Concurrency only if the locks are held for a short period of time and only if the cost of each lock is lower than rolling back the transaction in case of a conflict, as Neeraj said.

I would recommend to read more about isolation levels applying both Pessimistic and Optimistic models here.

EDIT - I found a very detailed explanation about isolation levels on Stack, here.

cdrrr
  • 1,138
  • 4
  • 13
  • 44
  • So can you explain how the SELECT will block the insert? The insert is not effecting the rows in the SELECT because it is a brand new row. – cool breeze Sep 16 '19 at 17:31
  • The query I am using is in my question, I'm not doing repeatable reads... – cool breeze Sep 16 '19 at 17:41
  • Sorry, I confused `REPEATABLE READ` with `SERIALIZABLE`. So, `SERIALIZABLE` means that locks are acquired on the range of values being read and are held until the end of the transaction, transactions cannot read uncommitted data, and cannot modify the data being read by other transactions until the transaction completes; another transaction cannot insert or delete the rows in the range of rows being read. How is the `INSERT` being made? Through a stored procedure? – cdrrr Sep 16 '19 at 18:00