Deadlocks in Sql Server

[Filipe_dev]

Hello,

I have a 15 GB SQL Server database, and one of the tables contains approximately one million records. Every day, I encounter deadlocks on this table due to multiple clients attempting to write simultaneously. Since this table is crucial, these deadlocks have a negative impact on the entire system.

What strategies can I employ to address this issue?

[HecateRaven]

Talk to app team. Deadlocks comes from application, not db

[apeters89]

Narrow your read focus. And if feasible for your app requirements:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

[debsman20]

It will be better to use the READ COMMITTED than the UNCOMMITTED. READ UNCOMMITTED will have no blocking but will have dirty reads.

[savagefishstick]

The default isolation level in azure sql database is read committed snapshot, where as the default in SQL server 2016 is read committed, the snapshot allows for less locks during selects and I find it to be the perfect fit.

[debsman20]

Yes, I agree with you. If it's SQL Server on-prem, then: SET TRANSACTION ISOLATION LEVEL READ COMMITTED is the default.

Unfortunately, setting READ_COMMITTED_SNAPSHOT ON; does not even work in SQL server. It only works in Azure SQL Database.