Transactions and Locks in SQL2005 (iv)-REPRINT

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

--Author:happyflystone

--date:2009-10-04 09: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)

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

--Keyword: Isolation level lock unexpected data behavior lock Hint SnapShot

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

Up an early, also nothing dry, suddenly think of yesterday did not complete the task, continue on an article did not say finish.

3 , Repeatable Read ( Repeatable Read )

Concept review: Repeatable read level more than committed read one more convention: All shared locks persist until the end of the transaction, not after the data is read to release. After the data has been set, the other process can only query and increase cannot change, obviously this level of isolation has higher requirements for the program, because it may affect the concurrency performance of the system because of the long-time sharing lock, increase the probability of deadlock occurrence. It is clear that the unexpected behavior of non-repeatable reading is resolved.

Data test:

Query one:

SET TRANSACTION Isolation  Level Repeatable READ BEGIN TRANSELECT *  fromTaWHERETCID= 1 --can be queried repeatedly and cannot read uncommitted data--commit TRAN--don ' t commitSELECT @ @SPID/*tcid tcname-------------------------------1 READ COMMITTED LOCK (1 rows affected)------52 (1 rows affected)*/

Query two:

INSERTTaSELECT 9,'FF'/*(1 rows affected)*/SELECT *  fromTa--With (UPDLOCK)WHERETCID= 1/*tcid tcname-------------------------------1 READ COMMITTED LOCK (1 rows affected)*/UPDATETASETTcname= 'READ COMMITTED REP'WHERETCID= 1/*--Query has been in progress ... No results are locked--Modify the data set the exclusive lock must wait until the end of the spid=52 transaction*/

Query three:

exec Sp_us_lockinfo

It is obvious that query three s,is (sharing and intent sharing) lock has not disappeared, because the query one transaction does not end, in query two can be found that the insertion and read (including in query one again Select) is not affected, and read the data before the modification.

　　4 , Snapshot (SnapShot)

Concept review: This is a new feature of SQL SERVER2005, where all read operations are no longer affected by other locks, and the data that is read is logically determined and conforms to the consistency of the data row version before the transaction begins reading through the row versioning controller. The difference between this level of isolation and the read-committed version of the row manager is only how long the historical version data in the row versioning manager is.

Test data:

Query one:

ALTER DATABASEtestcsdnSETAllow_snapshot_isolation onGOSELECT *  fromTaWHERETCID= 1 --Old DataBEGIN TRANUPDATETASETTcname= 'SNAPSHOT'WHERETCID= 1--commit TRAN--don ' t commitSELECT @ @SPID/*tcid tcname-------------------------------1 READ COMMITTED REP (1 rows affected) (1 rows affected)------52 (1 rows affected)*/

Query two:

SET TRANSACTION Isolation  Level SNAPSHOT SELECT *  from WHERE = 1 /* Tcid        Tcname-------------------------------1           READ COMMITTED REP (1 rows affected)* /

Query three:

exec Sp_us_lockinfo

5 , serializable:

Concept review: This is the most robust and rigorous level of isolation in trading. Full isolation of interference from other transactions through the index key range, this isolation and select are the same as the holdlock effect of the lock hint. This level basically solves all the unexpected behavior, and it is obvious that the concurrency performance is down or the loss of system resources increases.

Test data:

Query one:

DROP TABLETBGOCREATE TABLETB (IDINT Primary Key, COLVARCHAR(Ten))GOINSERT  intoTBSELECT 1,'A'GOSET TRANSACTION Isolation  Level SERIALIZABLEBEGIN TRANSELECT *  fromTBWHEREIdbetween 1  and 5--Old Data--commit TRAN--don ' t commitSELECT @ @SPID/*ID COL---------------------1 A (1 rows affected)------52 (1 rows affected)*/

Query two:

SELECT *  fromTBWHEREId= 1/*ID COL---------------------1 A (1 rows affected)*/INSERTTBSELECT 2,'EE'/*--Query has been in progress ... No results are locked--Modify the data set the exclusive lock must wait until the end of the spid=52 transaction*/UPDATETBSETCOL= 'SERIALIZABLE'WHEREId= 1/*--Query has been in progress ... No results are locked--Modify the data set the exclusive lock must wait until the end of the spid=52 transaction*/

Query three:

exec Sp_us_lockinfo

It is obvious that a large number of index key ranges (ranges-s ...) can be found, ensuring that additional user processes cannot insert data within the index key range until the current transaction is closed, preventing this unexpected behavior from occurring. After serialization, in addition to data can be queried, can not modify, add, delete any data rows within the index key range, must wait until the lock on the index release.

Conclusion: Through some tests, we know that through the isolation level we can control the unexpected behavior of concurrency, in the process of actual operation we can use the activation transaction to control the lock granularity, the influence scope, in order to control the concurrency mechanism of data logic and data consistency. Finally we find that the lock HINTS can also change the table level lock type, lock cycle, and achieve the same level of isolation function.

Appendix: Isolation levels and unexpected data behavior

Unexpected behavior Isolation level	Lost based	Dirty Read	Not Repeat Read	Young Shadow	Concurrency model
Non-committed read	Whether	Is	Is	Is	Pessimistic
Read Committed (Locked)	Whether	Whether	Is	Is	Pessimistic
Read Committed (row version)	Whether	Whether	Is	Is	Optimistic
REPEATABLE READ	Whether	Whether	Whether	Is	Pessimistic
Snapshot	Whether	Whether	Whether	Whether	Optimistic
Serializable	Whether	Whether	Whether	Whether	Pessimistic

Well, so far we've introduced a lot of things about business, and we've introduced some of the locks in advance, and we've focused on locking in the following article.

Please keep your eye on my blog:http://blog.csdn.net/happyflystone.

Transactions and Locks in SQL2005 (iv)-REPRINT