Basics of Locking
To understand locking in Microsoft SQL Server Compact 4.0, you must be familiar with the resources that can be locked and the different modes that you can use to lock resources.
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 4.0 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 4.0.
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 4.0 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 4.0.
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 4.0 does not require the use of S locks to read the data. Although this is required for Microsoft SQL Server, SQL Server Compact 4.0 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.