Transactions and Locks in SQL2005 (vii)-REPRINT

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

--Author:happyflystone

--Date:2009-10-21

--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)

--Reprint Please indicate the source, more please pay attention: Http://blog.csdn.net/happyflystone

--Keyword: lock escalation, prohibit escalation row lock and page lock dynamic management lock deadlock

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

The previous article has carried on the thorough discussion to the lock resource, and has discussed to the entity type, the lock essence, the life cycle and so on, this one we further to the sensitive topic lock's escalation and the deadlock to comb. We often see someone posting a database deadlock, and what types of deadlocks are there, if viewed? How to deal with?

6 , lock escalation No upgrade

Lock granularity is the smallest data locked by a query or update, and the performance and concurrency capabilities of different databases can be eliminated. The smaller the granularity of the lock, the more the number of concurrent users, which is obvious and easy, if a situation occurs, according to the business law to lock a large number of record lines to update, in the premise of maintaining concurrent users, we locked records of the row lock or key lock is many, we know that lock is not a free lunch, is to pay the price The more locks you manage, the greater the overhead of system resources. Remember that we introduced the lock block before, the lock block is a 64/128 (128 is 64-bit operating system) bytes of memory block, in addition to each application or is holding a lock block process also to prepare a 32/64 (64 is 64-bit operating system) bytes of memory block to describe these processes, Here we determine a premise: regardless of the size of the lock granularity, each lock takes up almost the same overhead. Good, for example, we want to make 10W row data update, in order to concurrency we all use row lock to lock, according to lock block definition then we need 64B * 100000+n*32b= 6400000B +32nb (theory update we take n=1 relative to 6400000 can ignore) > 6.4M of RAM to manage these row locks, assuming that the number of concurrent processes (of course, on different resources) is x, then the current database has to be x*6.4m RAM for administrative locking, obviously this increase in RAM demand is intolerable, impossible to satisfy the growth of the infinite system, Then SQL Server has to use a way to prevent the system from using too much memory to track the lock and improve the efficiency of the lock. This task is given to the lock manager, which is responsible for balancing the use of resources (and, of course, maintaining continuous, logical integrity from the start of a particular operation to the end), when the manager takes a smart decision to lock up the upgrade, from a row or key lock or page lock to a table-level lock, Comparing 6.4M and 96B, it is obvious that getting a table-level lock is more meaningful than holding many rows or key locks.

the significance of lock escalation is obvious, resulting in reduced locking overhead and avoidance of system resource exhaustion. In the structure engine we refer to the lock manager, the memory allocated to the lock manager is limited, and the upgrade of the lock ensures that the lock occupies a reasonable limit for memory maintenance.

timing of lock escalation :

1. A query or update holds the number of locks on an object exceeding the threshold. SQL2005 default is 5,000 locks (remember SQL6.0 only 200, but we have to remember SQL6.0 only page lock OH).

2, the lock resource occupies more memory than AWE or conventional memory 40%,40% is an approximate.

When the time is met SQL Server will try to lock the upgrade, of course, the upgrade does not necessarily succeed, when the lock resource on the same object rises again to a certain extent after the failure, the upgrade will occur again, if the upgrade succeeds SQL Server will release the object on the previous obtained row, key, paging lock. An upgrade failure occurs when another process has rows on the table or when the page has an exclusive lock.

potential dangers of lock escalation :

1, the result of the lock upgrade must be a full table-level lock, that is, the row lock can not be upgraded to page lock, the most fine row-level lock escalation of the direct result must be table lock.

2, lock escalation may cause unexpected blocking (this should be very well understood)

3. Unable to downgrade after successful lock upgrade

prohibit upgrade We know that lock escalation is potentially dangerous, and the result of this upgrade is impossible to downgrade now unless the transaction ends. So upgrading is not a good thing for all applications, MS offers two switches: 1211 and 1224, and we can disable the upgrade by setting up a tracking flag.

7 , row locks, page locks

7.0 Previous version Lock the minimum granularity is the page lock, remind you that the page at that time the minimum unit is 2K, if the careful deployment to a certain extent can meet the large amount of throughput and acceptable response time. Then 7.0 after the paging from 2KB to 8KB (why do you want to promote it?) Hey, leave a question for everyone), this page lock on concurrency is a challenge, that is, the lock range is 4 times times before 7.0, when concurrency and response time are a problem. SQL2005 fully implemented row-level locking, obviously this is a welcome response to concurrency, but as I am in the lock upgrade to the account of the people, in the limited availability of locked resources, the cost of a lot of row-level locking is unacceptable, especially in the state of the limit.

We know that locking is a dense operation, a lock not only to see the loss of memory, but also to see that SQL Server management of these locks on itself is also a load. While SQL internal uses a latch or spin lock to reduce this load, it is easy to imagine managing a paging lock more easily and efficiently than managing N row-level locks (assuming there are n rows of records in the page).

