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.
|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|
The following table describes the lock modes supported by SQL Server Compact Edition.
|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.|
|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)|