Transactions and Locks in SQL2005 (v)-Reprint

------------------------------------------------------------------------

--Author:happyflystone

--Date:2009-10-05 14:00:00

--Version:microsoft SQL Server 2005-9.00.2047.00 (Intel X86)

-APR 14 2006 01:12:25

--Copyright (c) 1988-2005 Microsoft Corporation

--Enterprise Edition on Windows NT 5.2 (Build 3790:service Pack 2)

------------------------------------------------------------------------

In the process of production transactions, multiple users accessing data at the same time can not be avoided, through different isolation levels of resources and data for various types of lock protection and in due course release to ensure the correct operation of the transaction, so that the transaction integrity and ensure the consistency of the data. Whether a lock or a row version controller determines the smooth business logic, the integrity of the transaction, data consistency. So we have to deploy according to the actual situation, to find a balance between concurrency performance and resource management costs, how can we find this balance, then we have to understand how SQL Server manages resources and locks, SQL Server not only manages locks, You also manage compatibility or escalation locking between lockdown modes and resolve deadlock issues. With SQL Server's powerful, granular locking mechanism, concurrency performance is maximized, but using as few system resources as possible is what we want most.

There are two kinds of locking system: One is locking the shared data, which is the lock that we discuss in the big part of time, one is the internal data structure and processing index, this is a kind of lightweight lock called latch, which consumes less resources than the first lock, and the information of this kind of lock is not seen in sys.dm_tran_locks. This lock occurs when we place physical records on the data page or compress, fold, and transfer paging data. We've been talking about the logical consistency of the data, and that logical consistency is controlled by locking, and the new latch we're talking about is a guarantee of physical consistency (the latch is used inside the system so we don't focus on it).

When data is accessed concurrently, SQL Server 2005 uses the following mechanisms to ensure transactional integrity and maintain data consistency:

|-Lock

Each transaction requests different types of locks on the resources that it depends on, such as rows, pages, or tables. Locks can prevent other transactions from modifying resources in such a way that a transaction request lock error may occur. When a transaction no longer relies on a locked resource, it releases the lock.

|-row Version control

When row versioning-based isolation levels are enabled, the database engine maintains the version of each row that is modified. Instead of using locks to protect all reads, applications can specify that transactions use row versions to view the data that exists at the beginning of a transaction or query. By using row versioning, the likelihood of a read operation blocking other transactions is greatly reduced.

Locking and row versioning prevents users from reading uncommitted data, and also prevents multiple users from trying to change the same data at the same time. Without locking or row versioning, queries performed on data may return data that has not yet been committed in the database, resulting in unexpected results. Finally, the granularity of the lock and concurrency is contradictory, but the cost of the management lock is advantageous, the greater the granularity of concurrency performance degradation, the smaller the granularity of management lock cost greater.

Six, lock

1. Lock granularity and lockable resources

SQL Server2005 has a multi-granularity lock that allows a transaction to lock different types of resources. To minimize the overhead of locking, the database engine automatically locks resources at the appropriate level for the task. Locking at a smaller granularity, such as a row, can increase concurrency, but it is expensive because if many rows are locked, you need to hold more locks. Locking in a larger granularity, such as a table, reduces concurrency because locking the entire table restricts access to any part of the table by other transactions, but with less overhead because fewer locks are required to be maintained.

SQL Server can lock tables, pagination, row-level, index keys, or scopes. Here I remind you, for a clustered index table, because the data row is the leaf level of the index, the lock is a key lock complete instead of a row lock.

The database engine typically has to obtain a lock on a multi-granularity level to fully protect resources. The locks on this set of multi-granularity levels are called lock hierarchies. For example, to fully protect the reading of an index, the instance of the database engine might have to obtain a shared lock on the row and an intent shared lock on the page and table.

The following table lists the resources that the database engine can lock:

Query one:

SELECT *  fromMASTER.. Spt_valuesWHERETYPE= 'LR'/*Name Number type low high status----------------------------------------------------         -----LOCK RESOURCES 0 LR null null 0NUL 1 LR NULL null        0DB 2 LR null null 0FIL 3 LR NULL NULL 0TAB 5 LR NULL null 0PAG 6 LR NUL    L NULL 0KEY 7 LR NULL null 0EXT 8 LR          Null NULL 0RID 9 LR null NULL 0APP 10                    LR NULL null 0MD-one LR null null 0HBT + LR NULL null 0AU-LR NULL null 0 (13 rows affected)*/

Note:

RID RID lock row identifier for rows in the heap

Key key to serialize the Keys range row lock in a transaction

PAG page data or index pages, 8K units

EXT EXTENT data or index page, continuous 8*page

HBT HOBT heap or B-tree to protect the lock of the index or heap table page heap

TAB table entire table, including data and indexes

FIL File Database files

App Application App Resources

MD METADATA Meta Data

AU allocation_unit allocation unit

DB database

Note: Spt_values This everyone is not strange, many people use it to generate a continuous ID number, then someone asked the use of the table, now found its role in it. We will also use the following.

2. Lock mode

We are in front of the premise to the shared lock, update lock, exclusive lock, this is to match the previous transaction, and then the SQL SERVER2005 how many locking mode? We use a simple query to list:

Inquire:

SELECT *  fromMASTER.. Spt_valuesWHERE [TYPE] = 'L'/*NAME number TYPE Low high STATUS------------------------------------                                ----------------------------LOCK TYPES 0 L null null 0NULL        1 l NULL NULL 0SCH-S 2 L NULL                                   NULL 0SCH-M 3 L NULL null 0S 4 l NULL NULL 0U 5 L null NUL                                  L 0X 6 l NULL null 0IS 7 l NULL NULL 0IU 8 L NULL NULL 0          IX 9 L null NULL 0SIU 10 L NULL NULL 0SIX l NULL null 0UIX NULL NULL 0BU-NULL NULL 0          Ranges-s L NULL NULL 0ranges-u 15                           L NULL NULL 0rangein-null + NULL NULL 0RANGEIN-S + NULL NULL 0rangein-u l N                            ULL null 0rangein-x L NULL NULL 0RANGEX-S        -NULL NULL 0rangex-u l NULL NULL 0rangex-x NULL NULL 0 (23 rows affected)*/

We can see a total of 22 locking modes, and I simply enumerate the above [NAME]:

X---row lock (Exclusive)

Sch---Schema Lock (Schema)

BU---A lot of updates (Bulk update)

Range---key ranges (key-range)

The other is in the above locked variant combinations, such as is---intent sharing lock

In fact, no introduction to these locking modes, you can refer to the online Help: Access and change the database data locking and row versioning in the database engine lock. In fact, these locking mode in the previous article basically have appeared, we can look at the following definition and then look back to the relevant content of the previous article. Let me briefly say:

Shared Lock (S lock)

When we query (select) data, SQL SERVER2005 attempts to request a shared lock (S lock) on the data, but only if there is no mutex on the current data that is mutually exclusive to the shared lock. When a shared lock exists on a resource, no other transaction can modify the data but read the data. Once the read operation is complete, the shared lock on the resource is freed immediately, unless the transaction isolation level is set to repeatable read or higher, or the lock hint (HOLDLOCK) is used to retain the shared lock for the duration of the transaction.

Update lock (U Lock)

is a lock between shared and exclusive locks, is a relay lock, like an intermediate gate, to the lock from the shared lock to the request to queue, effectively prevent the common deadlock. In a repeatable read or serializable transaction, a transaction reads the data [acquires a shared lock (S lock] on a resource (page or row)], and then modifies the data [this operation requires the lock to be converted to an exclusive (X) lock)]. If two transactions acquire a shared mode lock on the resource and then attempt to update the data at the same time, a transaction attempts to convert the lock to an exclusive (X) lock. The conversion of a shared mode to an exclusive lock must wait for a period of time, because the exclusive lock of one transaction is incompatible with the shared mode lock of other transactions; a lock wait occurs. The second transaction attempted to obtain an exclusive lock (X lock) to update. A deadlock occurs because two transactions are converted to exclusive (X) locks, and each transaction waits for another transaction to release the shared-mode lock. With the update lock can avoid this potential deadlock problem, after finding the data to be updated, SQL Server first set the update lock for the data, because the sharing lock and update lock is not mutually exclusive, the other transaction set the share lock can still set the update lock, and then because of the update lock scold, If other transactions to modify the data must wait. If the transaction modifies the resource, the update lock is converted to an exclusive lock (X lock).

