Check Isolation Level of a Database | DBCC USEROPTIONS

While testing a functionality we had to run some Stored Procedures in parallel. So we executed all 4 SPs in different Sessions (separate windows). All SPs got executed successfully except one, this one ended up in a Deadlock.

I thought to check the Isolation level of database but my mind didn’t clicked instantly.
So I checked MS BOL about this and find the DBCC management command to find it, which is DBCC USEROPTIONS & as follows:

USE [AdventureWorks2012]
GO

DBCC USEROPTIONS
GO

Output:-

Set Option		Value
textsize		2147483647
language		us_english
dateformat		mdy
datefirst		7
lock_timeout		-1
quoted_identifier	SET
arithabort		SET
ansi_null_dflt_on	SET
ansi_warnings		SET
ansi_padding		SET
ansi_nulls		SET
concat_null_yields_null	SET
isolation level		read committed

Other Isolation level values it returns are:-

- read uncommitted
- read committed
- repeatable read
- serializable
- read committed snapshot
- snapshot

The sys.databases metadata View also contains a column i.e. is_read_committed_snapshot_on, which tells if READ_COMMITTED_SNAPSHOT Isolation level in ON or OFF.
Check this:

select is_read_committed_snapshot_on, *
from sys.databases

About these ads