How SQL server tracks the deadlocks
-
Where can I find some reliable information on how SQL server tracks the deadlocks? From my testing it looks that SQL server reports the deadlocks based on PID rather then per connection.
Any idead is apprecited.
Thanks a lot, mj
-
This was removed by the editor as SPAM
-
Any connection would come across to the database as an spid and that can be back tracked to the connection if that is what you are interested in ...
Mike
Mike
-
add startup parameters -T1204 and T3605 to your sqlserver's parameters.
or execute dbcc traceon(1204, 3605, -1)
This way you get deadlockinfo in your sql server logs.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Ok, this is what I see:
The app starts and logs as PID.The I could see multiple process running with the same PID. One of this process select and updates records in the staging table. The second process takes the same records loaded in the bus and process them with specific logic. then it starts from the beginning. When I have 4-5 of this PIDs with several processes inside each and they are running against the same tables I have huge amount deadlocks. If I run the same application on Oracle I could see each of these processes with separate connection and I do not have deadlocks. My colleagues is running this on Oracle with no problems. My question is how the SQL server handles these connections differently?
One more complication - if instead of a staging table I use a flat file which the application reads, then there's no deadlocks on SQL server - why reading a tables is different and why it causes contention? I also tried to create the same staging table in separate database - in this case I have much less deadlocks - like only 10%.
Can somebody help me to try to explain and possible to troubleshoot this?
Thanks a lot.mj
-
1) oracle uses versioning, whilst sqlserver uses locking.
Only when updated/deleted, a lock will be placed in Oracle.
2) what kind of isolation level are you using. In many cases read committed should do just fine, unless you have other specific needs.
3) a thread in sqlserver that uses the same pid as another thread, is a parallel execution of a command.
4) differend PIDs may cause deadlocks if they need to pass through the same datarows, or in case of lock escalation, the same object.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
OK, so I have 2 places with deadlock problems the way I see them in the error logs.
This is what a single process does: Select 5 rows from a staging table and updates them to show processed (let's say processes P1). Puts these rows/parsed data in the bus. Then they are picked up from there to get processed - P2. I was using old data all over again, which means that the data from the staging table was already into the database. In this case the application will select the rows from accout table, it'll find out that they are there and it'll do the update on the account table. This is the logic. When I start the app I see P1 and P2 also runs under P1's PID. I tried to set lock timeout for P1, but then P2 dies as the application logic said - if P1 is down, then P2 has nothing to do - no data to process.
May be something in this logic is not right...
On top of that, for faster ptocessing there are usualy 5-6 instances of this application running together.
Now, the dealocks are on both tables - staging and account.
There's no isolation level specified.
Thanks again for the help.
Mj
-
- So let's guess you are using repeatable read (lock as long as possible so you can redo everiting like from the beginning)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
- So let's guess you are using repeatable read (lock as long as possible so you can redo everiting like from the beginning)
- provide indexes to support the rowselection from your staging table ! (this way you will avoid tablescans, and that by itself may already solve your deadlock situations)
- if the above does not do the trick, you may want to use the readpast hint
, but I'd first try the others !with readpast , the select does not try to read locked rows, so your procs should read their rowset asap to obtain the locks.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks a lot.
I'm not sure if the app is using repeatable reads but it looks so - is there a way to change that? I already spoke to dev team and tehy have promised to make whatever change we need but they could not figure out what the problem is. Please, advise if I could replace the repeatable reads and with what.
The first thing I did was to create indexes and now the query takes hundreds of milliseconds, never FTS.
The deadlock is actually on the update. I already tried READPAST on the select but it did not work - I got the same deadlocks on the update statement. I also tried READPAST on the update but got an error as it's not allowed. Also on the update I tried ROWLOCK specified with no result.
LOCK_TIMEOUT 10 did not work for me as if the first process times out, then the second process says - #1 is out and i need to quit... So, the whole application shuts down.
This is the statements which I use to update the records.
SET ROWCOUNT 5
UPDATE EXAMPLE
SET OWNER = 'ABC', LOCK_TIME = ?
WHERE (OWNER IS NULL OR LOCK_TIME < ?)
Index:
CREATE INDEX [IX_EX_OWNER] ON [dbo].[EXAMPLE]([OWNER] ON [PRIMARY]
CREATE INDEX [IX_EX_LT] ON [dbo].[EXAMPLE]([LOCK_TIME]) ON [PRIMARY]
This is the table:
CREATE TABLE [EXAMPLE]
([LOAD_SEQ] [smallint] NOT NULL ,
[OWNER] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCK_TIME] [bigint] NULL ,
[MSG] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Thanks a lot for the help.mj
-
- Is there a primary key or unique key on this table ?
- Wich is the clustering index. (=the physical order of rows for the table)
- can you give some statistics (%) regarding colums OWNER IS NULL and LOCK_TIME
- If you perform the update,try to use the actual primary or unique key.
this way you even avoid index-scans.
- Is this sequence executed within a sql-stored-proc ?
- if the sequence is executed from an application, the locking mechanism used depends on the way the app connects or defines its sql-command-objects.
Keep in mind, that the defaults for locking are always the most locking ones.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply