This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is the Read Uncommitted Isolation Level of the SQL Server?
What is the READ COMMITTED Isolation Level of the SQL Server?
SQL Server: READ COMMITTED Isolation Level with the READ_COMMITTED_SNAPSHOT option
What is the READ COMMITTED Isolation Level with the READ_COMMITTED_SNAPSHOT option?
When the READ_COMMITTED_SNAPSHOT database option is ON, read committed isolation uses row versioning.
READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. When data is updating, you can still read old version of data and there is no any share lock, but you cannot update that same data until the running update is not finished.
What is the REPEATABLE READ Isolation Level of the SQL Server?
What is the SERIALIZABLE Isolation level of the SQL Server?
What is the SNAPSHOT Isolation Level of the SQL Server?
Can you enable SNAPSHOT Isolation Level for a particular SQL statement?
You cannot enable SNAPSHOT Isolation level at statement level.
You have to enable at the database level and after enabling all your database transactions will execute in MVCC fashion.
What is the difference between READ_COMMITTED_SNAPSHOT option and Snapshot Isolation Level?
Using READ_COMMITTED_SNAPSHOT option, you can SELECT old version of rows while it is updating, but you cannot execute new UPDATE on the same data until it finished a running UPDATE.
Using SNAPSHOT ISOLATION LEVEL, you can perform any kind of DML operation because it is managing different data version in the TempDB so the reader and writer never blocked by each other.
What is a Shared lock?
Shared (S): Used for read operations that do not change or update data, such as a SELECT statement.
What is an Update lock?
Update(U): Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
What is an Exclusive lock?
Exclusive(X): Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
What is an Intent lock?
The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.
What is a Schema lock?
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table.
What is a Bulk Update lock?
BulkUpdate (BU): Used when bulk copying data into a table and the TABLOCK hint is specified.
What is a Key range lock?
Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads.
A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.
Do you think lock is reducing the performance of the database system?
For large computer systems, locks on frequently referenced objects can become a performance bottleneck as acquiring and releasing locks place contention on internal locking resources.
What do you know about the lock partitioning?
Lock partitioning enhances locking performance by splitting a single lock resource into multiple lock resources. This feature is only available for systems with 16 or more CPUs, and is automatically enabled and cannot be disabled. Only object locks can be partitioned.
