Blocking and deadlock

1, the lock occurs in the transaction. The 4 properties of a transaction are: atomicity, consistency, isolation, persistence. (ACID)
1) atomicity: For data modification, or all of the execution, or all do not, there is no part of the modification and the other part of the situation, even if the execution of a power outage, the next time the start of the log will also read the last unfinished operation (so for transactions, log priority write).
2) Isolation: For data modification, only one transaction can be processed at the same time
3) Consistency: When the transaction is complete, all data must be kept in a consistent state.
4) Persistence: After the transaction is complete, its effect on the system is permanent.

2, Isolation Level: MSSQL implements different transaction isolation levels through different processing of shared lock request and release mechanisms.
1) Isolation Level:
Whether the isolation level requests that a shared lock is released with or without a range lock
Uncommitted read No no No
Read Committed is the current statement is complete without
Repeatable Read is a transaction commit without
Serializable is when a transaction commits a

Inside the transaction:
Uncommitted read you read it and I can read and write
Committed read I can also read when you read, but I can write after you read, read operation shared lock time until the end of the read.
Repeatable READ: I can write when a transaction commits, read operations share lock time until the end of the transaction.
Note: READ UNCOMMITTED: Allow dirty reads, so a transaction may see uncommitted changes made by other transactions.
Read Committed: allows a transaction to read data that was previously read (unmodified) by another transaction without having to wait for the first transaction to complete.  

2) The default isolation level is read Committed
3) Set the isolation level:
SET TRANSACTION Isolation Level
{READ UNCOMMITTED
| READ COMMITTED
| Repeatable READ
| SNAPSHOT
| SERIALIZABLE
}
[ ; ]
BEGIN TRAN
...
COMMIT

4) Select in the Set NOLOCK, you can let MSSQL not to apply for shared lock (S), but may not commit the transaction data also displayed, if the transaction rollback, select will appear dirty data.

3, type of Lock: Read lock (Shared lock), request modification Lock (U), modify Lock (X)
1 Share (S): for read operations, such as Select
2) Update (U): Request to modify resources, do the applicant registration, when the resource is released, you can first modify the resource, it is used in the updatable resources, the data is changed to an exclusive lock. Only one transaction at a time can obtain an update lock (U Lock) for a resource. If the transaction modifies the resource, the update lock (U Lock) is converted to an exclusive lock (X lock). Below the committed read level (including read-committed), because the shared lock is released after the statement has been executed, the transaction that gets the U lock is then converted to a shared lock. Above the committed read level, the update lock function is not small, assuming that two transactions to the same data acquired a shared lock, all perform the update operation, then before the end of the transaction will not be released, so you lock will always wait, conversion not exclusive lock to modify, it will appear deadlock.

3) Exclusive (X): For data modification operations, such as Insert,update,delete
4) Intention (I): Used to establish the level of the lock, usually the parent level, it has three types, intention to share (IS), intent Exclusive (IX), intent exclusive Sharing (ISX).
5) Architecture (SCH): Includes two types, schema modification (SCH-M), and schema Stabilization (sch-s)
6) Bulk Update.
7) Key range.
Note: Intent lock, lock table or page, use it to improve performance. Reason: Suppose to go to the Peach Garden attractions, rules only in the Peach Garden No tourist is, only allow the next visitor to enter. Now came a new visitor, judging whether he should enter the sights. Methods have two: 1, send Scenic area administrator into the Peach Garden, in the scenic area of the rivers, all the troubleshooting, if no tourists, then arrange the next visitor to enter, if there is, then the next visitor waiting. 2, when a visitor enters, will the scenic spot whether visitors enter the state set to true, otherwise false, then the next visitor to enter the scenic area is, it can be very easy to determine whether you can enter. It is known from this problem that method 2 has a higher performance. Intent locks are the equivalent of this, and when querying data, set the page or table where the data resides to be re-used to avoid large-scale judgments when requesting additional locks. Therefore know Prajna: intent locks can improve performance because the database engine examines intent locks only at the table level and determines whether transactions can safely get locks on the table without having to check each row or page on the table.

8) Lock and Lock application: Read and modify mutually exclusive, even if resources on the existing shared lock, then can not add lock, can read lock, request to modify the lock. If the resource has a modification lock (X), then can not read, and can not apply for modification of the Lock (U), in other words, S lock and U lock are mutually compatible, but are not compatible with the X lock.

9) Resource categories that can be locked: (1) RID, which locks a row on the heap. (2) Key: A row on the index, or an index key. (3): Data page or index page. (4): The entire table that contains all the data and indexes. (5): Database: Entire databases

Blocking and deadlock