Application analysis of SQL application-lock-in (locking) in SQL2008 _mssql2005

First, the basic concept of the Lock:

Lock(locking)is aRelationship TypeDatabaseSystem's Generaland necessaryPart of, which prevents the same data from being  concurrent update   ,  data integrity It can also prevent user reads are being modified data   sql Server dynamically management Lock   understand transact-SQL query How to affect lock Here, a brief introduction to the basic knowledge of the lock.

Locking helps to prevent concurrency problems from occurring. A concurrency problem occurs when one user tries to read data that another user is modifying, or modifies data that another user is reading, or tries to modify the data that another transaction is trying to modify.

The SQL Server resource is locked and the resource is locked in its lock mode (lock mode), and the following table lists the main locking modes that SQL Server handles:

Name	Describe
Share (S)	For read operations that do not change or do not update data, such as a SELECT statement.
Update (U)	Used in resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and subsequent resource updates that might occur.
Exclusive (X)	For data modification operations, such as INSERT, UPDATE, or DELETE. Make sure that the same resource does not have multiple updates at the same time.
Intention	The hierarchy used to establish the lock. The intent lock consists of three types: intent sharing (IS), intent Exclusive (IX), and intent exclusive sharing (SIX).
Architecture	Used when performing operations that depend on a table schema. There are two types of schema locks: schema modification (SCH-M) and schema Stability (sch-s).
Bulk Update (BU)	Used when bulk data replication is made to a table and TABLOCK prompts are specified.
Key Range	Protects the range of rows read by the query when using SERIALIZABLE transaction isolation levels. Make sure that other transactions cannot insert rows that conform to a serializable transaction when the query is run again.

You can lock various objects in SQL Server, either as a row or as a table or database. Resources that can be locked differ significantly in granularity (granularity). From thin (row) to thick (database). Fine-grained locks allow for greater database concurrency because users can execute queries on some unlocked rows. However, each lock generated by SQL Server requires memory, so thousands of separate row-level locks can also affect SQL Server performance. Coarse-grained locks reduce concurrency, but consume fewer resources. The following table describes the resources that SQL Server can lock:

Resources	Description
KEY	A row lock in the index used to protect the range of keys in a serializable transaction.
PAGE	A 8-KB page in a database, such as a data page or index page.
EXTENT	A continuous set of eight pages, such as a data page or index page.
HoBT	Heap or B-tree. A lock that protects a B-tree (index) or heap data page in a table that does not have a clustered index.
TABLE	The entire table that includes all data and indexes.
FILE	Database files.
RID	The row identifier used to lock a single row in the heap.
Application	An application-specific resource.
METADATA	Meta data lock.
Allocation_unit	Allocation unit.
DATABASE	The entire database.

Not all locks are compatible with each other. For example, a resource locked by an exclusive lock cannot be added to another lock . Other transactions must wait or timeout until exclusive locks are freed. Resources that are locked by an update lock can only accept shared locks for other transactions. Resources that are locked by a shared lock can also accept other shared or updated locks.

SQL Server automatically assigns and upgrades locks. An upgrade means that a fine-grained lock (row or page lock) is converted to a coarse-grained table lock. SQL Server attempts to initiate a lock escalation when a single T-SQL statement acquires more than 5,000 locks on a single table or index, or if the number of locks in the SQL Server instance exceeds the available memory threshold. locks occupy system memory, so converting many locks into a larger lock frees up memory resources. However, it reduces concurrency while freeing up memory resources.

SQL Server 2008 brings new table options to disable lock escalation or enable lock escalation in a partition (rather than a table) range.

Ii. View the activity of the lock

The following shows an instance that uses the Sys.dm_tran_locks dynamic view to monitor the activity of locks in the database.

Open a Query window and execute the following statement:

Copy Code code as follows:

Use AdventureWorks
BEGIN TRAN
SELECT ProductID, ModifiedDate
From Production.productdocument
With (Tablockx)

Open another Query window and execute:

Copy Code code as follows:

SELECT request_session_id SessionID,
Resource_type type,
resource_database_id dbid,
object_name (resource_associated_entity_id, resource_database_id) objectname,
Request_mode Rmode,
Request_status Rstatus
From sys.dm_tran_locks
WHERE Resource_type in (' DATABASE ', ' OBJECT ')

Execution results:

Copy Code code as follows:

