mssqltips logo

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


Last Update: 5/27/2011




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools