SQL Server concurrency Transaction (ii): Lock granularity and lock mode

Size of Lock:

Resources	Format	Description
DATABASE	Not applicable	The database ID is already provided in the resource_database_id column.
FILE	<file_id>	The file ID represented by this resource.
Object	<object_id>	The object ID represented by this resource. This object can be any object listed in sys.objects, not just a table.
PAGE	<file_id>:<page_in_file>	HoBt ID. This value corresponds to sys.partitions.hobt_id. The PAGE resource does not always have a HoBt ID, because the HoBt ID is additional information that can be provided by the caller, and some callers cannot provide that information.
KEY		Represents the hash of the key column represented by this resource in the row. HoBt ID. This value corresponds to sys.partitions.hobt_id.
EXTENT	<file_id>:<page_in_files>	Represents the file and page ID of the extents represented by this resource. The zone ID is the same as the page ID of the first page in the zone.
RID	<file_id>:<page_in_file>:<row_on_page>	Represents the page ID and row ID of the row represented by this resource. Note that if the associated object ID is 99, this resource represents one of the eight mixed page slots on the first IAM page of an IAM chain. HoBt ID. This value corresponds to sys.partitions.hobt_id.
Application	<dbprincipalid>:<upto characters>:(	Represents the ID of the database principal used to divide the scope of this application lock resource. It also contains a resource string corresponding to this application lock resource, which contains up to 32 characters. In some cases, only 2 characters can be displayed because the full string is no longer provided. This behavior occurs only during recovery of an application lock that is regained during database recovery. The hash value represents the hash of the full resource string that corresponds to this application lock resource.
HOBT	Not applicable	The HoBt ID provided as resource_associated_entity_id. This value corresponds to sys.partitions.hobt_id.
Allocation_unit	Not applicable	The allocation unit ID provided as a resource_associated_entity_id. This value corresponds to sys.allocation_units.allocation_unit_id.

lock granularity View Test :

--Transaction Lock resource type set TRANSACTION isolation level READ Committedbegin tranupdate t set info= ' KK ' from MyTest t where Id=1--object, Page,rid (heap), key (clustered index)--update t set info= ' KK ' from MyTest T with (Rowlock) where Id=1--object,page,rid (heap), key (clustered index)-- Update T set info= ' KK ' from MyTest T with (Paglock) where id=1--object,page--update t set info= ' KK ' from MyTest T with (tabl ock) where Id=1--object--alter table mytest add Col int--database,metadata (sch-s), OBJECT (sch-m), Object,rid (heap), KEY ( Clustered index) Select Resource_type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where resource_database_id=db_id () and [email protected] @SPIDrollback Tran

--Transaction Lock resource type set TRANSACTION isolation level READ Committedbegin Tranalter index ix_mytest_name on mytest rebuild--almost all basic classes included Type Select Resource_type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_ Tran_locks where resource_database_id=db_id () and [email protected] @SPIDrollback Tran

--All requested resource information Select Request_session_id,resource_type,resource_description,request_mode,request_status,request_ Type,request_lifetime,request_owner_type,resource_associated_entity_id,lock_owner_addressfrom sys.dm_tran_locks where resource_database_id=db_id () Order by request_session_id

--According to Sys.dm_tran_locks (resource_associated_entity_id), you can determine which object or index is being locked--(the object is being accessed and you need to wait for the end) select p.partition_id, P.partition_number,object_name (p.object_id) as table_name,i.name as Index_name,i.type_desc,p.rowsfrom Sys.partitions p INNER Join sys.indexes I on p.object_id=i.object_id and P.index_id=i.index_idwhere  281474979397632 In (p.object_id,p.hobt_id)-that is, where resource_associated_entity_id in (p.object_id,p.hobt_id)

Pick:

Microsoft SQL Server Database engine has multi-granularity locking,allow a transaction to lock different types of resources. to minimize the cost of locking,The database engine automatically locks resources at a level appropriate to the task. locked in a smaller granularity(such as Line)can increase the degree of concurrency,but the overhead is higher .,because if many lines are locked,you need to hold more locks.. locked in a larger granularity(such as Tables)will reduce the concurrency level,because locking an entire table restricts access to any part of the table by other transactions. but its overhead is low,because fewer locks are needed to maintain.

Lock mode:

Lock mode	Description
shared (S)	for read operations that do not change or do not update data, such as SELECT statement.
Update (U)	used in resources that can be updated. prevents common forms of deadlocks that occur when multiple sessions are read, locked, and subsequent resource updates are possible.
Exclusive (X)	used for data modification operations, such as INSERT , UPDATE or DELETE . Make sure that you do not make multiple updates to the same resource at the same time.
Intention	the hierarchy used to establish the lock. There are three types of intent locks: Intent sharing (IS), intent exclusive (IX) , and intent exclusive sharing (SIX) .
Architecture	used when performing operations that depend on the table schema. Schema locks consist of two types: schema modification (sch-m) and schema stability (sch-s).
High Volume update (BU)	bulk data replication to the table and the specified TABLOCK used when prompted.
Key Range	protects the range of rows read by a query when using the SERIALIZABLE transaction isolation level. Make sure that other transactions cannot insert rows that conform to a serializable transaction query when you run the query again.

--Current table record (only nonclustered index Ix_mytest_name) SELECT * from mytestidnameinfo1kknull2mmnullcreate nonclustered index ix_mytest_name on MyTest (name) with (Drop_existing=on)

--Transaction 1SET TRANSACTION Isolation Level serializablebegin Transelect * from MyTest Select Resource_type,resource_ Description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where Resource_ DATABASE_ID=DB_ID () and [email protected] @SPIDupdate T set info= ' KK ' from mytest t where name= ' KK ' select Resource_type,res Ource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where Resource_ DATABASE_ID=DB_ID () and [email protected] @SPID--waitfor delay ' 00:30:00 ' rollback tran

Description :

when a query is executed first, the entire table is a shared lock ( S ). The update is then performed, and in isolation level serializable , the query upgrades the object to an exclusive lock. If other transactions also have shared locks, they cannot be converted to an exclusive lock, so the database references the update lock. When a transaction has an update lock, other transactions can no longer request an update lock. This transaction is normally escalated to an exclusive lock, which avoids the possibility of deadlocks (such as having a shared lock on a few transactions, all of which are intended to be exclusive, waiting for each other to deadlock). A shared Intent exclusive lock (SIX) occurs when a table with a large shared granularity is transformed into an object with a smaller granularity. A key-range lock is generated only if there are indexes in the table and you need to query the key columns (such as name=' kk ') (ranges-u ), a lock is added to the granularity of the table hierarchy when the table share lock goes to the key lock process.

The following table: Lock granularity is increased and lock mode is lower.

Particle size	Lock type
KEY	Ranges-u
KEY	Ranges-u
RID	X
PAGE	IU
PAGE	Ix
OBJECT	SIX
DATABASE	S

to verify that there is a lock on the above grain size, first run the " Transactions 1 "and wait - minutes before the end. Then open another query window and execute the following " transaction 2" one by one.

--Transaction 2SET TRANSACTION Isolation level serializablebegin Transelect name from mytest where Name= ' KK '--key (ranges-u)-- Select ID from mytest where name= ' KK '--rid (X)--select name from mytest where name= ' mm '--page (IU)--select id,name from myTE St where name= ' mm '--page (IX)--select * from MyTest--object (SIX)--create table t (id int)--database (S) Select Resource_ Type,resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where RESOURCE_DATABASE_ID=DB_ID () and [email protected] @SPIDrollback Tran

Above , you can verify that transaction 1 is locked at each level of granularity.

Intent Lock:

Pick:

The database engine uses intent locks to protect shared locks ( S Lock ) or exclusive lock ( X Lock ) placed on the underlying resource of the lock hierarchy . Intent Locks are named intent locks because they are available at lower-level locks , so they are notified of intent to place locks at lower levels .

There are two uses of intent lock :

prevent other transactions from modifying higher-level resources in a way that makes locks at lower levels invalid .

improves the efficiency of the database engine to detect lock collisions at a high level of granularity .

For example , request a shared lock on a page or row of the table ( S Lock ) before , Request shared intent locks at the table level . Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive lock (X lock ) on the table that contains that page . Intent locks can be high performance , because the database engine only checks for intent locks at the table level to determine whether a transaction can safely get locks on the table . you do not need to check each row in the table or lock on each page to determine whether the transaction can lock the entire table .

Intention to lock not a lot of instructions, the above is the operation has also appeared, specific reference documents it

Bulk update lock View test:

The bulk update lock appears only when the bulk operation is in the test view

--Create TABLE first select C.name tablename,c.name columnname, o.object_id,c.column_id,o.type,o.type_desc into Bulktestfrom sys.objects o,sys.columns C where 1<>1select * from Bulktest

--export test data. Exec sp_configure ' show advanced options ', to enable the reconfigure;exec sp_configure ' xp_cmdshell cmdshellreconfigure;--must be placed on the same line to execute EXEC master: xp_cmdshell ' bcp ' select C.name tablename,c.name columnname, O.object_id,c.column_id,o.type,o.type_desc from sys.objects o,sys.columns C "queryout C:\Users\Administrator\Desktop\bulkTest.txt-c-T" | "-r" \ n "-SLOCALHOST-USA-PSA"  

--Test without logging a large number of logs ALTER DATABASE [mytest] set RECOVERY simple with No_wait--alter DATABASE [mytest] set RECOVERY bulk_logged W ITH no_wait

Tips:
If you specify the TABLOCK hint, the bulk operation will not lock the table, so that data can be inserted concurrently, of course, the system performance is good, the data constraints are not high.
If you do not specify TABLOCK, the system locks the entire table by default for bulk operation.

--All right, get to work!! Open a Query window, execute the script set TRANSACTION isolation level serializablebegin tranbulk INSERT Mytest.dbo.bulkTestFROM ' C:\Users\ Administrator\desktop\bulktest.txt ' with (tablock,fieldterminator = ' | ', rowterminator = ' \ n '); select Resource_type, Resource_description,request_mode,request_status,request_type,request_lifetimefrom sys.dm_tran_locks where RESOURCE_DATABASE_ID=DB_ID () and [email protected] @SPIDROLLBACK tran--truncate table Mytest.dbo.bulktest--select * From Mytest.dbo.bulkTest

This article only observes the related operations and partial effects of lock granularity and lock patterns in the database.

Reference:

Lock granularity and Hierarchy
Key Range Locking
Lock mode

SYS.DM_TRAN_LOCKS (Transact-SQL)
BULK INSERT (Transact-SQL)

SQL Server concurrency Transaction (ii): Lock granularity and lock mode