By: Tibor Nagy
Overview
Understanding how locks are handled in the database is very important. The basics of the locking mechanism in MySQL and MS SQL Server are similar, but there are differences in the implementation.
Explanation
MySQL uses read and write locks, and lock requests when it cannot establish a lock. The locks can be granted on row or table level, but row locking is available only for InnoDB engine.
In MS SQL Server the two main type of locks are also read and write locks, but resource handling is more complex.
This is the full list of lock types in SQL Server:
- shared (S): used for read operations
- update (U): used for updatable resources
- exclusive (X): used for write operations
- intent: used to establish lock hierarchy. Subtypes are intent shared (IS), intent exclusive (IX) and shared with intent exclusive (SIX). It is similar to lock request in MySQL.
- schema: this lock type is used to ensure that the schema is not modified when another session uses it
- bulk update (BU): used for bulk copying with TABLOCK hint, it prevents other sessions to access the table
- key-range: protects a range of rows
- RID: the row identifier is used to lock a row in a table
- key: a row lock within an index
- page: a 8KB data page in the database
- extent: 8 pages in contiguous group
- HoBT: a heap or B-tree lock for tables without clustered index
- table: a table including data and indexes
- file: a database file
- application: an application-specified resource
- metadata
- allocation_unit
- database
Additional Information
- Understanding SQL Server Locking article on MSSQLTips
