February 2, 2010 at 4:37 pm
I have an app (using ATG) where there is no control on the way query get fired on the databaes...so everything comes in implied transaction...
I am facing below deadlock where one query is trying to select while other is trying to delete the same table no ther tables are involved here... and still get into a deadlock because of the locks sequence on the indexes...
Lemme write my understanding of whats going on here....
DELETE FROM dms_topic_entry WHERE subscriber_id = @P0 AND msg_id = @P1
This delete statement take X lock on primary key (clustered index)dms_topic_entry_p and waiting to get lock on dms_topic_msg_idx id
SELECT tse.msg_id, tse.subscriber_id FROM dms_topic_entry tse WHERE tse.read_state = @P0 ORDER BY tse.msg_id
This process aquire a shared lock on dms_topic_msg_idx and waiting to get a lock on dms_topic_entry_p
In case you think why table is actually taking lock on dms_topic_msg_idx, cause table has less than 10 records any time though per second atleast 100+ insert/update statements are happening
One idea I am trying to get approved from app team is to is to drop dms_topic_msg_idx index cause there are less than 10 records in this table anytime, otherwise need to implement snapshot isolation to prevent this....
I repeat no change can be made at app level to change select to have nolock or running without transaction.
<B>Lemme know if you see I am missing something here... </B>
here's the deadlock graph...
deadlock-list
deadlock victim=process34613828
process-list
process id=process34609eb8 taskpriority=0 logused=0 waitresource=KEY: 23:72057596062859264 (7a00906c1ddd) waittime=4984 ownerId=9501636911 transactionname=implicit_transaction lasttranstarted=2010-02-02T14:23:58.620 XDES=0x1bc33bb6f0 lockMode=X schedulerid=21 kpid=13072 status=suspended spid=6219 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-02-02T14:23:58.620 lastbatchcompleted=2010-02-02T14:23:58.620 clientapp=app2 hostname=esprdatg54 hostpid=0 loginname=estoreuser isolationlevel=read committed (2) xactid=9501636911 currentdb=23 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
frame procname=adhoc line=1 stmtstart=46 sqlhandle=0x0200000065d91a2d8c65ab708c5d5d330877715f2a9b9a77
DELETE FROM dms_topic_entry WHERE subscriber_id = @P0 AND msg_id = @P1
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@P0 bigint,@P1 bigint)DELETE FROM dms_topic_entry WHERE subscriber_id = @P0 AND msg_id = @P1
process id=process34613828 taskpriority=0 logused=0 waitresource=KEY: 23:72057596062793728 (7a00ee3d5080) waittime=4984 ownerId=9501633269 transactionname=implicit_transaction lasttranstarted=2010-02-02T14:23:56.543 XDES=0x1b85ca4d80 lockMode=S schedulerid=22 kpid=15196 status=suspended spid=6266 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2010-02-02T14:23:56.543 lastbatchcompleted=2010-02-02T14:23:56.543 clientapp=app3 hostname=esprdatg54 hostpid=0 loginname=estoreuser isolationlevel=read committed (2) xactid=9501633269 currentdb=23 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
frame procname=adhoc line=1 stmtstart=24 sqlhandle=0x020000003341a837e9d8a28769347b110383657319a30fde
SELECT tse.msg_id, tse.subscriber_id FROM dms_topic_entry tse WHERE tse.read_state = @P0 ORDER BY tse.msg_id
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@P0 bigint)SELECT tse.msg_id, tse.subscriber_id FROM dms_topic_entry tse WHERE tse.read_state = @P0 ORDER BY tse.msg_id
resource-list
keylock hobtid=72057596062859264 dbid=23 objectname=Lightyear.dbo.dms_topic_entry indexname=dms_topic_msg_idx id=lock993c91a80 mode=S associatedObjectId=72057596062859264
owner-list
owner id=process34613828 mode=S
waiter-list
waiter id=process34609eb8 mode=X requestType=wait
keylock hobtid=72057596062793728 dbid=23 objectname=Lightyear.dbo.dms_topic_entry indexname=dms_topic_entry_p id=lock30ee927a00 mode=X associatedObjectId=72057596062793728
owner-list
owner id=process34609eb8 mode=X
waiter-list
waiter id=process34613828 mode=S requestType=wait
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 2, 2010 at 6:51 pm
Since the deadlock victim is the select, there is a good chance that setting the database to READ_COMMITTED_SNAPSHOT will prevent the deadlocks and it will not require any coding changes.
February 3, 2010 at 12:02 am
What indexes exist on the table dms_topic_entry? If this is a key-lookup deadlock (as I suspect) then simply widening the index to be covering will fix this completely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2010 at 4:58 pm
Michael Valentine Jones (2/2/2010)
Since the deadlock victim is the select, there is a good chance that setting the database to READ_COMMITTED_SNAPSHOT will prevent the deadlocks and it will not require any coding changes.
Yes, this is the option I am considering, though not sure if this feature is matured enough to be safely implemented and all the known issues or bugs for snapshot isolation are ironed out...
Another question since you mentioned "will not require any coding changes" which means application will continue to use "read committed" as isolation level and database will automatically start implementing READ_COMMITTED_SNAPSHOT whenever "read committed" request came up...is this the expected behaviour?
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 3, 2010 at 5:00 pm
GilaMonster (2/3/2010)
What indexes exist on the table dms_topic_entry? If this is a key-lookup deadlock (as I suspect) then simply widening the index to be covering will fix this completely.
Right, for some cases this is already implemented to fix this, though there are scenerio where many columns are in select clause, making covering index too big or crossing character limit...
Thus snapshot isolation seems to be the right choice...
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 3, 2010 at 8:21 pm
Prakash Heda (2/3/2010)
Michael Valentine Jones (2/2/2010)
Since the deadlock victim is the select, there is a good chance that setting the database to READ_COMMITTED_SNAPSHOT will prevent the deadlocks and it will not require any coding changes.Yes, this is the option I am considering, though not sure if this feature is matured enough to be safely implemented and all the known issues or bugs for snapshot isolation are ironed out...
Another question since you mentioned "will not require any coding changes" which means application will continue to use "read committed" as isolation level and database will automatically start implementing READ_COMMITTED_SNAPSHOT whenever "read committed" request came up...is this the expected behaviour?
Prakash
I don't know what "known issues or bugs" you are referring to, but it has been available for 5 years with SQL Server. I have used it to solve a number of deadlocking problems. It eliminated the problem in a vendor supplied application where we were getting over 10,000 deadlocks per day.
http://msdn.microsoft.com/en-us/library/ms173763.aspx
"The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
...
If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions."
February 4, 2010 at 1:59 am
Prakash Heda (2/3/2010)
though there are scenerio where many columns are in select clause, making covering index too big or crossing character limit...
There's no character limit to included columns in SQL 2005.
I'll give you the 'too big' though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2010 at 9:48 am
GilaMonster (2/4/2010)
Prakash Heda (2/3/2010)
though there are scenerio where many columns are in select clause, making covering index too big or crossing character limit...There's no character limit to included columns in SQL 2005.
I'll give you the 'too big' though.
Thanks for clarifying such finer detail, this seems could be used as temporary option
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 4, 2010 at 10:37 am
Now this is crazy one I am lost whats happening here...
Here's the question why 944 has exclusive lock on dms_topic_msg_idx here its just doing an update on another table(dms_msg) completely and it has no connection with dms_topic_entry index.....only thing which seems typical is the trancount 2 for 944 why is that? is there any previous transaction for 944 which is not getting captured in deadlock trace?
Prakash
deadlock-list
deadlock victim=process34609c18
process-list
process id=process11c27048 taskpriority=0 logused=520 waitresource=KEY: 23:72057596070133760 (0300a368bd5f) waittime=3703 ownerId=10128819332 transactionname=implicit_transaction lasttranstarted=2010-02-04T08:47:47.523 XDES=0x410d11510 lockMode=X schedulerid=4 kpid=16400 status=suspended spid=934 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-02-04T08:47:47.523 lastbatchcompleted=2010-02-04T08:47:47.523 clientapp=app3 hostname=esprdatg15 hostpid=0 loginname=estoreuser isolationlevel=read committed (2) xactid=10128819332 currentdb=23 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
frame procname=adhoc line=1 stmtstart=24 sqlhandle=0x020000002275650b155381a7b0d46a3be7a3b9e5c6dc24e2
UPDATE dms_msg SET reference_count = reference_count-1 WHERE msg_id = @P0
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@P0 bigint)UPDATE dms_msg SET reference_count = reference_count-1 WHERE msg_id = @P0
process id=process34609c18 taskpriority=0 logused=0 waitresource=KEY: 23:72057596068495360 (7a00547e6007) waittime=3703 ownerId=10128819333 transactionname=implicit_transaction lasttranstarted=2010-02-04T08:47:47.523 XDES=0x19365b1a30 lockMode=S schedulerid=21 kpid=17908 status=suspended spid=351 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2010-02-04T08:47:47.523 lastbatchcompleted=2010-02-04T08:47:47.523 clientapp=csr2 hostname=esprdws66 hostpid=0 loginname=estoreuser isolationlevel=read committed (2) xactid=10128819333 currentdb=23 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
frame procname=adhoc line=1 stmtstart=24 sqlhandle=0x0200000055523b1c4f5c658be203ffe2d811a1d148c469cb
SELECT m.msg_class, m.msg_id, m.timestamp, m.correlation_id, m.reply_to, m.destination, m.delivery_mode, m.redelivered, m.type, m.expiration, m.priority, m.has_properties, m.small_body, m.large_body FROM dms_msg m WHERE m.msg_id IN (SELECT DISTINCT tse.msg_id FROM dms_topic_entry tse WHERE tse.read_state = @P0) ORDER BY m.priority DESC
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@P0 bigint)SELECT m.msg_class, m.msg_id, m.timestamp, m.correlation_id, m.reply_to, m.destination, m.delivery_mode, m.redelivered, m.type, m.expiration, m.priority, m.has_properties, m.small_body, m.large_body FROM dms_msg m WHERE m.msg_id IN (SELECT DISTINCT tse.msg_id FROM dms_topic_entry tse WHERE tse.read_state = @P0) ORDER BY m.priority DESC
resource-list
keylock hobtid=72057596068495360 dbid=23 objectname=Lightyear.dbo.dms_topic_entry indexname=dms_topic_msg_idx id=lock13201e7a00 mode=X associatedObjectId=72057596068495360
owner-list
owner id=process11c27048 mode=X
waiter-list
waiter id=process34609c18 mode=S requestType=wait
keylock hobtid=72057596070133760 dbid=23 objectname=Lightyear.dbo.dms_msg indexname=dms_msg_p id=lock2e413c1100 mode=S associatedObjectId=72057596070133760
owner-list
owner id=process34609c18 mode=S
waiter-list
waiter id=process11c27048 mode=X requestType=wait
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 4, 2010 at 2:33 pm
Attaching the table structure and indexes on deadlock related tables just to complete information....
USE [Lightyear]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dms_topic_entry](
[subscriber_id] [numeric](19, 0) NOT NULL,
[msg_id] [numeric](19, 0) NOT NULL,
[delivery_date] [numeric](19, 0) NULL,
[read_state] [numeric](19, 0) NULL,
CONSTRAINT [dms_topic_entry_p] PRIMARY KEY CLUSTERED
(
[subscriber_id] ASC,
[msg_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [dms_topic_msg_idx] ON [dbo].[dms_topic_entry]
(
[msg_id] ASC,
[subscriber_id] ASC
)
INCLUDE ( [read_state]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [dms_topic_read_idx] ON [dbo].[dms_topic_entry]
(
[read_state] ASC,
[delivery_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dms_msg](
[msg_class] [varchar](250) NULL,
[has_properties] [numeric](1, 0) NULL,
[reference_count] [numeric](10, 0) NULL,
[msg_id] [numeric](19, 0) NOT NULL,
[timestamp] [numeric](19, 0) NULL,
[correlation_id] [varchar](250) NULL,
[reply_to] [numeric](19, 0) NULL,
[destination] [numeric](19, 0) NULL,
[delivery_mode] [numeric](1, 0) NULL,
[redelivered] [numeric](1, 0) NULL,
[type] [varchar](250) NULL,
[expiration] [numeric](19, 0) NULL,
[priority] [numeric](1, 0) NULL,
[small_body] [binary](250) NULL,
[large_body] [image] NULL,
CONSTRAINT [dms_msg_p] PRIMARY KEY CLUSTERED
(
[msg_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [dms_msg_tm] ON [dbo].[dms_msg]
(
[timestamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 5, 2010 at 4:36 pm
I didn't see it in my quick scan of the thread, but here is the Bible for deadlock resolution: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Note there are 2 more parts to that post.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 5, 2010 at 8:14 pm
that link been used by me before posting this thread, i was wondering if i am missing something here...it seems a bug in deadlock graph...hoping to get a second opinion
open a case with ms support also same analysis.
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply