multithreading = sqlserver deadlocks

I created a .net program which uses multithreading. The threads
concurrently access the same db resources. This has generated no end
of sql server deadlocks, which our dba is struggling to resolve.
I am very close to giving up and going ahead and serializing all
database access for the application. This will reduce performance
quite a bit, although it will eliminate all the deadlocks.
Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
mine for building an app that hits the db so hard, but we are unable
to solve all the deadlocking issues using the errorlog and profiling
combo that has been suggested. We solve one deadlock issue, only to
see another one revealed later, perhaps on different resources,
perhaps not.
I have heard there are many applications that use the Singleton
pattern to serialize db access accross the board, just to avoid
situations like this.
Any feedback welcome.Deadlocks are usually caused by 2 or more users attempting to update the
same set of tables in opposite orders. If you have one sp that updates
Orders and then Details while another updates details first and then orders
this will eventually cause a dead lock. If they are both in the same order
you most likely get only blocking which is normal and can be controlled. Do
you have an example of 2 procedures that deadlock on each other and the
associated DDL for those tables? Deadlocking is almost always a result of a
poor schema or more likely poor data access methods. Serialization is not
the way to go, you should do it right and find the cause so you can fix it.
Maybe these will help:
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
--
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402170756.4a53601f@.posting.google.com...
> I created a .net program which uses multithreading. The threads
> concurrently access the same db resources. This has generated no end
> of sql server deadlocks, which our dba is struggling to resolve.
> I am very close to giving up and going ahead and serializing all
> database access for the application. This will reduce performance
> quite a bit, although it will eliminate all the deadlocks.
> Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> mine for building an app that hits the db so hard, but we are unable
> to solve all the deadlocking issues using the errorlog and profiling
> combo that has been suggested. We solve one deadlock issue, only to
> see another one revealed later, perhaps on different resources,
> perhaps not.
> I have heard there are many applications that use the Singleton
> pattern to serialize db access accross the board, just to avoid
> situations like this.
> Any feedback welcome.|||Can't deadlocking also happen for locks other than tables? It can happen
for RID or KEY locks as well within the same table, can't it?
Christian Smith
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Deadlocks are usually caused by 2 or more users attempting to update the
> same set of tables in opposite orders. If you have one sp that updates
> Orders and then Details while another updates details first and then
orders
> this will eventually cause a dead lock. If they are both in the same
order
> you most likely get only blocking which is normal and can be controlled.
Do
> you have an example of 2 procedures that deadlock on each other and the
> associated DDL for those tables? Deadlocking is almost always a result of
a
> poor schema or more likely poor data access methods. Serialization is not
> the way to go, you should do it right and find the cause so you can fix
it.
> Maybe these will help:
>
> http://www.support.microsoft.com/?id=224453 Blocking Problems
> http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> Blocking
>
>
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > I created a .net program which uses multithreading. The threads
> > concurrently access the same db resources. This has generated no end
> > of sql server deadlocks, which our dba is struggling to resolve.
> >
> > I am very close to giving up and going ahead and serializing all
> > database access for the application. This will reduce performance
> > quite a bit, although it will eliminate all the deadlocks.
> >
> > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > mine for building an app that hits the db so hard, but we are unable
> > to solve all the deadlocking issues using the errorlog and profiling
> > combo that has been suggested. We solve one deadlock issue, only to
> > see another one revealed later, perhaps on different resources,
> > perhaps not.
> >
> > I have heard there are many applications that use the Singleton
> > pattern to serialize db access accross the board, just to avoid
> > situations like this.
> >
> > Any feedback welcome.
>|||Yes there are other reasons why dead locks occur but the main reason is as I
posted. Without more details it's all a guess so the most logical or common
reason is what I posted.
--
Andrew J. Kelly
SQL Server MVP
"Christian Smith" <csmith@.digex.com> wrote in message
news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Can't deadlocking also happen for locks other than tables? It can happen
> for RID or KEY locks as well within the same table, can't it?
> Christian Smith
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > Deadlocks are usually caused by 2 or more users attempting to update the
> > same set of tables in opposite orders. If you have one sp that updates
> > Orders and then Details while another updates details first and then
> orders
> > this will eventually cause a dead lock. If they are both in the same
> order
> > you most likely get only blocking which is normal and can be controlled.
> Do
> > you have an example of 2 procedures that deadlock on each other and the
> > associated DDL for those tables? Deadlocking is almost always a result
of
> a
> > poor schema or more likely poor data access methods. Serialization is
not
> > the way to go, you should do it right and find the cause so you can fix
> it.
> > Maybe these will help:
> >
> >
> > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > Blocking
> >
> >
> >
>
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > I created a .net program which uses multithreading. The threads
> > > concurrently access the same db resources. This has generated no end
> > > of sql server deadlocks, which our dba is struggling to resolve.
> > >
> > > I am very close to giving up and going ahead and serializing all
> > > database access for the application. This will reduce performance
> > > quite a bit, although it will eliminate all the deadlocks.
> > >
> > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > mine for building an app that hits the db so hard, but we are unable
> > > to solve all the deadlocking issues using the errorlog and profiling
> > > combo that has been suggested. We solve one deadlock issue, only to
> > > see another one revealed later, perhaps on different resources,
> > > perhaps not.
> > >
> > > I have heard there are many applications that use the Singleton
> > > pattern to serialize db access accross the board, just to avoid
> > > situations like this.
> > >
> > > Any feedback welcome.
> >
> >
>|||One deadlocking situation is fairly simple:
node 1: Select from some_table where rownum = 42
node 2: update some_table with (TABLOCKX) where rownum = 42
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as I
> posted. Without more details it's all a guess so the most logical or common
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Can't deadlocking also happen for locks other than tables? It can happen
> > for RID or KEY locks as well within the same table, can't it?
> >
> > Christian Smith
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > Deadlocks are usually caused by 2 or more users attempting to update the
> > > same set of tables in opposite orders. If you have one sp that updates
> > > Orders and then Details while another updates details first and then
> orders
> > > this will eventually cause a dead lock. If they are both in the same
> order
> > > you most likely get only blocking which is normal and can be controlled.
> Do
> > > you have an example of 2 procedures that deadlock on each other and the
> > > associated DDL for those tables? Deadlocking is almost always a result
> of
> a
> > > poor schema or more likely poor data access methods. Serialization is
> not
> > > the way to go, you should do it right and find the cause so you can fix
> it.
> > > Maybe these will help:
> > >
> > >
> > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > > Blocking
> > >
> > >
> > >
> >
> http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > I created a .net program which uses multithreading. The threads
> > > > concurrently access the same db resources. This has generated no end
> > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > >
> > > > I am very close to giving up and going ahead and serializing all
> > > > database access for the application. This will reduce performance
> > > > quite a bit, although it will eliminate all the deadlocks.
> > > >
> > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > > mine for building an app that hits the db so hard, but we are unable
> > > > to solve all the deadlocking issues using the errorlog and profiling
> > > > combo that has been suggested. We solve one deadlock issue, only to
> > > > see another one revealed later, perhaps on different resources,
> > > > perhaps not.
> > > >
> > > > I have heard there are many applications that use the Singleton
> > > > pattern to serialize db access accross the board, just to avoid
> > > > situations like this.
> > > >
> > > > Any feedback welcome.
> > >
> > >
> >
> >|||Here is the deadlock graph of the previous post:
Wait-for graph
2004-02-18 12:06:37.50 spid4
2004-02-18 12:06:37.50 spid4 Node:1
2004-02-18 12:06:37.51 spid4 KEY: 26:1124251110:2 (130092d8179c)
CleanCnt:1 Mode: U Flags: 0x0
2004-02-18 12:06:37.51 spid4 Grant List 0::
2004-02-18 12:06:37.51 spid4 Owner:0x4fce9ac0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:73 ECID:0
2004-02-18 12:06:37.51 spid4 SPID: 73 ECID: 0 Statement Type:
SELECT Line #: 46
2004-02-18 12:06:37.51 spid4 Input Buf: RPC Event:
S_BPI_BY_BPIID_XML;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:57 ECID:0 Ec:(0x53915568) Value:0x558fa140 Cost:(0/3C)
2004-02-18 12:06:37.53 spid4
2004-02-18 12:06:37.53 spid4 Node:2
2004-02-18 12:06:37.53 spid4 KEY: 26:1124251110:1 (4a004cee256c)
CleanCnt:1 Mode: X Flags: 0x0
2004-02-18 12:06:37.53 spid4 Grant List 0::
2004-02-18 12:06:37.53 spid4 Owner:0x4edb17a0 Mode: X
Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0
2004-02-18 12:06:37.53 spid4 SPID: 57 ECID: 0 Statement Type:
UPDATE Line #: 347
2004-02-18 12:06:37.53 spid4 Input Buf: RPC Event:
U_FAILED_ELI_CT;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:73 ECID:0 Ec:(0x4872B568) Value:0x48b1e3e0 Cost:(0/0)
2004-02-18 12:06:37.53 spid4 Victim Resource Owner:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:73 ECID:0 Ec:(0x4872B568) Value:0x48b1e3e0 Cost:(0/0)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as I
> posted. Without more details it's all a guess so the most logical or common
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Can't deadlocking also happen for locks other than tables? It can happen
> > for RID or KEY locks as well within the same table, can't it?
> >
> > Christian Smith
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > Deadlocks are usually caused by 2 or more users attempting to update the
> > > same set of tables in opposite orders. If you have one sp that updates
> > > Orders and then Details while another updates details first and then
> orders
> > > this will eventually cause a dead lock. If they are both in the same
> order
> > > you most likely get only blocking which is normal and can be controlled.
> Do
> > > you have an example of 2 procedures that deadlock on each other and the
> > > associated DDL for those tables? Deadlocking is almost always a result
> of
> a
> > > poor schema or more likely poor data access methods. Serialization is
> not
> > > the way to go, you should do it right and find the cause so you can fix
> it.
> > > Maybe these will help:
> > >
> > >
> > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > > Blocking
> > >
> > >
> > >
> >
> http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > I created a .net program which uses multithreading. The threads
> > > > concurrently access the same db resources. This has generated no end
> > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > >
> > > > I am very close to giving up and going ahead and serializing all
> > > > database access for the application. This will reduce performance
> > > > quite a bit, although it will eliminate all the deadlocks.
> > > >
> > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > > mine for building an app that hits the db so hard, but we are unable
> > > > to solve all the deadlocking issues using the errorlog and profiling
> > > > combo that has been suggested. We solve one deadlock issue, only to
> > > > see another one revealed later, perhaps on different resources,
> > > > perhaps not.
> > > >
> > > > I have heard there are many applications that use the Singleton
> > > > pattern to serialize db access accross the board, just to avoid
> > > > situations like this.
> > > >
> > > > Any feedback welcome.
> > >
> > >
> >
> >|||Not sure I follow what Node1 & 2 are. Are they 2 different users? Do they
both issue a select and an update? Sometimes it can be more confusing to
only give partial details. There must be more to it than what you show.
Are they wrapped in a transaction and what other statements are between the
begin and commit? But the real question is why on earth are you using a
TABLOCKX?
--
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402201155.64b1af2@.posting.google.com...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> > Yes there are other reasons why dead locks occur but the main reason is
as I
> > posted. Without more details it's all a guess so the most logical or
common
> > reason is what I posted.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Christian Smith" <csmith@.digex.com> wrote in message
> > news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > > Can't deadlocking also happen for locks other than tables? It can
happen
> > > for RID or KEY locks as well within the same table, can't it?
> > >
> > > Christian Smith
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > > Deadlocks are usually caused by 2 or more users attempting to update
the
> > > > same set of tables in opposite orders. If you have one sp that
updates
> > > > Orders and then Details while another updates details first and then
> > orders
> > > > this will eventually cause a dead lock. If they are both in the
same
> > order
> > > > you most likely get only blocking which is normal and can be
controlled.
> > Do
> > > > you have an example of 2 procedures that deadlock on each other and
the
> > > > associated DDL for those tables? Deadlocking is almost always a
result
> > of
> > a
> > > > poor schema or more likely poor data access methods. Serialization
is
> > not
> > > > the way to go, you should do it right and find the cause so you can
fix
> > it.
> > > > Maybe these will help:
> > > >
> > > >
> > > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > > http://www.support.microsoft.com/?id=271509 How to monitor SQL
2000
> > > > Blocking
> > > >
> > > >
> > > >
> > >
> >
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > > I created a .net program which uses multithreading. The threads
> > > > > concurrently access the same db resources. This has generated no
end
> > > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > > >
> > > > > I am very close to giving up and going ahead and serializing all
> > > > > database access for the application. This will reduce performance
> > > > > quite a bit, although it will eliminate all the deadlocks.
> > > > >
> > > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe
it's
> > > > > mine for building an app that hits the db so hard, but we are
unable
> > > > > to solve all the deadlocking issues using the errorlog and
profiling
> > > > > combo that has been suggested. We solve one deadlock issue, only
to
> > > > > see another one revealed later, perhaps on different resources,
> > > > > perhaps not.
> > > > >
> > > > > I have heard there are many applications that use the Singleton
> > > > > pattern to serialize db access accross the board, just to avoid
> > > > > situations like this.
> > > > >
> > > > > Any feedback welcome.
> > > >
> > > >
> > >
> > >|||In article <1faa8782.0402201155.64b1af2@.posting.google.com>,
n_o_s_p_a__m@.mail.com said...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
Actually both would error out because they're not valid SQL statements ;)
Seriously though, why would that deadlock? Whichever grabbed the table
first would finish its work and release it for the other.