Transactions and Locks in SQL2005 (eight)-reprint

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

--Author:happyflystone

--Date:2009-10-26

--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: row version controller RCSI SI lock prompt lock timeout setting

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

In the previous article I said the lock between the row lock and the page lock is the same with the non-lock upgrade, dynamic lock, deadlock, plus the sixth chapter of some theory plus examples, basically lock the relevant knowledge of this phase is over, this one to learn about the new features of the 2005 row version controller, by the way, locking hints and lock timeout settings, In fact, this locking hint is often mentioned in the previous, but we do not elaborate.

Ten , row versioning Overview

Row versioning is a new mechanism for SQLSERVER2005 to ensure data integrity and consistency. We mentioned that there are two types of concurrency models: pessimistic and optimistic concurrency, while row versioning is a new technology that guarantees data integrity and consistency under optimistic concurrency. Row versioning and the previously mentioned locking mechanism is not the same, it guarantees that the process of writing and read the process will not block, and to ensure that not read to uncommitted data and improve the concurrency of the database, however, we should note that optimistic concurrent write process or will get exclusive lock, some of the above mentioned locking mode, The lock time and the way the deadlock is managed are all applicable to it.

There are two isolation SQLSERVER2005 using row versioning: RCSI and SI. RCSI is a relatively nonblocking read-committed mode, so-called relative nonblocking is relative to the traditional committed read mode, where the write process does not block the read process, the read process does not set the shared lock, but instead uses row versioning to read the statement-level consistency of the data, Simply put, any reading can get the most recent data that has been submitted at the beginning of the statement. Snapshot Isolation (SI) allows any read process to read the consistency data at the transaction level, simply that any read process can read the data that was submitted at the beginning of the transaction.

SQLSERVER2005 How to write non-blocking read it? Once the RCSI or SI is enabled, the database begins to store all the modified copies of the records in tempdb (the record version, which we call the line version later), and ensures that the row versions are maintained as long as the process requires the data, so tempdb is called the version store. Obviously, after you enable row versioning, tempdb needs more space to manage the row versions, so if your database uses row versioning, be sure to manage tempdb. How row versions are stored in the version store I'll talk about that.

Well, as a whole, we should have the concept that the data that has been submitted is stored in the current database, and when the data before the data is modified is copied to tempdb, how do they relate to each other? Here we introduce another term: XSN, note oh, different from LSN oh. The XSN is called the transaction sequence number, and the new line maintains a connection between this xsn and the row version in tempdb, is it a bit of a pointer taste, haha, and we should note that the new line of the xsn points to an old row in the row version area, and this old row may contain a pointer to the old data row xsn, Sqlserve can access the correct version through this list.

Said so much, feelings this row version management benefits A lot of ah, at least enhance the concurrency ability, but before changing the current database using row version management or to think twice: first, increase the burden of tempdb, this burden is not only space. Then, maintaining the old version of the data row will inevitably reduce the ability to update operations, regardless of whether there is a read process exists, as long as there is an update to the existence of the database to pay the price. Thirdly, the increased concurrency allows each read process to pay additional overhead to access the list of the xsn that we mentioned earlier to find the appropriate row version. Finally, we say that blocking is not completely avoidable, that is, in this optimistic mode of writing or blocking. In the following we simulate the update conflict under SI.

Someone has to ask what is the difference between RCSI and Si? In fact, RCSI and Si behavior basically similar, all can be in the current data locked under the premise of reading to the current data has been submitted to the previous version, their main difference is 2:1, row version records in line version save time. How to understand this, I said in front of RCSI is the statement level and SI is the transaction level, this is the direct result of the data row version of the key. Second, RCSI is a non-blocking variant that has been submitted for reading, while Si is blocked. Here we will say both of these acts.

One , row version area

SQLSERVER2005 as soon as the snapshot is turned on, all updates and deletions generate the row versions that have been committed, and the row versions are saved on the data page of the row version area, the tempdb database, to ensure the snapshot's query needs, in other words, the row version area data exists whenever a query is needed. SQLSERVER2005 has a cleanup thread, the general seems to be a one-minute recovery, for Si isolated query row version save transaction end, for RCSI isolation query row version has been saved to the end of the current query statement.

