Causes and resolutions of deadlock in SQL Server 2006-07-18 05:12:10Category: SQL ServerIn fact, the deepest cause of all deadlocks is one: resource competitionPerformance One:A User A accesses table A (locks up table A), then accesses table B, another user B accesses table B (locked table B), and then attempts to acce
. sysprocesses a Join (Select blocked from master. sysprocesses GROUP by blocked) b on a.spid=b.blocked where a.blocked=0UNION ALLSelect ' |_ victim _> ',Spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,Status,hostname,program_name,hostprocess,nt_domain,net_address,S1=blocked,s2=1From Master. sysprocesses a where blocked) A ORDER by s1,s2Select @[email protected] @rowcount, @i=1If @count =0 and @show_spid_if_nolock =1BeginInsert #tSelect flag = ' normal process ',Spid,kpid,blocked,db
Tags: io os sp data on CTI Code BS ASRecently in a large-scale project, because data design uses a discrete database design to facilitate requirements change and user-defined process elements, because the user-defined features, data integrity by code constraints become less realistic, can only rely on the relationship between the table constraints, resulting in data operations often produce X (exclusive) lock, Severely affect data performance.The most straightforward solution to a database
SYS. sysprocesses is an important system view. It is mainly used to locate and solve SQL Server blocking and deadlock.
Msdn: contains information about processes running on an SQL server instance. These processes can be client processes or system processes.
Main
@intCounter @intCountProperties
begin
--take the first record
Select @spid =spid@bl =BL from#tmp_lock_whowhereId= @intCounter
begin
if @spid =0
Select 'causing the database deadlock is:'+ CAST(@bl as VARCHAR(Ten))+ 'process number, which executes the following SQL syntax'
Else
Select 'process number spid:'+ CAST(@spid as VARCHAR(T
Tags: Profiler deadlock deadlock Trace transactionSQL Server Profiler-Debug DeadlockTracking generic exceptions is simple, but dealing with deadlocks is difficult, with a deadlock message number of 1205 and a severity of 13. The only suggestion that the server can "rerun the
Label:Write a stored procedure a few days ago, the stored procedure used a transaction, and then I put some code comments out to debug the wrong, suddenly found a table is locked, the original is the code to create a transaction forget to comment out. This table locks the workaround. In fact, not only the situation described above will lock the table, there are many kinds of scenes will make the table life and Death lock, unlocking is actually very simple, the following with an example to explai
Tags: DDR amp ESC sel EPO system datetime Order AddressViewing with extended events for dynamic viewsSELECT
xed.value (' @timestamp ', ' datetime ') as Creation_date,
xed.query ('. ') As Extend_event
from
(
SELECT CAST ([Target_data] as XML) as Target_data
from sys.dm_xe_session_ Targets as XT
sys.dm_xe_sessions as xs on
xs.address = xt.event_session_address
WHERE Xs.name = N ' system_health ' and
xt.target_name = n ' ring_buffer '
) as Xml
1. First we need to determine which user is locked in which table.
--Query Locked table
Select request_session_id spid,object_name (resource_associated_entity_id) tablename
A table that contains the SPID and tablename columns is returned after the query.
Where the SPID is the process name and TableName is the table name.
2. Knowing which process has locked the table, you need to find the host that locked the table through the process.
--Query host name
exec sp_who2 ' xxx '
xxx
Someone asked me how to capture and record the deadlock information on the Forum. Here, I will share some of my experiences with you, and I would like to ask you to correct them.
We know that we can use the profiler tool provided by SQL Server to track deadlock information. However, this method has a big flaw, that is,
Tags: style blog http io using ar strong data SPTurning on deadlock monitoring in the database collects the deadlock conditions that occur in the database. There are 2 ways to open it:1 Turn on 1222 monitoringExecute SQL statement:DBCC Traceon (1222,-1);Then view the deadlock information in the system log.2 Start
four necessary conditions of the joint action produced, so according to the general idea, as long as can break one of them, can effectively avoid the production of deadlocks.
The following are the processing that can be made for the four prerequisites:
Allows concurrent access to resources.
Allow resource deprivation for the process.
The resources required for the process to be submitted once.
The orderly allocation of resources, that is, access to resources in the
paragraph of SQL, only the update Table2 section is run
USE testdb;
-- Transaction2
BEGIN TRAN
UPDATE table2
SET student_name = student_name + 'Transaction2'
WHERE id = 1
UPDATE table1
SET student_name = student_name + 'Transaction2'
WHERE id IN (1,2,3,4,5)
COMMIT TRANSACTION
Run again, the first SQL update table2, run the second SQL
SQL Server deadlock makes the problem that we often encounter, the deadlock of database operation is unavoidable, this article does not intend to discuss how deadlocks are produced, the focus is to resolve deadlocks. Hopefully it will help you learn about the deadlock in
Query deadlock Process Statement
Select
request_session_id spid,
object_name (resource_associated_entity_id) tablename
from
sys.dm _tran_locks
where
resource_type= ' OBJECT '
Kill the deadlock process statement
Kill spid
Let's share a section about SQL Server detection
Label:The use of SQL Server as a database application system, will not be able to sometimes create a deadlock, after the deadlock, the maintenance personnel or developers will mostly only through the sp_who to find the deadlock process, and then killed with Sp_kill. With Sp_
Tags: ber write amp order parameter IO performance HDD persistence anotherThis article focuses on common metrics for using Performance Monitor to monitor SQL Server under Windows, including the buffer Cache hit Ratio, pages/sec, Available Bytes, Disk Time, AVG. Disk Queue Length, Processor time, Processor queue Length,
and wait statistics5.1 Batch requests/sec;sql Compilations/sec;sql re-compilations/sec The number of requests, compilations, and recompilation per second that are optimized for reference.5.2 Lock waits the number of processes waiting for the lock.5.3 Log write waits number of processes waiting to be written to the log cacheThe 5.4 page IO latch waits statistics related to page I/O latches. Used to determin
Find the next information, http://www.cnblogs.com/qanholas/archive/2013/05/15/3080013.html the gods are such a standard answer, the mark should be worthy of the standard answer, write too detailed , considering a variety of situations, but spit groove ah, too much content, find useful information is very difficult, I here to filter the content, the problem is basically server configuration problems, the solution is as follows, to have a picture of
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.