Exclusive lock (X lock)

You can prevent concurrent transactions from accessing resources. When an exclusive lock (X lock) is used, no other transaction can modify the data, and the read operation is performed only when the NOLOCK hint or uncommitted read isolation level is used.

Data modification statements, such as INSERT, UPDATE, and DELETE, incorporate modify and read operations. Statement performs a read operation to obtain data before performing the desired modification operation. Therefore, data modification statements typically request shared and exclusive locks. For example, an UPDATE statement might modify a row in another table based on a join with one table. In this case, in addition to requesting an exclusive lock on the update row, the UPDATE statement will also request a shared lock on the rows read in the Join table.

An exclusive lock is disposed with the end of the transaction.

Intent Lock (I lock)

The database engine uses intent locks to secure shared locks (S locks) or exclusive locks (X locks) on the underlying resources of the lock hierarchy. Intent locks are named intent locks because they are available at lower-level locks, so they are notified of intent to place locks at lower levels.

• Prevents other transactions from modifying higher-level resources in such a way that they invalidate the lower-level locks.
• Improves the efficiency of the database engine to detect lock collisions at a high level of granularity.

For example, a shared intent lock is requested at the table level before a shared lock (S Lock) is requested on the table's page or row. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock (X Lock) on the table that contains that page. Intent locks can improve performance because the database engine only checks for intent locks at the table level to determine whether a transaction can safely get locks on the table. You do not need to check each row in the table or lock on each page to determine whether the transaction can lock the entire table.

