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 number, such as a booking system, and modify it, one of the modifications of one user destroys another modified result.
Dirty
A user modified the data, then the data restored the original value. At this time, B users received the data again, but A users have generated the data for certain reasons due to data from the data in the database, inconsistent
Not repeatable
A user read the data, then B is inconsistent users to read the data and modify it. At this time, the user finds the value before and after the user reads the data.
The main method of concurrent control is to block the lock, the lock is consistent to prohibit users from doing certain operations for some time to avoid 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) Among usage of updated resources when you resource update. Prevent when multiple session is dead. Read, locked, and then possible
Row it (x) for data modification operations, a resource multiple update. For example INSERT, UPDATE, or DELETE. Ensure that it will not be the same at the same time
The intent lock is used to build (IX) and the hierarchy of sharing the standlink lock with intention. Type (SIX) of intent lock. To: Interested Sharing (IS), intentional
Architecture locks are executed (SCH-M) and architectural stability (SC use when the SC is dependent on the operation of the table architecture. "H-S). The type of the restructing is: architecture modification
Large Capacity Update (BU) replicates data in the table and specifies the TabLock prompt.
Shared lock
Shared (S) lock allows concurrent transaction reading (SEL it does not modify data. Once the read number isolation level is set to be repeatable or higher, or ECT) one resource. When there is a shared (s) lock, any other, immediately release the resource sharing (S) lock unless the transaction is kept in the transaction survival cycle to keep the shared (S) lock.
Update lock
Update (U) lock prevents usual death, access to resource (page or rows) sharing (S) lock, and two transactions have received resource sharing mode locks, row it (X) lock. Shared mode is not compatible with the shared mode lock of the translucent of the row lock; a lock waiting. The first transaction is converted to a row (X) lock, and each dead is locked. lock. General update mode consists of a transaction, this transaction reads the record and then modify the line, this operation requires the lock to convert it (X) lock. As then trying to update the data at the same time, a transaction attempts to convert the lock to a must wait for a while, because a transaction is locked with other two transactions trying to get the row (X) lock for updates. Since both transactions are waiting to release the shared mode lock, therefore happens
To avoid this potential death update (U) lock. If the transaction is enjoys the lock. Lock problem, please use the update (U) lock. If resource is changed, the update (U) lock converted to only one transaction can obtain the resource to obtain the resource. (X) lock. Otherwise, the lock is converted into a total
Lock
Row it (X) lock prevents locking data. Concurrency transactions access to resources. Other transactions cannot read or modify the row (X) lock
Intent
The intent lock indicates that SQL Server needs to lock at the hierarchy (X). For example, placing a shared intent lock in a table-level setting in a table-level intent lock prevents another transaction from increasing performance because SQL Server is only checked. There is no need to check each row or lock on each page to get a shared (S) lock or row it in the structure in some underlying resources to represent transactions intended to place a shared (S) lock on the page or line in the table. Get the row (X) lock on the table containing that page. The intent lock can identify whether to determine if the transaction can be securely acquired by the lock to determine whether 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) is through instead of all) underlying resources. Place the S lock on each resource indicating that the intention of the transaction is part of the hierarchy (
Optimistical row (ix) by placing instead of all resources instead of all) underlying resources. IX is the ultra X lock of IS, indicating that the intention of the transaction is part of the hierarchy (set.
The SIX lock that is interested in sharing (SIX) and modifies the SIX lock of the part table places an X-lock on the table to be updated on the configured line, but it passes the IX lock on each resource. (Not all) underlying resources. Allow a SIX lock (allowed concurrent IS lock). Although each resource is only a period of time, it can use the IS of the table-level IS to indicate that the intention of the transaction is the concurrent IS lock on the top resource in the hierarchy. For example, the IX lock is placed on the currently modified page (there is a SIX lock to prevent other events from reading the underlying resource in the hierarchy.
Excord lock: When only the program update command is allowed to lock the operation, SQL Server will automatically use exclusive use, and any other other to his operation will not be accepted. Execute the data lock. When there is other locks on the object, it cannot be alone.
Shared lock: When the shared lock is locked, SQL Server can read the object to the object to be read by other users, but its shared lock. His user can't modify it, executing SELECT
Update lock: When SQL Server can be modified, you can read it. Waiting for excite locks, when there are other locks on the object, it is first unable to update the lock when it is determined to the data to SQL Server. The object is updated to lock, so that the data will automatically lock the update to
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 a small size that is blocked the target, and the sealing particle size is high, but the overhead is large, the granularity of the blockage is low, but
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.
When the shared lock of the read operation is confirmed, the lock is only released when the transaction isolation level is read in the period of the page. If you specify the HOLDLOCK SERIALIZABLE, until the end of the transaction depends on the transaction isolation level. Use the unfault control shared lock of Read commandted. In the scan until the transaction isolation level is set to the REATABLE READ or on the next page within the scan.
Based on concurrency to the cursor, until the next extraction or closes the Holdlock, until the transaction end option, the cursor can get the cursor of the shared mode (at first happens to be subject to the first), release the roll lock. Scroll locks to protect extraction. When you need to scroll lock roll lock. However, if specified
The locking lock of the protection update will be released until the end of the transaction.
If a connection attempts to get a connection to: a lock, and the lock conflicts controlled by another connection, trying to get the lock.
Release the conflict lock and the connection acquire the requested lock.
The overtime separation of the connection has expired. By default, the indefinite period is not allowed to wait without time separation, but some applications set up time separation to prevent customization of the five SQL Server locked custom customs
1 handle deadlock and set deadlock priority
The deadlock is a plurality of users to apply for different blockade, and there is a partially blocked the applicant, which is partially blocked and waiting for other users.
You can locate the data using SET DEADLOCK and until the rest. _Priority Control When the deadlock process releases its own lock, the reactive way of each process is in the process. If two processes can release their locks, they have a dead lock.
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 set time greater than lock_timeout setup lock request timeout time "1222 allows the application to set the statement waiting to be croused, the system will automatically cancel the longest time of the blocking of the quotes of the message. When the sentence is waiting for the sentence, and give Application returns "has exceeded
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 to free process suspension; reasonable range, such as: 3 minutes -10 division; more than time, automatically abandon this operation, avoid
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
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 performed at the same time, the SELECT query must wait for 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 a shared lock until the whole thing Serializable transaction isolation level is completed, it should be released immediately when the locked object is not needed, equal to
NOLOCK statement does not send a shared lock, allow dirty reading, equal to Read Uncommitted transaction isolation
Paglock uses multiple page locks in places where a table is used
ReadPast allows the SQL Serve level only skips the RID lock, does not skip R Skips any lock line, execute transactions, pages, zones, and table locks apply to read uncommitted transaction isolation
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
Select * from Table with other transactions cannot read tables, updates, and delete
