Tag Info

Hot answers tagged

28

tracking deadlocks is the easier of the two: By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605. Write deadlock info to the SQL Server error log: DBCC TRACEON(-1, 1204, 3605) Turn it off: DBCC TRACEOFF(-1, 1204, 3605) See "Troubleshooting ...


20

The best way would be to use tables you already have. Create two tables -- table-a, table-b For a test you can even update the same column with the same information so you don't affect any real data. For instance UPDATE table_a set ID = ID where ID = 100; Open two sessions to the same database. On one, run BEGIN TRAN update table_a set ID=ID where ID = ...


18

The data you need is recorded in the default extended events trace. DECLARE @xml XML SELECT @xml = target_data FROM sys.dm_xe_session_targets JOIN sys.dm_xe_sessions ON event_session_address = address WHERE name = 'system_health' AND target_name = 'ring_buffer' SELECT CAST(XEventData.XEvent.value('(data/value)[1]', ...


17

my favorite articles to read and learn about deadlocks are: Simple Talk - Track down deadlocks and SQL Server Central - Using Profiler to resolve deadlocks. They will give you samples and advices about how to handle suck a situation. In short, to solve a current problem, I'd make the transactions involved shorter, take out the unneeded part out of them, ...


15

It's typically not the count of deadlocks that can cause a serious problem, but the reason to investigate would be to find out what workloads are deadlocking on each other. After all, in a deadlock there is a victim. So now you have a process that didn't complete the work that it intended to complete. And, in good theory applications should have some sort ...


13

If the SQLCAT team says that FK validation is done using read-committed isolation, then they must know what they're talking about. Emphasis on validation. The real question is Why would a report trigger FK validation? Validation occurs on writes, and reports are supposed to be reads. Either your reports are causing writes, in which case snapshot isolation ...


12

In SQL Server there is a separate thread that periodically (default 5 seconds, lower interval if a deadlock has just been detected) checks a list of waits for any cycles. I.e. it identifies the resource a thread is waiting for, then it finds the owner of that resource and recursively finds which resource that thread is in turn waiting for, thereby ...


12

This exact issue was just announced on Deadlocks occur when you execute a stored procedure to alter a temporary table if lock partitioning is enabled in SQL Server 2008 R2. It is linked from Cumulative update package 4 for SQL Server 2008 R2 SP2. It finally pays off to read SQL Server fix descriptions.


12

You have the hobt_id so the following query will identify the table:- SELECT o.name FROM sys.partitions p INNER JOIN sys.objects o ON p.object_id = o.object_id WHERE p.hobt_id = 72057632651542528 From that you can then run the following statement to identify the row in the table (if it still exists):- SELECT %%LOCKRES%%, * FROM [TABLE NAME] ...


11

No database can possibly work around deadlock errors in general-- in Oracle, a deadlock indicates a bug in the application, not in the database. The Oracle database will detect the deadlock condition (i.e. session A has a lock that session B is waiting on and session B has a lock that session A is waiting on) and terminate one of the blocked statements to ...


11

Sometimes a deadlock can be solved by adding indexing, as it allows the database to lock individual records rather than the whole table, so you reduce contention and the possibility of things getting jammed up. For example, in InnoDB : If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every ...


11

In the deadlock graph XML you will see something like: <deadlock-list> <deadlock victim="..."> <process-list> <process id="..." ... waitresource="X:..." ... The X is the interesting bit, possible values you are interested in are: RID for row id (row level locking) PAG for page level lock OBJECT (which may be further ...


11

It just means that the text of the statement contained the string "password" and SQL Server "helpfully" has masked it as a security feature to prevent you seeing some one else's password. I was able to reproduce this as follows CREATE TABLE T(X varchar(1000)) Connection 1 BEGIN TRAN INSERT INTO T VALUES('password1') WAITFOR DELAY '00:01:00' SELECT * ...


10

It looks to me as if you are trying to do an SELECT and an UPDATE in the same statement and onto the same table. The SELECT is holding a shared lock on the values inside the IX_system_Queue_DirectionByStatus index, and the UPDATE needs for those locks to be released before it can get it's exclusive lock which will update the primary key (which I will guess ...


10

What I am looking for is why I am getting a DELETE/DELETE deadlock. It appears the deadlock occurs because: spid 54 ecid 0 acquires an update (U) page lock on PAGE: 12:1:5147422 spid 166 ecid 3 requests an update (U) page lock on the same page, and is blocked spid 54 ecid 2 requests an update (U) page lock on the same page... Pages are being ...


10

The optimal index for those two queries is not far from the existing definition of the IK_HTT_ACTION_LOG_1 index (add ACTION_UUID as an INCLUDE to the improved index below): CREATE INDEX nc1 ON dbo.HTT_ACTION_LOG ( TRANSITION_UUID, STATUS, ACTION_SEQ ); The first query is: UPDATE dbo.HTT_ACTION_LOG SET [STATUS] = 'ABORTED', CLOSED = ...


9

You can configure alerts for both of these with SQL Agent. Create a new alert and select type "SQL Server performance condition alert" For long running queries, choose Object "MSSQL$InstanceName:Transactions" and Counter: Longest Transaction Running Time. Configure the values, and the alert notification options, and you're good to go. For deadlocks, the ...


9

First, I would avoid making a round trip to the database for every value. For example, if your application knows it needs 20 new IDs, do not make 20 round trips. Make only one stored procedure call, and increment the counter by 20. Also it might be better to split your table into multiple ones. It is possible avoid deadlocks altogether. I have no deadlocks ...


9

To answer that I have to take a little detour, so bear with me. If two sessions take a lock on the same resource SQL Server checks the lock compatibility map and if the second request is not "compatible" with the first, the second session has to wait. There are three lock types "S"hared, "U"pdate and e"X"clusive. S locks are taken to read from a resource ...


9

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table: I believe the issue with this is that a SQL function will do parsing (and ...


8

There would not be a problem if the table variable only ever held one value. With multiple rows, there is a new possibility for deadlock. Suppose two concurrent processes (A & B) run with table variables containing (1, 2) and (2, 1) for the same company. Process A reads the destination, finds no row, and inserts the value '1'. It holds an exclusive ...


8

The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index. If there are nonclustered indexes on the table, you may have to disable page locking for them as well. The following script demonstrates this: CREATE TABLE dbo.LockTest ( col1 ...


8

Is there a way to prevent the deadlock while maintaining the same queries? The deadlock graph shows that this particular deadlock was a conversion deadlock associated with a bookmark lookup (an RID lookup in this case): As the question notes, the general deadlock risk arises because the queries may obtain incompatible locks on the same resources in ...


8

The is a supplement to the answers already posted by DBAFromTheCold and Aaron Bertrand. Microsoft has still left %%lockres%% as undocumented feature. Below is the script that will help you : declare @databaseName varchar(100) = 'yourdatabaseName' --CHANGE HERE ! declare @keyValue varchar(100) = 'KEY: 9:72057632651542528 (543066506c7c)' --Output from ...


7

Use the sp_getapplock system stored procedure to take what ever locks to need on your sample code.


7

Rather than theorising as to what could potentially be responsible, qualify exactly what is by capturing the SQL Server Profiler Deadlock Graph Event. How To Track Down Deadlocks in SQL Server using Profiler Once you know what the problem is that you are trying to solve, you can then proceed to actually solve it.


7

Three things leap out: Your DELETE is on the 2nd column (RespondentID) of the current PK which means a scan, not a seek. Pointless ROWLOCK hint Your "UPSERT" pattern is not concurrency safe. The test for existence may pass for 2 overlapping (in time) concurrent threads giving an error. To fix Reverse your PK order in DEXTable to (RespondentID, ...


7

You are dealing with a deadlock, not a performance bottleneck issue. If you have a thousand new records per hour, you are far far far away from reaching MySQL limits. MySQL can handle at least 50 times your load. Deadlocks are cause by application code and are not the database server's fault. Deadlocks can not be fixed on the MySQL server side, except in ...


7

Use of the XLOCK hint on either your SELECT approach or the following UPDATE should be immune to this type of deadlock: DECLARE @Output TABLE ([NewId] INT); SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRANSACTION; UPDATE dbo.tblIDs WITH (XLOCK) SET LastID = LastID + 1 OUTPUT INSERTED.[LastId] INTO @Output WHERE IDName = @IDName; ...


7

Using OPTION (LOOP JOIN) isn`t suitable since it costs almost 15% more than MERGE JOIN The cost percentages displayed in showplan output are always optimizer model estimates, even in a post-execution (actual) plan. These costs likely do not reflect actual runtime performance on your particular hardware. The only way to be sure is to test the ...



Only top voted, non community-wiki answers of a minimum length are eligible