Sql-server – SQL Read Committed Snapshot Isolation in Database with Selects and Inserts Only

concurrencyperformanceperformance-tuningsql server

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)

Best Answer

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.

Related Question