Alternative to Nolock-commit read Snapshot isolation [row versioning]

With (NOLOCK) and means that there is no lock, in fact, when querying a table, or there is a lock, the object will be added to the schema lock, prevent the table will be modified, the database will be added to the shared lock. If you use DROP INDEX, wait until the schema lock is released. SQL SERVER2005 provides two new, non-locking, non-blocking transaction isolation levels for snapshot isolation and read Committed snapshots: Each time a modification is made from the data, the previous version is stored on the teampdb benefit: Select does not require a lock, Will greatly reduce the lock load of the entire library nolock will read to the uncommitted transaction when the modified data, and read the snapshot read the data before the modification, so nolock easy to read to the dirty data read snapshot and nolock the same place is not shared locks, but will add object schema lock and the database shared lock, The difference is that the NOLOCK needs to be added after each SQL statement, while the read snapshot is not used and the read snapshot does not read the data for uncommitted transactions. Row versioning: Before any modification, make a copy of the pre-modified version, and all subsequent reads will read the copied version, and the modification will create a new version. In this processing mode, read and write operations do not block each other. The advantage of using this line versioning mechanism is that the concurrency of the program is high, but the disadvantage is that the user is reading a data value that is not a dirty data, but is probably being modified. "Note: With row versioning, deadlocks are minimized, but not deadlock-related stored procedures: select * FROM Sys.dm_tran_version_store set the SQL Server database transaction ISOLATION level to committed read Snapshot isolation: If you run the following statement directly: ALTER Database [Mydbname] Set Read_ Committed_snapshot on may be blocked for a long time. You can choose to run the following statement: if (charindex (' Microsoft SQL Server 2005 ', @ @version) > 0) begindeclare @sql varchar (8000) Select @sql = ' ALTER DATABASE ' + db_name () + ' SET single_user with ROLLBACK IMMEDIATE; ALTER DATABASE ' + db_name () + ' SET read_committed_snapshot on; ALTER DATABASE ' + db_name () + ' SET multi_user; ' Exec (@sql) End by querying sys.databases is_read_committed_snapshot_on field select is_read_committed_snapshot_on from sys.databases where name = DB_ Name () is_read_committed_snapshot_on if 1 represents the currently committed read Snapshot isolation level reference: 7 things developers should Know about SQL serverusing read-committed Snapshot Isolation-mini-labsql Server committed read snapshot isolation level settings SQL Server 2005 using row versioning-based isolation levels

Nolock Alternative-commit read Snapshot isolation [row versioning]