SQL: Find locked tables, and SQL statements for lock tables (recommended)

--Deadlock detection with Master SELECT * from sysprocesses where blocked<>0--find spid   exec sp_lock--find ObjID Select objec from spid T_name (85575343)--Find table name by ObjID

1.DatabaseName is the same as the name of the database you want to monitor (although this does not seem to work, my Computer settings are invalid)
2.DatabaseID with the dbid of the database you want to detect, you can use selectdb_id (N ' The name of the library you want to monitor) to get dbid
3.ObjectName is the same as the name of the object you want to monitor, such as table name, view name, etc.
4.ObjectID with the ID of the object you want to monitor, you can get the ID with select OBJECT_ID (N ' object name you want to monitor)
5.Error is the same as error, if a number error occurs frequently, the error number
6.Seccess Same as 0, failure, 1, success, if it is wrong, filter out the successful processing

The following SQL is tracked, followed by a trace log, and then analyzed with SQL Server Profiler *******************************

DECLARE @rc int
DECLARE @TraceID int
declare @FileName sysname
DECLARE @maxfilesize bigint
Set @maxfilesize = 5
SELECT @FileName = ' E:\lock2 '

--Initialization tracking
exec @rc = sp_trace_create @TraceID output, 0, @FileName, @maxfilesize, NULL
--The E:/dblog/deadlockdetect here is the file name (which can be modified by itself), and SQL automatically appends the. trc extension
if (@rc! = 0) Goto error

--Set trace events
DECLARE @on bit
Set @on = 1
--148 in the following statement refers to the Locks:deadlock graph event (see sys.trace_events) and 12 to the SPID column (see SYS.TRACE_COLUMNS)
exec sp_trace_setevent @TraceID, 148, @on
exec sp_trace_setevent @TraceID, 148, one, @on
exec sp_trace_setevent @TraceID, 148, 4, @on
exec sp_trace_setevent @TraceID, 148, @on
exec sp_trace_setevent @TraceID, 148, @on
exec sp_trace_setevent @TraceID, 148, @on
exec sp_trace_setevent @TraceID, 148, 1, @on

--Start tracking
exec sp_trace_setstatus @TraceID, 1

--record the Tracking ID for later use
Select traceid = @TraceID
Goto Finish

Error
Select [email protected]

Finish
Go

EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus 2, 2

SELECT * from fn_trace_gettable (' E:\lock2.trc ', 1)

/*
To pause the server-side trace above, run the following statement:
EXEC sp_trace_setstatus 3, 0--The first parameter represents Traceid, which is the output parameter in step 1. The second parameter indicates that the state is changed to 0, which is a pause

To stop the above server-side trace, run the following statement:
EXEC sp_trace_setstatus 3, 2--the first parameter represents Traceid, which is the output parameter in step 1. The second parameter indicates that the state is changed to 2, that is, stop

There are two ways to view the trace file (E:/DBLOG/DEADLOCKDETECT.TRC) generated above:
1.
SELECT * from fn_trace_gettable (' E:/dblog/deadlockdetect.trc ', 1)
The TextData column in the result returns the details of the deadlock in the form of XML.

2.
Open in SQL Server Profiler.
Go to Profiler, open trace file, select E:/dblog/deadlockdetect.trc, and you can see the deadlock information in graphical form.

//*****************************************************************************************************

Method Two: Analyze deadlocks with SQL Server Profiler (key recommendation, 2014-4-3 edition)

1. Open SQL Server Management Studio >> tools >> SQL Server Profiler

2. Create a new trace

3. On the Event Selection page, tick show all events, then select "Deadlock Graph" event, "Lock: Deadlock" and "Lock: Dead Chain" (Deadlock Graph,lock:deadlock;lock:deadlock Chain) as shown:

, and then the other default event options (Deadlock graph,lock:deadlock;lock:deadlock Chain events) are canceled and run.

4. Tracking for a period of time, the execution of the transaction aborted the end, select deadlock graph, we can visually see the cause of the deadlock between transactions:

The ellipse has a fork that indicates that the transaction was selected as a deadlock victim by SQL Server, and a hint appears if we move the mouse pointer over the fork ellipse. Locked object is a proc stored procedure (can be based on ID, select OBJECT_ID (N ' id)

Two rectangles are called resource nodes, and they represent database objects such as tables, rows, or indexes.

Because transactions A and B attempt to acquire an exclusive lock on each other's resources when they have their own resources, the process waits for each other to release resources, causing deadlocks.

Resolving deadlocks

Here are a few ways to help us solve the deadlock problem.

Optimizing queries

When we write query statements, we need to consider whether the query joins without the necessary tables? Do you want to return too much data (too many columns or rows)? Does the query perform a table scan? Is it possible to avoid deadlocks by adjusting the order of queries? Should a left join be used where join is used? is not the in statement considered thoughtful?

We are writing query statements based on the above criteria to consider whether the query should be optimized.

Use with caution (NoLock)

By default, the SELECT statement adds a s lock (shared lock) to the queried resource, and since S lock is incompatible with the X lock (exclusive lock), after adding with (NoLock), select does not locking the queried resource (or sch-s lock, sch-s lock can be compatible with any lock) , so that the query statement can be better and other statements concurrently execute, suitable for the table data update is not frequent cases.

Perhaps some people will question with (NoLock), may lead to dirty reading, first we have to consider whether the query table is frequently updated operation, and whether the data to be read back will be modified, so the measurement whether to use with (NoLock) or based on the actual reality.

Optimizing indexes

Are there any missing or redundant indexes? Do you have any duplicate indexes?

Handling deadlocks

We cannot observe the deadlock all the time, but we can record the deadlock of the system through the log, we can write the deadlock error of the system to the table, so it is convenient to analyze the cause of deadlock.

Cache

Perhaps we are doing many of the same queries very often, and if we put these frequent operations into the cache, the number of times the query is executed will reduce the chance of deadlocks. We can apply the cache, or the disk file, on a temporary table or table in the database, or in memory, or on disk.

SQL: Find locked tables, and SQL statements for lock tables (recommended)