MySQL to SQL Server Locking Differences


By:
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
The lock granularity has different levels:
  • 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
Automatic lock escalation can happen to optimize performance. Lock escalation means that for example several rows are locked in a table and these locks are replaced by a table lock.

Additional Information





Comments For This Article

















get free sql tips
agree to terms