Locking (SQL Server Compact)
When multiple users or applications access the same data at the same time, locking prevents them from making simultaneous changes to the data. Locks are managed internally by the Microsoft SQL Server Compact 3.5 (SQL Server Compact 3.5) Database Engine. The locks are automatically acquired and released based on actions taken by the user.
If locking is not used and multiple users update the same data at the same time, the data within the database can become logically incorrect. If that occurs, queries executed against that data could produce unexpected results.
An application never makes a direct request for a lock. Instead, when a transaction begins and commands are performed in query language, data manipulation language (DML), or data definition language (DDL) within the transaction, SQL Server Compact 3.5 locks whatever resources are required to help protect the resources at the isolation level that you want.
SQL Server Compact 3.5 automatically determines when locks are to be granted and released for all resources. By default, row-level locking is used for data pages, and page-level locking is used for index pages. To preserve system resources, the lock manager automatically performs lock escalation when a configurable threshold on the number of row locks is exceeded. The maximum number of locks that can be allocated per session in lock manager is 262143.
You can choose to change the locking granularity for some operations. Use caution, because changing locking granularity can have adverse effects on concurrency.
SQL Server Compact 3.5 can lock the following resources:
- Rows
- 4 KB data or index pages
- Table schemas
- Tables
- Databases
Locking at a smaller granularity, such as the row level, increases concurrency but has more overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as the table level, reduces concurrency because locking a complete table restricts access to any part of the table by other transactions. However, locking at this level has a lower overhead because fewer locks are maintained.
In This Section
| Topic | Description |
|---|---|
Explains the basics of locking in SQL Server Compact 3.5. |
|
Describes display information returned by the sys.lock_information view. |
|
Describes ways of customizing locking. |