|
|
Posted on 7/20/2016 12:37:53 PM
|
|
|
Overview of locks
1. Why introduce locks
When multiple users perform concurrent operations on the database at the same time, the following data inconsistencies occur:
Missing updates
Two users, A and B, read the same data and modify it, and the result of one user's modification destroys the result of the other modification, such as the ticket booking system
Dirty reading
User A modifies the data, and then user B reads out the data, but user A cancels the modification of the data for some reason, and the data returns to its original value
Do not read repeatedly
User A reads the data, and then user B reads the data and modifies it
The main method of concurrency control is blocking, which is to prohibit users from doing certain operations for a period of time to avoid data inconsistencies
2. Classification of locks
There are two divisions into categories of locks:
1 . From the perspective of the database system: it is divided into exclusive locks (i.e., exclusive locks), shared locks and update locks
MS - SQL Server uses the following resource lock patterns.
Lock Mode Description
Share(s) is used for operations that do not change or update data (read-only operations), such as SELECT statements.
Update (U) is used in updatable resources. Prevents common forms of deadlocks when multiple sessions are read, locked, and possibly a resource update that may occur.
Exclusive (X) is used for data modification operations, such as INSERT, UPDATE, or DELETE. Ensure that multiple updates are not performed on the same resource at the same time.
Intent Locks are used to establish a hierarchy of locks. The types of intent locks are: Intent Shared (IS), Intent Exclusive (IX), and Intent Exclusive (SIX).
Schema locks are used when performing operations that depend on the table schema. The types of schema locks are: schema modification (Sch -M) and schema stability (Sch -S).
Bulk updates (BUs) are used when large volumes of data are copied to a table and a TABLOCK hint is specified.
Shared locks
A shared(s) lock allows concurrent transactions to read (SELECT) a resource. When a shared (S) lock exists on a resource, no other transaction can modify the data. Release the shared (S) lock on the resource as soon as the data has been read, unless the transaction isolation level is set to repeatable or higher, or the shared (S) lock is retained with a lock hint for the duration of the transaction's lifetime.
Update lock
Update (U) locks prevent deadlocks in their usual form. A typical update pattern consists of a transaction that reads a record, gets a shared (S) lock for a resource (page or row), and then modifies a row, which requires the lock to be converted to an exclusive (X) lock. If two transactions acquire a shared mode lock on a resource and then try to update the data at the same time, one transaction attempts to convert the lock to an exclusive (X) lock. The transition from shared mode to exclusive lock must wait for a while because the exclusive lock of one transaction is not compatible with the shared mode lock of another transaction; A lock wait occurs. The second transaction attempts to obtain an exclusive (X) lock for an update. A deadlock occurs because both transactions are converted to exclusive (X) locks, and each transaction waits for the other transaction to release the shared mode lock.
To avoid this potential deadlock problem, use an updated (U) lock. Only one transaction at a time can get an updated (U) lock for a resource. If the transaction modifies the resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared lock.
Exclusive locks
Exclusive (X) locks prevent concurrent transactions from accessing resources. Other transactions cannot read or modify the data locked by the exclusive (X) lock.
Intention lock
An intent lock indicates that SQL Server needs to acquire a shared (S) lock or an exclusive (X) lock on some of the underlying resources in the hierarchy. For example, a share-intent lock placed at the table level indicates that the transaction intends to place a share(S) lock on a page or row in the table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table that contains that page. Intent locks can improve performance because SQL Server only checks the intent lock at the table level to determine whether a transaction can safely acquire a lock on that table. Instead of checking the locks on each row or page in the table to determine if a transaction can lock the entire table.
Intent locks include Intent Sharing (IS), Intent Exclusive (IX), and Intent Exclusive Sharing (SIX).
Lock Mode Description
Intent Sharing (IS) indicates that the transaction's intent is some, not all, of the underlying resources in the read hierarchy by placing S-locks on each resource.
Intent Exclusive (IX) indicates that the transaction's intent is to modify some, but not all, of the underlying resources in the hierarchy by placing an X-lock on each resource. IX is a superset of IS.
Exclusive sharing with intent (SIX) indicates that the transaction's intent is to read all of the underlying resources in the hierarchy and modify some, but not all, of the underlying resources by placing IX locks on each resource. Allow concurrent IS locks on top-level resources. For example, a table's SIX lock places a SIX lock on the table (allowing concurrent IS locks) and an IX lock on the currently modified page (an X lock on the modified row). While each resource can only have one SIX lock for a period of time to prevent other transactions from updating the resource, other transactions can read the underlying resources in the hierarchy by acquiring table-level IS locks.
Exclusive lock: Only the program that performs the locking operation is allowed to use it, and any other operations on it will not be accepted. When you execute a data update command, SQL Server automatically uses an exclusive lock. When other locks exist on an object, you cannot add an exclusive lock to it.
Shared lock: The resource locked by the shared lock can be read by other users, but other users cannot modify it.
Update lock: When SQL Server is ready to update data, it first locks the data object so that the data cannot be modified but can be read. When SQL Server determines that it wants to update data, it will automatically replace the update lock with an exclusive lock, and cannot add an update lock to it when other locks exist on the object.
2 . From the programmer's point of view: it is divided into optimistic lock and pessimistic lock.
Optimism Lock: Relies entirely on the database to manage the lock's work.
Pessimistic locks: Programmers manage lock handling on data or objects themselves.
MS - SQLSERVER uses locks to implement pessimistic concurrency control between multiple users who perform modifications in the database at the same time
3. The particle size of the lock
The lock granularity is the size of the blocked target, the small blocking granularity is high concurrency, but the overhead is large, and the large blocking granularity is low concurrency but the overhead is small
SQL Server supports lock granularity for rows, pages, keys, key ranges, indexes, tables, or databases
Resource Description
RID row identifier. Used to lock a row in a table individually.
Key row lock in the index. Used to protect the range of keys in serializable transactions.
8 kilobytes (KB) of data pages or index pages.
Extended Disk A set of eight adjacent data pages or index pages.
Table The entire table including all data and indexes.
DB database.
4. The length of the lock-up time
The length of time a lock is held is the length of time required to protect the resource at the requested level.
The hold time of the shared lock used to protect read operations depends on the transaction isolation level. With the default transaction isolation level of READ COMMITTED, the shared lock is controlled only for the duration of the read page. In a scan, the lock is not released until the lock is acquired on the next page within the scan. If you specify a HOLDLOCK prompt or set the transaction isolation level to REPEATABLE READ or SERIALIZABLE, the lock is not released until the transaction ends.
Depending on the concurrency option set for the cursor, the cursor can acquire a scroll lock in shared mode to protect the extract. When a scroll lock is required, the scroll lock is not released until the next time the cursor is extracted or closed, whichever occurs first. However, if you specify a HOLDLOCK, the scroll lock is not released until the end of the transaction.
The exclusive lock used to protect the update will not be released until the end of the transaction.
If a connection tries to acquire a lock that conflicts with a lock controlled by another connection, the connection attempting to acquire the lock will be blocked until:
The conflicting lock is released and the connection acquires the requested lock.
The connection timeout has expired. There is no timeout interval by default, but some apps set timeout intervals to prevent indefinite waiting
Five customization of locks in SQL Server
1 Handle deadlocks and set deadlock priorities
Deadlock is the endless waiting caused by multiple users applying for different blocks, because the applicant has a part of the blocking right and waiting for the partial blocking owned by other users
You can use the SET DEADLOCK_PRIORITY to control how the session reacts in the event of a deadlock condition. If both processes lock the data, and each process cannot release its own lock until the other process releases its own lock, a deadlock situation occurs.
2 Handle timeouts and set lock timeout durations.
@@LOCK_TIMEOUT Returns the current lock timeout setting for the current session in milliseconds
The SET LOCK_TIMEOUT setting allows the application to set the maximum amount of time that the statement waits to block the resource. When the waiting time of the statement is greater than the LOCK_TIMEOUT setting, the system automatically cancels the blocking statement and returns the application an error message of 1222 that the lock request timeout period has been exceeded
example
In the following example, the lock timeout period is set to 1, 800 milliseconds.
SET LOCK_TIMEOUT1800
3) Set the transaction isolation level.
4) Use table-level lock hints for SELECT, INSERT, UPDATE, and DELETE statements.
5) Configure the locking granularity of the index
You can use sp_indexoption system stored procedures to set the lock granularity for indexing
6. View the information of the lock
1 Perform EXEC SP_LOCK report information about the lock
2 Press Ctrl + 2 in the query analyzer to see the information of the lock
7. Precautions for use
How to avoid deadlocks
1. When using transactions, try to shorten the logical processing process of transactions, and submit or roll back transactions early.
2 Set the deadlock timeout parameter to a reasonable range, such as: 3 minutes - 10 minutes; After the time, the operation will be automatically abandoned to avoid the process hanging;
3. Optimize the program, check and avoid the phenomenon of deadlock;
4. Test all scripts and SPs carefully before the exact version.
5 All SPs must have error handling (via @error)
6 Do not modify the default level of SQL SERVER transactions. Forced locking is not recommended
Solve the problem How to lock a row table database
8. Several questions about locks
1 How to lock a row of a table
SET TRANSACTIONISOLATION LEVEL READUNCOMMITTED
SELECT *FROM table ROWLOCKWHERE id = 1
2 Lock a table in the database
SELECT *FROM table WITH( HOLDLOCK )
Lock statement:
sybase:
update table set col1 = col1 where1= 0 ;
MSSQL:
select col1from table (tablockx)where 1= 0 ;
oracle:
LOCK TABLE table IN EXCLUSIVE MODE ;
After the lock is locked, no one else can operate it until the locked user unlocks it, and it is unlocked with commit or rollback
A few examples help you deepen your impression
Set table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
1) Exclusive lock
Create two new connections
Execute the following statement in the first connection
begin tran
update table1
set A= ' aa '
where B= ' b2 '
waitfor delay' 00:00:30' --wait 30 seconds
commit tran
Execute 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 update to be executed, that is, wait 30 seconds
2) Shared lock
Execute the following statement in the first connection
begin tran
select *from table1 holdlock - The holdlock is artificially added to the lock
where B= ' b2 '
waitfor delay' 00:00:30' --wait 30 seconds
commit tran
Execute 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 executed at the same time, the select query in the second connection can be executed
The update must wait for the first transaction to release the shared lock and convert it to an exclusive lock before it can be executed, that is, wait 30 seconds
3) Deadlock
Added table2(D,E)
D E
d1 e1
d2 e2
Execute 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
Execute 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 detects the deadlock and aborts the process
To add:
Table-level locking hints supported by SQL Server 2000
HOLDLOCK holds the shared lock until the entire transaction is complete and should be released as soon as the locked object is not needed, equal to the SERIALIZABLE transaction isolation level
The NOLOCK statement is executed without issuing a shared lock, allowing dirty reads, which is equal to the READ UNCOMMITTED transaction isolation level
PAGLOCK uses multiple page locks where one table lock is used
READPAST lets the sql server skip any locked lines and execute transactions, and for READ UNCOMMITTED transaction isolation levels, only skip RID locks, not page, zone, and table locks
ROWLOCK enforces the use of rowlocks
TABLOCKX enforces the use of an exclusive table-level lock, which prevents any other transaction from using the table during the transaction
UPLOCK forces the use of updates when reading a table without a shared lock
App Lock:
An application lock is a lock generated by client code, not a lock generated by SQL Server itself
Two processes for handling application locks
sp_getapplock Lock application resources
sp_releaseapplock Unlock the application resources
Note: The difference between locking a table in a database
SELECT *FROM table WITH( HOLDLOCK ) Other transactions can read the table, but cannot update and delete
SELECT *FROM table WITH(TABLOCKX) Other transactions cannot read, update, and delete the table
|
Previous:There was no endpoint listening at http://localhost:111/xxx.svc that c...Next:SQL locks NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
