Lock overview
1. Why is it to introduce a lock?
Multiple users simultaneously bring the following data inconsistent with the concurrent operation of the database:
Lost update
A, B reads the same data and modified, one of the user's modifications destroy another modified result, such as a booking system
Dirty
A user modified the data, and then b users read the data again, but a user canceled the modification of the data, the data restored the original value, and the data obtained at this time is inconsistent with the data in the database.
Not repeatable
A user read the data, then B user reads the data and modifies, at which time A user reads data, it is found that the values before and after the two times.
The main method of concurrent control is to block the lock, the lock is prohibited from doing certain operations for some time to avoid inconsistent data.
Second lock classification
There are two categories:
1. From the perspective of the database system: divided into exclusive lock (ie, the locking lock), shared lock and update lock
MS-SQL Server uses the following resource lock mode.
Lock mode description
Sharing (S) is used to do not change or do not update the operation (read-only operation), such as a SELECT statement.
Update (U) is used in updatable resources. Prevent a common form of deadlocks when multiple sessions are read, locked, and then possible resource updates.
It is used for data modification operations such as INSERT, UPDATE, or DELETE. Ensure that you will not be multi-update at the same time.
Interested lock for the hierarchy of the lock. The type of intent lock is: intentional sharing (IS), intentional zone (IX), and interested row sharing (Six).
The architecture lock is used when performing operations depending on the operation of the table architecture. The type of architecture lock is: architecture modification (SCH-M) and architectural stability (SCH-S).
Large Capacity Update (BU) replicates data in the table and specifies the TabLock prompt.
Shared lock
Shared (S) lock allows concurrent transaction reading (SELECT) a resource. Any other transactions cannot modify data when there is a shared (s) lock. Once the data has been read, the shared (S) lock on the resource is immediately released unless the transaction isolation level is set to re-read or higher level, or the shared (S) lock is kept in the transactional cycle.
Update lock
Update (U) locks prevent usual formal deadlocks. General update mode consists of a transaction, this transaction reads a record, obtains a resource (page or row) shared (S) lock, then modify the line, this operation requires lock to convert to a row it (X) lock. If the two transactions get a resource shared mode lock, then try to update the data at the same time, an transaction attempt to convert the lock to a row it (X) lock. The conversion of shared mode to the row lock must wait for a while, because a transaction is not compatible with the shared mode lock of other transactions; a lock waiting. The second transaction is trying to get the row (X) lock for updates. Since both transactions are converted into a row (X) lock, and each transaction waits for another transaction to release the shared mode lock, there is a deadlock.
To avoid this potential deadlock problem, please use the update (U) lock. Only one transaction can get a resource update (U) lock. If the transaction is modified, the update (U) lock is converted to a row it (X) lock. Otherwise, the lock is converted to a shared lock.
Lock
It is resistant to the resource from the lock. Other transactions cannot read or modify the data of the lock lock lock.
Intent
The intent lock indicates that SQL Server requires a shared (S) lock or row (X) lock on some undercurrent resources in the hierarchy. For example, placing a shared intent lock in a table-level indicates that the transaction is intended to place a shared (S) lock on the page or line in the table. In the table-level set intent lock prevents another transaction, it will then get row (X) locks on the table containing that page. The intent lock can improve performance because SQL Server only checks the intent lock in the table-level to determine if the transaction can safely obtain the lock on the table. There is no need to check each row or lock on each page to determine if the transaction can lock the entire table.
The intent lock includes intentional sharing (IS), intentional rows it (ix), and sharing it with intentions. Lock mode description
Interested Sharing (IS) By placing the S lock on each resource, it indicates that the intention of the transaction is the partial (rather than all) underlying resources in the reading hierarchy.
The intentional row (ix) is to place an X lock on each resource, indicating that the intention of the transaction is the partial (rather than all) underlying resources in the hierarchy. IX is an ultra-set of IS.
Sharing (SIX) with intention is to place IX locks on each resource, indicating that the intention of the transaction is to read all underlying resources in the hierarchy and modify the part (rather than all) underlying resources. Allow concurrent IS locks on top resources. For example, the SIX lock of the table places a SIX lock on the table (allowing concurrent IS lock), placing the IX lock on the currently modified page (placing the X-lock on the modified line). Although each resource can only have a SIX lock in a period of time to prevent other transactions from updating the resource, other transactions can read the underlying resources in the hierarchy by getting the table-level IS lock.
Exclusive lock: Programs that are allowed to lock operations are allowed to use, and any other other to him will not be accepted. SQL Server automatically uses exclusive locks when performing data update commands. When there is other locks on the object, it cannot be alone.
Shared lock: The resource shared lock-locked resource can be read by other users, but other users cannot modify it. When performing SELECT, SQL Server will share the object to the object.
Update lock: When SQL Server is ready to update the data, it first makes the data object to update the lock lock, so the data will not be modified, but can be read. When SQL Server is determined to update the data operation, he will automatically lock the update lock to exclusive locks. When there is other locks on the object, it cannot be updated.
2. From the perspective of programmers: divided into optimistic locks and pessimistic locks.
Optimistic lock: completely rely on the database to manage the lock work.
Pessimistic lock: programmers manage data or lock processing on the object.
MS-SQLServer uses locks to achieve pessimistic concurrency control between users in multiple simultaneous execution of modifications in the database
Three lock particle size
The lock size is the size of the blocked target, and the sealing particle size is high, but the overhead is large, the blockade particle size is low, but the overhead is small
SQL Server supported lock size can be divided into line, pages, keys, key range, index, table, or database acquisition lock
Resource description
RID line identifier. Used to lock a row in the table alone.
Key Lock in the key index. Used to protect the keys range in serial transactions.
Page 8 kilobytes (KB) data page or index page.
A group of eight data pages or index pages adjacent to the extension panel.
Tables include all the tables, including all data and indexes.
DB database.
The length of the four-lock time
The length of the lock maintained is the length of time required to protect the resource on the requested level.
The hold time of the shared lock for protecting the read operation depends on the transaction isolation level. When using the default transaction isolation level of read committed, the shared lock is only controlled during the period of reading the page. In the scan until the lock is released when the lock is acquired on the next page in the scan. If the HoldLock prompt is specified or set to REPEATABLE READ or Serializable, the lock is released until the transaction ends.
The cursor can get the scroll lock of the shared mode to protect the extract. When the scroll lock is required until the next extraction or closes the cursor (at first happens), the rolling lock is released. However, if Holdlock is specified, the rolling lock is released until the end of the transaction.
The locking lock of the protection update will be released until the end of the transaction.
If a connection is trying to get a lock, the lock is controlled by another connection, the connection that is trying to get the lock will always blocked: release the collision lock and the connection acquire the requested lock.
The overtime separation of the connection has expired. By default, there is no time division, but some applications set up time separation to prevent indefinite waiting.
Five SQL Server locked custom customization
1 handle deadlock and set deadlock priority
The deadlock is a different blockade of multiple users. Since the applicant has partially blocking the right, waiting for the partial blockade of other users, the endless wait
You can use SET DEADLOCK_PRIORITY to control the reactive way of the session when the deadlock occurs. If the two processes lock the data, and until the other process releases your own lock, each process can release your lock, that is, the deadlock occurs.
2 Processing timeout and setting the lock timeout duration.
@@ lock_timeout Returns the current lock timeout setting of the current session, in milliseconds
Set lock_timeout Settings Allow the application to set the statement to wait for the longest time for blocked resources. When the statement is waiting for the Lock_Timeout setting, the system will automatically cancel the blocking statement, and return the application to the 1222 error message that has exceeded the lock request timeout time.
Example
The following example sets the lock timeout period to 1,800 milliseconds.
Set Lock_timeout 1800
3) Set the transaction isolation level.
4) Use a table-level lock prompt for SELECT, INSERT, UPDATE, and DELETE statements.
5) Configure the lock particle size of the index
You can use the sp_indexoption system stored procedure to set the lock particle size for the index.
Six view lock information
1 Execute an Exec SP_LOCK report information about the lock
2 Press CTRL 2 in the Analyzer to see the lock information
Seven use precautions
How to avoid dead locks
1 When using a transaction, try to shorten the logical process of transactions, submit or roll back the transaction early;
2 Set the deadlock timeout parameter is reasonable range, such as: 3 minutes -10 division; more than time, automatically abandon this operation, avoid the process suspension;
3 Optimize procedures, check and avoid death lock phenomenon;
4. Test carefully for all scripts and sp. It is the version.
5 All spings must have errors (via @error)
6 General Do not modify the default level of the SQL Server transaction. Do not recommend forcibly lock
Solve problems How to confine the database lock
Eight questions about locks
1 How to lock a list of a table
Set Transaction Isolation Level Read Uncommitted
Select * from table Rowlock Where id = 1
2 Lock a table for the database
Select * from Table with (Holdlock)
Plocking statement:
Sybase:
Update table set col1 = col1 WHERE 1 = 0;
MSSQL:
Select col1 from table (Tablockx) where 1 = 0;
Oracle:
Lock Table Table in Exclusive Mode;
Other people are not operable, until the user unlocks, unlocks with commit or rollback
Several examples help everyone deepen the impression
Set Table1 (A, B, C)
A b c
A1 B1 C1
A2 B2 C2
A3 B3 C3
1) Row it lock
Newly built two connections
Perform the following statement in the first connection
Begin TRAN
Update Table1
Set a = 'aa'
WHERE B = 'b2'
WAITFOR DELAY '00: 00: 30' - Waiting for 30 seconds of Commit TRAN
Perform the following statement in the second connection
Begin TRAN
SELECT * from Table1
WHERE B = 'b2'
Commit TRAN
If the above two statements are executed at the same time, the SELECT query must wait for the UPDATE execution to be executed to wait for 30 seconds.
2) Sharing lock
Perform the following statement in the first connection
Begin TRAN
Select * from Table1 Holdlock-Holdlock artificial lock
WHERE B = 'b2'
Waitfor delay '00: 00: 30' - waiting for 30 seconds
Commit TRAN
Perform the following statement in the second connection
Begin TRAN
SELECT A, C from Table1
WHERE B = 'b2'
Update Table1
Set a = 'aa'
WHERE B = 'b2'
Commit TRAN
If the above two statements are simultaneously performed, the SELECT query in the second connection can be executed.
Update must wait for the first transaction release shared lock to the row to lock it before you want to wait for 30 seconds
3) dead lock
Add Table2 (D, E)
DE
D1 E1
D2 E2
Perform the following statement in the first connection
Begin TRAN
Update Table1
Set a = 'aa'
WHERE B = 'b2'
Waitfor delay '00: 00: 30'
Update Table2
SET D = 'D5'
WHERE E = 'E1'
Commit TRAN
Perform the following statement in the second connection
Begin TRAN
Update Table2
SET D = 'D5'
WHERE E = 'E1'
WAITFOR DELAY '00: 00: 10 '
Update Table1
Set a = 'aa'
WHERE B = 'b2'
Commit TRAN
At the same time, the system will detect the dead lock and the process is stopped.
add another point:
SQL Server2000 supported table-level lock prompt
Holdlock holds shared lock until the entire transaction is completed, it should be released immediately when the lock object is not needed, equal to the Serializable transaction isolation level
The NOLOCK statement does not send a shared lock when executed, allows dirty reading, equal to the READ UNCOMMITTED transaction isolation level
Paglock uses multiple page locks in places where a table is used
ReadPast lets SQL Server skip any lock line, execute transactions, apply to the read uncommitted transaction isolation level only skip the RID lock, do not skip, area and table lock
Rowlock enforces line lock
Tablockx enforces exclusive table-level lock, which prevents any other transactions from using this table during the transaction
Uplock Forced to use updates when reading a table without sharing
Application lock:
The application lock is the lock generated by the client code, not the lock generated by the SQL Server itself.
Two processes for processing application locks
SP_GetApplock locks application resources
Sp_releaseApplock unlocks the application resource
Note: The difference between a table of locking the database
Select * from Table with other transactions can read tables, but cannot update delete
