I'm reading a book related to transaction and concurrency. In one paragraph it is mentioned that:
- In on-premises SQL Server instance the default isolation level is Read-Committed based on locking
And the next sentence is:
- The default in SQL Database is Read – committed snapshot based on row-versioning
My question is: what is the difference between "On-premises SQL Server instance" and "SQL Database" in these two sentences?
What is the default isolation level and how can I find it? Is there any special query to find out the default isolation level?
Best Answer
It's referring to Azure SQL Database which uses RCSI by default.
In on-premises SQL Server, the default isolation level is Read Committed.
Finding the "default" isolation level is a bit of a goose chase. It will depend on the database where a query is initiated from, and if any hints exist in the query to override a database level setting.
That said, you could query
sys.databases
to find out if RCSI or SI is enabled.Note that the
READ_COMMITTED_SNAPSHOT
is not technically an isolation level. Rather, it is a database option that controls the implementation so that row versioning instead of locking is used to provide read consistency for sessions using theREAD_COMMITTED
isolation level.