Comparing row and page locks, row locks reduce concurrency conflicts but the loss of resources is also obvious, and the page lock reduction must have the number of locks and the resource loss to manage these locks but at the expense of concurrent performance degradation. Which is more appropriate, I am afraid is not a sentence can be said to finish, because for different applications, different industries, different concurrency model, different isolation of the two have each advantage.

In SQLSERVER2005, you can use sp_indexoption to control the locking units of an index. We can look at the online Help for this setting, but be aware that it is only for indexing, so paging locks cannot be controlled on heap tables.

8 , dynamic management lock

The SQL lock type, granularity is based on the number of rows, the number of pages that may be scanned, the number of rows on the page, the isolation level, what is being done, the system resources available, and so on, depending on the impact of these factors, SQL Server chooses an appropriate locking mode this process is called a dynamic locking strategy ( I found that the strategy is very popular in MS, and the database engine (and I have the memory engine in the engine structure) dynamically manages granularity and locking patterns, controlling the best cost efficiency of locking and system resources. A range of locks to use the system resources must be small, but the concurrency of the system is also reduced, if the selection of a small range of locks, the management of locks used by the system resources increased, but the concurrency performance has been dripping.

In general, we can use the system default settings (row-level locking is the system default), and let the system decide whether to lock the upgrade. This simplifies our management of library locks, and the system balances the load according to the actual situation.

9 , Deadlock

first, we have to know that deadlocks and waiting are two different things. Waiting is the resource that the current process needs to have the other process discharge it, as long as another process is released, then the process can continue to execute (of course, if the other process is already deadlocked that will go to an indefinite wait, but this situation generally does not occur because SQL Server will interfere with deadlocks. In addition we have a lock timeout setting, which you can read online. While deadlocks occur between two processes, there is a dilemma that no one can continue to work without human intervention in two locked processes. Another notable thing is that if a deadlock occurs, SQL Server will intervene, and we can perceive, for example, that a 1205th error has been received, and a robust application will manually intervene with 1205 errors and properly resubmit the batch. When a 1205 error occurs without terminating a process that obtains the appropriate resources and processes its own transactions until the resource is freed, this human intervention potentially provides an external environment for deadlocks. Of course, a process we wrote earlier can also query the corresponding lock information.

then, the deadlock cannot be completely avoided . In a concurrent multiuser system, the occurrence of locks, threads, memory, parallel queries, and deadlocks in Mars is normal, predictable, and inevitable. In our ability to handle deadlocks as much as possible on the application side or server, the impact of such an event that cannot be completely avoided can be minimized. That is, we should understand that deadlocks cannot be completely avoided, but I can reduce the number of occurrences.

third, the deadlock is a kind of doomsday, no human intervention will never be out of this state . A concurrent multiuser system the possibility of competing resources is very large, there will be "contradictions" in the competition, the two sides waiting for each other to release their needs of resources, will inevitably become an indefinite wait, this wait is what we call the deadlock. We know through the above introduction that the SQL Server Lock manager will intervene in this process, imagine if there is no SQL Server Lock manager intervention then two processes a zeroing result is an indefinite wait, for the application system is a doomsday. SQLSERVER2005 provides a wealth of lock-related metadata that makes it easy to detect lock-in information, and the result of SQL Server Lock Manager intervention is to prioritize low-and least-cost processes as victims, based on their priority and rollback costs. Kill the process and throw a 1205 error.

Four, the deadlock is broadly divided into three categories: Cycle Deadlock, conversion Deadlock , application-level deadlock, and unknown deadlock .

Cycle Deadlock: an exclusive resource held by both sides of the process that the other party wants. For example, process A has TA's table-level lock when it wants to apply for TB lock, and process B First has a TB row it lock also want to apply TA's table-level lock, which is the process a want TB lock but already let process B lock, process B wants to have the TA lock also let process B lock, then deadlock occurs, Let's simulate this:

Create Table int varchar (ten)) Create Table int varchar (ten)) Go

Query one:

 begin  tran  update  TB set  COL =    a    waitfor  DELAY   00:00:05        update  TA set  COL =    b     -- commit TRAN  

 begin  tran  update  TA set  COL =    a    waitfor  DELAY   00:00:05    exec  Sp_us_lockinfo–-  Get lock information before deadlock  update  TB =   '  

Execution order first run row query immediately switch query two execution, the lock information before the deadlock, we list some of the lock information:

We can see that the first query and query two respectively obtainedTB 　 TaOn the lock, line number is20And25The record we can see separately got the platoon it locked (I only explainedRID,is also locked on the table and on the page), because the query executes on one line, then5SAfter that it wants to updateTa, when the blockage occurred, we recorded from26Can seeSPID=53The process wants to get the tableTA Upstream update lock by SPID= The process of the block, and enter the wait-like, note this is not a deadlock oh, is waiting Oh, immediately after the query two 5S

Transactions and Locks in SQL2005 (vii)-REPRINT