Basics of Locking

Lock Granularity

Lock granularity refers to the level at which locks occur:
  • Row
  • Table
  • Page
  • Database

Locking at a smaller granularity, such as at the row level, increases concurrency, but more locks must be held if many rows are locked. Locking at a larger granularity, such as at the table level, reduces concurrency because locking an entire table restricts access to any part of the table by other transactions. However, in table-level locking, fewer locks must be held.
By default, SQL Server Compact Edition uses row-level locking for data pages and page-level locking for index pages.
The following table shows the resources that can be locked by SQL Server Compact Edition.

Locks Description
RID Row identifier. Used to lock a single row within a table.
PAG Data page or index page.
TAB Entire table, including all data and indexes
MD Table metadata. Used to protect the table schema
DB Database

Lock Modes

Lock modes determine how concurrent transactions can access data. SQL Server Compact Edition determines which lock mode to use based on the resources that must be locked and the operations that must be performed.
The following table describes the lock modes supported by SQL Server Compact Edition.
Lock mode Description
Shared (S) Protects a resource for read access. No other transactions can modify the data while shared (S) locks exist on the resource.
Exclusive (X) Indicates a data modification, such as an insert, an update, or a deletion. Ensures that multiple updates cannot be made to the same resource at the same time.
Update (U) Prevents a common form of deadlock. Only one transaction at a time can obtain a U lock on a resource. If the transaction modifies the resource, then the U lock is converted to an X lock.
Schema Used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S).
Intent Establishes a lock hierarchy. The most common types of intent lock are IS, IU, and IX. These locks indicate that a transaction is operating on some, but not all, resources lower in the hierarchy. The lower-level resources will have an S, U, or X lock.
Important:
For the default isolation level of Read Committed, a SELECT statement in SQL Server Compact Edition does not require the use of S locks to read the data. Although this is required for Microsoft SQL Server, SQL Server Compact Edition does not need the S lock to enforce Read Committed. The only lock required for a SELECT statement is Sch-S, which protects the schema while the operation executes. As a result, the SELECT statements are highly concurrent. For more information, see Transaction Isolation Level.(sursa: http://msdn.microsoft.com/en-us/library/ms172909(SQL.90).aspx)sau http://www.sqlteam.com/article/introduction-to-locking-in-sql-server  (cu exemple)

 

sau http://msdn.microsoft.com/en-us/library/ms175519.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s