8.3 Locking

Source: Internet
Author: User
Tags bulk insert

Locking and concurrency
8.3 Locking
SQL Server has several different ways to lock data
For example
Update locks are obtained at the beginning of the update operation
Read operation acquires a shared lock, while a write operation acquires an exclusive lock
Two independent locking systems
Classification
The first affects all fully shared data and provides row-level locks, paging locks, tables, data paging, LOB paging, and table-level locks at the index paging level
The second type of SQL Server is used internally to handle index concurrency control, to manage access to internal data structures, and to obtain individual records of paging, while the second system uses latch latch, which consumes less resources and provides performance optimizations than locks
Difference
Lock guarantees logical consistency
Latch guarantees physical consistency
Spin lock
Type of lock for user data
Concept
The size of the lock granularity
Yes
Page out
An index key
A range of index keys
An extension
Table
Ownership of locks Scope
Session
Transaction
Cursor
Mode of Lock
Classification
Share Lock shared Lock
SQL Server acquires shared locks automatically when data is read
A shared lock can be held by a table, a paging, an index key, or a single row
Many processes can hold shared locks on the same data, but no process can get an exclusive lock on that data if a shared lock already exists
Typically, a shared lock is released immediately after a data has been read, but can be changed by using a query hint or a different transaction isolation level
Row it lock exclusive lock
When data is inserted, modified, or deleted, SQL Server automatically acquires an exclusive lock on the data
Only one process at a time holds an exclusive lock on a particular data resource
The process cannot acquire any type of lock if another process already has an exclusive lock on the data resource to be requested by a process
The exclusive lock will remain until the end of the transaction.
This means that the data being modified is usually not available to other processes until the current transaction commits or rolls back
Other processes can read exclusive locked data through query hints
Updates Lock Update lock
It's not actually a separate lock, they're a mix of shared and exclusive locks.
When SQL Server performs a data modification operation, but first needs to search the table to find the resource to be modified, the update lock is fetched
By using query hints, a process can explicitly request an update lock
Provides compatibility with other data readers, so that the process can modify the data later if the data has not been modified since it was last read.
The update lock is not sufficient to allow the user to modify the data, and all data modifications require that an exclusive lock exists on the resource being modified.
The role of the update lock is like a serialized valve that presses a subsequent request for an exclusive lock into the queue
Whenever a process holds an update lock on a resource, other processes cannot get an update or exclusive lock on the resource
Other update locks or exclusive locks that are requesting the same resources must wait for
A process that holds an update lock can convert it to an exclusive lock because the update lock avoids the compatibility of locks with other processes
Update lock can be treated as intent update intent to update
The update lock remains until the end of the transaction, or into an exclusive lock
SQL Server uses update locks for any data modification operations that require data to be searched before actual modifications are made
Such operations include restricted updates and deletions
Also includes tables with clustered indexes for insert operations
SQL Server must first search for data (using a clustered index) to find the correct location to insert a new record
When SQL Server is only in the search phase, it uses an update lock to protect the data, but only if it finds the correct location and starts inserting it, the update lock is upgraded to exclusive lock
Intent Lock Intent Lock
Special Locking method
Schema stabilization Lock schema stability locks
Schema stabilization locks prevent other processes from acquiring schema modification locks when a query is compiled
Schema change lock schema modification locks
Bulk update lock Bulk update locks
Bulk update locks are obtained when the BULK INSERT or bcp tool is executed to import data into a table
Bulk Import operations must use the TABLOCK query hint to obtain this special lock
The lock mode determines whether a lock on a concurrent request is compatible

8.3 Locking

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.