Intent locks include intent sharing (IS), intent Exclusive (IX), intent exclusive Sharing (SIX), Intent Update (IU), shared intent update (combination of SIU, S and IU locks), update intent Exclusive (combination of uix,u lock and IX lock).

Here's the Six,siu,uix we can understand as a conversion lock, which is not applied directly by SQL Server, is the intermediate state when converting from one mode to another. For example, six indicates that a process that is holding a shared lock is attempting to request an intent to lock it, or that a part of a resource that holds a shared lock is locked by another process's row. Others can be understood in the same vein.

Sp_us_lockinfo View the information of the lock, in fact, my update only affects a row record, but we found that there are three locks exist, as long as the current transaction does not end, other things on the table request whether it is a page lock or table-level lock must be with the present table or page intent lock conflict, And then there is the block, and we have an example of the previous isolation level, and you'll see that it's requesting a status of wait instead of grant.

schema Lock (schema modification lock, schema stability lock sch-s Lock)

Use schema modification locks when performing data definition language (DDL) operations on a table, such as adding columns or deleting tables. The concurrent access to the table is prevented during the period when the schema modification lock is in effect. This means that all operations other than the lock are blocked until the schema modification lock (SCH-M Lock) is released.

The schema stability Lock is used when the query is compiled. The schema stability Lock does not block any transaction locks, including exclusive locks (X locks). As a result, other transactions [including transactions with an exclusive (X) lock on the table] can continue to run when the query is compiled. However, DDL operations cannot be performed on the table.

Bulk Update lock (BU Lock)

A bulk update lock is used when you bulk copy data to a table, and you specify the TABLOCK hint or use sp_tableoption to set the table lock on bulk option. A bulk update lock allows multiple threads to load data concurrently into the same table, while preventing other processes that do not bulk load the data from accessing the table.

There are two types of key locks in SQL SERVER2005: Key lock and key range lock. Which type of key lock is used depends on the isolation level. For Read Committed, repeatable read, snapshot isolation, SQL Server locks the actual index key (if the heap table has a row lock on the actual row in addition to the key lock on the actual nonclustered index), the key-range lock can be seen if the isolation is serializable. In earlier versions of our experiment we could see that SQL Server was implemented by paging or table locks, perhaps the key range lock is not the most perfect, but we should see that the Score page or table lock is locked in a much smaller range. Key-range locks provide higher concurrency performance than previous versions with lock-in guarantees that no phantom is present.

Key Range Lock

Key-range locks are placed on the index, specifying the start and end key values. This lock will block any attempts to insert, update, or delete any rows with key values in that range, because these operations will first get the locks on the index. Key-range locks include a range component and a row component specified by range-row format, which is a combination lock mode (range-index entry's lock mode). For example: Rangei-n, Rangei represents the insertion range, and N (NULL) represents an empty resource, which indicates that the range is tested before inserting a new key in the index.

In select * from MASTER. Spt_values WHERE [TYPE] = ' L ' The last 9 of the query result is a key-range lock. This locking is usually difficult to see in sys.dm_tran_locks because of its short duration. For example, Rangei_n this lock, is the key within the scope of the insertion of records, in the key range to find the position immediately promoted to X lock, the process is very short, we can not find it in the sys.dm_tran_locks, but we are able to simulate it, let us simulate the following:

GOCREATE TABLETB (IDINT Primary Key, COLVARCHAR( -))GOGOSET TRANSACTION Isolation  Level SERIALIZABLESELECT *  fromTBWHEREIdbetween 1  and 5 --Old Data--commit TRAN--don ' t commit/*---------------------------------*/

When using serializable transaction isolation levels, key-range locks are locked at the index for the recordset read by the Transact-SQL statement to prevent all attempts to add data rows that contain index key values that fall within the range, and implicitly protect the range of rows contained in the recordset. Key-range locks prevent Phantom reads. By protecting the range of keys between rows, it also prevents phantom insertions or deletions to the recordset accessed by the transaction.

For example, in the Serializable isolation level where we have an example above, when selecting the index key value at ' 1-5 ', SQL SERVER sets the key range lock for the key value falling between 1-5 and avoids the insertion of the key values contained within this range and the deletion and update of the key values within this range. The polling processor must use the index to implement the scope filtering predicate. For example, the WHERE clause in Selec. Lock compatibility Controls whether multiple transactions can acquire locks on the same resource at the same time. If a resource is locked by another transaction, a new lock request is granted only if the mode of the request lock is compatible with the mode of the existing lock. If the mode of the request lock is incompatible with the mode of the existing lock, the transaction requesting the new lock waits for the existing lock to be freed or the lock timeout interval to expire.

Transactions and Locks in SQL2005 (v)-Reprint