/*
SessionID type dbid objectname rmode rstatus
4 NULL S GRANT
DATABASE 4 NULL S GRANT
DATABASE 8 NULL S GRANT
DATABASE 8 NULL S GRANT
OBJECT 8 productdocument X GRANT
*/

Parsing: In this example, we first started a new transaction and used the Tablockx lock prompt (which placed an exclusive lock on the table) and executed a query against the Production.productdocument table. Query sys.dm_tran_locks dynamic management vision You can monitor which locks are open in the current instance of SQL Server. It returns a list of active locks in the AdventureWorks database. You can see an exclusive lock on the Productdocument table in the last row in the result.

The first three columns define session locks, resource types, and database IDs. The fourth column uses the OBJECT_NAME function, noting that it uses two parameters (object ID and database ID) to specify which name to access (the second parameter is introduced by SQL Server SP2, which specifies which database to use to convert the object name). The lock request mode and state are also queried, and finally, the FROM clause references the DMV, with the WHERE clause specifying two resource types. RESOURCE_TYPE specifies a locked resource type, such as a database\object\file\page\key\rid\extent\metadata\application\allocation_unit or hobt type. The resource_associated_entity_id of the dependent resource type, determining whether the ID is an object ID, allocation unit ID, or HOBT ID.

If the resource_associated_entity_id column contains an object ID (the resource type is Object), you can use the Sys.objects catalog view to convert the name.

If the resource_associated_entity_id column contains the allocation unit ID (the resource type is Allocation_unit), you can reference sys.allocatiion_units and Contain_ ID is connected to the sys.partitions, you can determine the object ID.

If the resource_associated_entity_id column contains a HOBT ID (the resource type is Key\page\row or HOBT), you can reference the sys.partitions directly, and then look for the corresponding object ID.

For a database, Extent, application, or metadata resource type, the resource_associated_entity_id column will be 0.

Use sys.dm_tran_locks to debug unexpected concurrency problems. For example, a query session may be locked for a longer period of time than expected, or the lock's granularity or lock mode is not what we expect (it may be that you want to use table locks instead of smaller-grained row locks or page locks). Understanding the locking level of locks can help us to debug the concurrency problems of queries more effectively.

Third, the control table lock escalation behavior

Each lock created in SQL Server consumes memory resources. When the number of locks increases, memory is reduced. If the percentage of memory usage for a lock exceeds a specific threshold, SQL Server converts fine-grained locks (pages or rows) to coarse-grained locks (table locks). This process is called lock escalation. Lock escalation reduces the number of locks in the SQL Server instance and reduces the use of lock memory.

Although finer granularity consumes more memory, it also improves concurrency because multiple queries can access unlocked rows. Introducing table locks can reduce memory consumption, but it can also cause congestion because a query locks the entire table. Depending on the application using the database, this behavior may not be desirable, and you may want to gain more control as SQL Server enforces lock upgrades.

SQL Server 2008 introduces the ability to use the ALTER TABLE command to control lock escalation at the table level. You can now choose from the following 3 settings:

Table This is the default behavior used in SQL Server 2005. When set to this value, lock escalation is enabled at the table level, whether or not it is a partitioned table.

Auto If the table is partitioned, lock escalation is enabled at the partition level (heap or B-tree). If the table is not partitioned, lock escalation occurs at the table level.

Disable Deletes a lock upgrade at the table level. Note that you may still see table locks for queries that use TABLOCK hints or heap under the Serializable isolation level.

The following example shows a new setting for modifying a table:

Copy Code code as follows:

ALTER TABLE person.address
SET (lock_escalation = AUTO)
--note that this sentence will go wrong under SQL Server 2005
SELECT Lock_escalation,lock_escalation_desc
From Sys.tables
WHERE name= ' address '

/*
Lock_escalation Lock_escalation_desc
2 AUTO
*/

Down, we disable lock escalation:

Copy Code code as follows:

ALTER TABLE person.address
SET (lock_escalation = DISABLE)
SELECT Lock_escalation,lock_escalation_desc
From Sys.tables
WHERE name= ' address '

/*
Lock_escalation Lock_escalation_desc
1 DISABLE
*/

Note: After you change this configuration, you can verify this option by querying the Lock_escalation_desc column of the Sys.tables catalog view.

Note: If the table is not partitioned, it is usually a table-level upgrade. If you specify the Disable option, the table-level lock escalation will not occur. This increases concurrency, but increases memory consumption if you request access to a large number of rows or pages.
Invited months from Http://www.cnblogs.com/downmoon