Lock Events Category
While the current 70-762 Exam Ref "Developing SQL Databases" emphasizes that using Extended Events is an efficient way to tackle locking issues. However, the book did not go very deep in this topic. Fortunately, there are several good resources online:
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/locks-event-category?view=sql-server-ver15
https://docs.microsoft.com/en-us/analysis-services/trace-events/lock-events-category?view=asallproducts-allversions
Use the event classes in the Locks event category to monitor locking activity in an instance of the Microsoft SQL Server Database Engine. These event classes can help you investigate locking problems caused by multiple users reading and modifying data concurrently.
Because the Database Engine often processes many locks, capturing the Locks event classes during a trace can incur significant overhead and result in large trace files or tables. The Locks Events event category has the event classes described in the following table. The following is a summary,
Example:
You are developing an application that connects to a database. The application runs the following jobs:
The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined.
If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete.
You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA. Which trace event class in the Locks event category should you use?
A. LockAcquired
B. LockCancel
C. LockDeadlock
D. LockEscalation
Correct Answer: A
The Lock:Acquired event class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.
The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.