SQL Server lock mechanism
All activities of SQL Server generate locks. The smaller the locked unit, the more you can improve concurrent processing, but the greater the overhead of the management lock. How to find a balance point, make concurrency and performance can accept the difficult service of SQL Server. SQL Server has several trivial: 1. Shared locks are used for read-only operations (SELECT), locking shared resources. The shared lock does not block other users from reading, but other users from writing and modifying. 2, update lock update lock is a intent lock, when a thing has requested sharing of sharing and trying to request a exclusive lock when updating For example, when the two things use shared locks on a few lines, and at the same time try to get exclusive locks to perform an update operation, they have a dead lock: They are waiting for the other party to release the shared lock to achieve exclusive locks. The purpose of the update lock is to only get one thing to update the lock to prevent this situation. 3, exclusive locks can only have one exclusive lock in one resource and prevent all other locks from sharing the sharing. Writing is exclusive lock, which can effectively prevent 'dirty reading' 4, intend to use the shared lock and exclusive lock. View the integrated lock from the hierarchy to determine whether things can share the shared locks and exclusive locks, improve the performance of the system, do not need to be checked from the line. 5. Plan lock SCH-M, SCH-S. When the database structure changes, use SCH-M to compile the query. Both locks do not block anything lock, including exclusive locks.
Reading is a shared lock, writing is a lock, first read the updated operation is an update lock, updating the lock successfully and changing the data when the lock is upgraded to the row of his lock. The type of lock is: DB ----- Database, because the DBID column already contains the database ID, there is no information fil ---- file idx ---- index pg ----- page, data or Index page. page number. The page is identified by the fileID: Page combination, where the fileID is the fileID in the sysfiles table, and the page is the logic page number within the file. Key ---- key, used to protect key range tab --- tables in serial transactions, including all data and indexes. Since the Objid column already contains the object ID of the table, no information EXT - region, adjacent eight data page or index pages are provided. The first page of the extension panel is being locked. Page is identified by FileID: Page combination to identify rid --- line identifiers that are locked in the table. The row is identified by the FileID: Page: RID combination, where the RID is the line identifier in the page.
Lock Status: Grant --- can use the authorized resource wait --- can use the resource CNVRT --- Convert of other tasks, the lock is being converted
Split lock mode: 0 NULL No Access to resources 1 SCH-S (Schema Stability) compiles the query. It can prevent lock-up objects that occur until the unlock 2 SCH-M (Schema Modification) changes the database structure. It is possible to prevent other things to access the locking object 3 IS (Intent Sharees) intent. 4 SIU (Share Intent Update) When maintaining the shared lock of resources, put the update lock to the lower resource of the lock hierarchy 5 is-s (Intent Share-Shared) Composite Boch Lock 6 IX (Intent Exclusive) intention Lock 7 Six (Share Intent Exclusive) 8 S (Share) Shared Lock 9 U (Update) Update Lock. Prevent deadlock 10 IIN-NUL (INTENT INSERT-NULL) index line hierarchical lock, Compound key Range 11 IS-X (Intent Share-Exclusive) 12 IU (Intent Update) intent to update lock 13 IS-U (Intent Share Update ) Serial Update Scan 14 x (Exclusive) The locks of the lock 15 BU block are used as the following conclusions.
1. A connection that is connected when modifying the data block cannot modify this data block until it is unlocked. Parallel access is the most valued problem in any database solution, and various types of database systems have been proposed in order to solve problems in parallel access. SQL Server uses a multi-threaded mechanism that certainly can process multiple requests at a time. However, in the case of user modification of data, the parallel access problem is complicated. Obviously, the database typically only allows unique users to modify specific data at a time. When a user begins to modify a block data, SQL Server can quickly lock data and prevent other users from updating this data until the first user of the data is modified to complete its operation and submit transactions or rollback. However, what happens when a user is modifying a piece of data when it is a piece of data? 2, usually, a connection to another connection when modifying the data block cannot be queried until it is unlocked. Vice versa: You can't write and modify when reading. This solution will reduce the performance and efficiency of the system, although it is now a row lock (7.0 is a lock or even lock table), if you modify multi-line data, SQL Server will upgrade the data lock range to page level and even lock The entire data sheet is there to be tracked and maintain the respective data locks for each record, so that the speed of modification can consume small server resources, but concurrency is poor. . 3, when a connection is written, the other connection can be written, but not to read 4, multiple connections can be read at the same time.
So the lock occurs in the competition of reading and writing.