The mention of tempdb here, a little mention of this tempdb,tempdb is also a record of the log, not many people think of not record, its log is for temporary objects on the transaction rollback, remember can only rollback, can not be restored or redo, of course, is a digression, along the belt.

There are three types of objects in tempdb: User objects, internal objects, and repository. There are 3:1 data sources for this repository, and DML is executed on the rebuilt index or at the snapshot level (we will say both snapshot levels); second, the trigger, which is different from 2000 Oh, 2005 of the Pseudo-table (deleted, inserted) is generated by the row version;

A , read-write behavior has been submitted for reading snapshot isolation

RCSI we must remember that it is a statement-level snapshot isolation level, and any query can query to the most recent data that has been committed at the beginning of the statement.

How to open this level of isolation we have already written before, yes, with the ALTER DATABASE dbname set read_comm.itted_snapshot on the line, in the Run this command to note when the user can not connect to the database, If someone is using the database, the command will block. This command has two switches: with NOWAIT and rollback to avoid blocking and terminating any database connection, you can check online from the book.

I mentioned this isolation level in the previous case of the isolation level and have the same behavior with locked committed read, let's take a look at the following example:

First modify the current library's read_committed_snapshot to On

ALTER DATABASE testcsdn SET  on GO Exec Sp_us_lockinfo Go

--test data and table Create TableTA (idint, colvarchar(Ten))InsertTaSelect 1,'a'Union  AllSelect 2,'b'Union  All Select 3,'C'Go

Query one:

begin Tran Update  Set='d'where= 1  waitfor'00:00:05'  – deliberately add delay to see if this lock affects query two exec  sp_ us_lockinfo– view the current locking situation, by the figure we know there is an exclusive lock commit on the table 

Query two:

begin Tran waitforDelay'00:00:01'-Make sure the table has an exclusive lock on itSelect *  fromTawhereId= 1 -– The row version reads to the most recently submitted data/*ID col---------------------1 A (1 rows affected)*/waitforDelay'00:00:05'—-guaranteed Query one submitted dataSelect *  fromTawhereId= 1– Query to the latest row version data/*ID col---------------------1 D (1 rows affected)*/Commit

Looking back over the process, we found that this committed snapshot is the same behavior as the committed lock, but it has more concurrency than the lock mode because the read-write process is no longer blocked. In addition, we note that there is no need to use set in each session to set the option to use RCSI, that is, we do not need to make any modifications to the application can be from the default locking mode of the committed read to switch to the snapshot mode of Read Committed, thereby reducing the concurrency conflict caused by blocking.

A , read-write behavior under snapshot isolation

Si is a new isolation introduced by SQLSERVER2005, to enable must be enabled in two places simultaneously: 1, enable ALLOW_SNAPSHOW_ISOLATION;2, use set TRANSACTION in session isolation The level command sets the isolation for each session. As we said earlier, it is an optimistic mode of isolation, similar to the read-committed snapshot isolation, but there are some differences.

Enable command:

ALTER DATABASE db_name SET allow_snapshot_isolation on;

When we use this command, it will not block like RCSI if there is an active connection, but it will still be blocked if there is an active transaction. After this command is run, the state of the data does not immediately become on, but it goes through a in_transition_to_on state, when the database is waiting for all transactions in the database to end and begins to produce version data for updates and deletes, once the transaction has ended at the start of the Alter command, The database will enter the on state. Similarly, when I modify to OFF, the library state of the data goes through an intermediate state In_transition_to_off, waiting for the active transaction to end. Once all the active transactions have ended the database becomes off state. OK, let's simulate the start-up process and close the process.

We simulate the open process:

Query one: Start a transaction, remember not to commit rollback

BEGIN TRAN UPDATE TA     SET = ' B ' WHERE = 1

Query two: Open a snapshot

ALTER DATABASE SET on;

Transactions and Locks in SQL2005 (eight)-reprint