SQL Read Committed Snapshot Isolation in Database with Selects and Inserts Only

Question

We have database with only 1 table, which conducts Inserts and Selects. There are No Updates in the table.

Table has lot of Concurrency; with many simultaneous Insert and Select queries occurring. Multiple Selects can cause Lock escalation, which lead to Page to Tables locks, etc .

Would Read Committed Snapshot Isolation reduce the locking/blocking in table with only Selects and Inserts? Current isolation level is Read Committed. My assumption is No. Inserts would not block Selects, since Selects Only Read Committed Data. Selects should not block Inserts, since Inserts are occurring on tail end of table with Identity Clustered and NewSequential Id.

[My thought: If we actually had Updates, RCSI would help since, before every begin transaction, those lines would be stored in tempdb for the Read queries.]

create table dbo.Customer
(
    Customerid int primary key identity(1,1),
    CustomerGuid uniqueidentifier default (newsequentialid()),
    PersonName varchar(255)
)
create nonclustered index ix_CustomerGuid on Customer(CustomerGuid) include (PersonName)
create nonclustered index ix_PersonName on Customer(PersonName) include (CustomerGuid)

Select and Insert Queries

select CustomerGuid from dbo.Customer where PersonName = @PersonNameSelectVar
select PersonName from dbo.Customer where CustomerGuid = @CustomerGuidSelectVar

insert into dbo.Customer(CustomerGuid, PersonName) (@CustomerGuidInsertVar, @PersonNameInsertVar)

Answer 1

Of course they can block each other. The values you're inserting for PersonName can end up anywhere in the index, unless your customers only place orders alphabetically.

Whenever you perform an insert, the value for PersonName gets inserted to the

• Leaf of the clustered index
• Key of ix_PersonName
• Leaf of ix_CustomerGuid

These inserts will lock intermediate (for keys) and leaf pages (for includes/the clustered index), and may cause page splits on full pages.

Even though your Id and GUID columns are sequential, which should direct most of the INSERT work to the end of those indexes where you're theoretically less likely to be selecting data from, PersonName can be anywhere in the indexes.

That can lead to blocking for any query that needs to touch PersonName.

Though singleton inserts should be brief, they can certainly pile up a bit, and may be hampered by any data synchronization (Mirroring, AGs), or just writing out to the transaction log (WRITELOG waits).

In an INSERT/SELECT only workload, RCSI or SI would behave like the READPAST hint, where you'd skip past reading locked rows being inserted.

Read Committed waits for write locks to commit or rollback, which can cause blocking in the same way that they could cause blocking waiting for an update or delete to finish.

Answer 2

Table has lot of Concurrency; with many simultaneous Insert and Select queries occurring. Multiple Selects can cause Lock escalation, which lead to Page to Tables locks, etc .

you can actually find out if tables are really locked.

Select * from sys.dm_tran_locks 

My assumption is No. Inserts would not block Selects, since Selects Only Read Committed Data. Selects should not block Inserts,

I think this assumption is wrong.

Tran1: select
Tran1: insert (not yet committed)
Tran2: select 
Tran2: insert

In case of Read Committed,

Tran2 select will be blocked because Tran1 is not yet committed.

In case of RCSI,

Tran2 select won't be block. It will be provided with last committed record because of row versioning.

[My thought, If we actually had Updates, RCSI would help since, before every begin transaction, those lines would be stored in tempdb for the Read queries.]

In RCSI, Row versioning is maintain even in Insert statement.

You can do the experiment yourself.

Create database RCSIDB 
Go

create table Test (col int) 
go 

insert into Test  values (1) 
go 

select max_record_size_in_bytes from sys.dm_db_index_physical_stats (db_id('RCSIDB'), object_id('RCSIDBo.Test'), null, null, 'DETAILED')

It return 11 bytes (7 bytes are metadata+4 byte for integer)

Now enable isolation level.

   Alter database RCSIDB SET READ_COMMITTED_SNAPSHOT ON 
    go 

insert new row in table,

insert into Test  values (2) 
go

run same query again,

select max_record_size_in_bytes 
from sys.dm_db_index_physical_stats (db_id('RCSIDB'), 
object_id('RCSIDBo.Test'), 
 null, null, 'DETAILED') 

It return 25 bytes (row versioning overhead of 14 bytes)

Since your table is clustered index.

If two or more transaction is trying to insert same key. Then First Trans will succeed and other trans will be blocked.

Any other transaction inserting different key will succeed.