Starting from wait_type simulating SQL Server Lock

One, lck_m_s, waiting to acquire a shared lock

Begins an SQL TRAN that performs an update to a data. But it is not a commit, nor a rollback.

begin Tran Update [dbo]. [hr_employee] Set [Description] = ' ZZ '

In this way, the [Employee] table is locked with an exclusive lock.

In another session, perform a select operation on the table. At this point, a deadlock occurs.

Select *  from [dbo]. [hr_employee]

Use the following script to query the current lock condition.

1 SELECTwt.blocking_session_id asBlockingsessesionid2, Sp.program_name asProgramName3,COALESCE(sp.) Loginame, Sp.nt_username) asHostName4, ec1.client_net_address asclientipaddress5, Db.name asDatabaseName6, Wt.wait_type asWaitType7, Ec1.connect_time asBlockingstarttime8Wt. Wait_duration_ms/ +                   aswaitduration9, ec1.session_id asBlockedsessionidTen, H1.TEXT                                    asBlockedsqltext One, H2.TEXT                                    asBlockingsqltext A    fromSys.dm_tran_locks asTL -   INNER JOINsys.databases DB -      ondb.database_id=tl.resource_database_id the   INNER JOINSys.dm_os_waiting_tasks asWT -      onTl.lock_owner_address=wt.resource_address -   INNER JOINsys.dm_exec_connections EC1 -      onec1.session_id=tl.request_session_id +   INNER JOINsys.dm_exec_connections EC2 -      onec2.session_id=wt.blocking_session_id +    Left OUTER JOINmaster.dbo.sysprocesses SP A      onSp.spid=wt.blocking_session_id at    CrossAPPLY sys.dm_exec_sql_text (Ec1.most_recent_sql_handle) asH1 -    CrossAPPLY sys.dm_exec_sql_text (Ec2.most_recent_sql_handle) asH2

The lock's wait_type is found to be lck_m_s, which means that the latter session is waiting to get a shared lock on the table to complete the query work.

Second, Lck_m_u, waiting to get update lock.

initiates a SQL session in which the update lock (UPDLOCK) Select data is used, and then wait for a certain amount of time.

1 begin Tran2 Select *  from [dbo].[Hr_employee]  with(UPDLOCK)where [Id]=73 waitforDelay'00:01:00' 4 Update [dbo].[Hr_employee] Set [Description]='ZZ' where [Id]=75 Commit Tran

During wait time, [Id]=7 's line is locked by an update lock.

Initiates another session, using an update lock (UPDLOCK) to complete the select operation.

1 Select *  from [dbo]. [hr_employee]  with (UPDLOCK)

Found after a session was block. Wait_type is Lck_m_u, which indicates that it is waiting for an update lock on the table.

Three, lck_m_x, waiting to get exclusive lock

Change the operation of the second session in the previous section to update.

Update [dbo]. [hr_employee] Set [Description] = ' ZZy ' where [Id] = 7

The latter session is also block, but this time the wait_type is lck_m_x, indicating that it is waiting for an exclusive lock for update data.

Starting from wait_type simulating SQL Server Lock