We use SQL Server 2000 SP4 with merge replication enabled. The master site
replicates all changes to at multiple subscribers, but we encounter some
problems due to deadlocking issues.
Some actions produce a lot of new data that must be replicated. The table,
in which the data is inserted, has a trigger defined that must be enabled for
replication. Inserting a record into this table takes 70-350ms during normal
operation. It goes through several complex calculations that have been
optimized pretty well.
When the merge agent starts replicating it often receives a deadlock when
inserting data in these tables. After this deadlock it becomes very slow. It
enlists all further records for retrying (Unable to synchronize row due to
unknown reason) and inserts that do get through have durations of over
35.000ms! Of course, this severly hurts replication performance.
I think the triggers for replication are the real probleme due to locking
issues. These calculations need to be performed even when data is replicated
and I don't know another method then using a trigger. Does anyone have a
suggestion?
Greetings,
Ramon de Klein
The replication triggers do cause increased latency of operations on
replicated tables.
Do you have real time requirements for the data that these triggers
calculate? You may want to evaluate having this calculation being performed
in a batch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ramon de Klein" <RamondeKlein@.discussions.microsoft.com> wrote in message
news:2820F1E2-B4B2-4140-BABC-26DC753D69EB@.microsoft.com...
> We use SQL Server 2000 SP4 with merge replication enabled. The master site
> replicates all changes to at multiple subscribers, but we encounter some
> problems due to deadlocking issues.
> Some actions produce a lot of new data that must be replicated. The table,
> in which the data is inserted, has a trigger defined that must be enabled
> for
> replication. Inserting a record into this table takes 70-350ms during
> normal
> operation. It goes through several complex calculations that have been
> optimized pretty well.
> When the merge agent starts replicating it often receives a deadlock when
> inserting data in these tables. After this deadlock it becomes very slow.
> It
> enlists all further records for retrying (Unable to synchronize row due to
> unknown reason) and inserts that do get through have durations of over
> 35.000ms! Of course, this severly hurts replication performance.
> I think the triggers for replication are the real probleme due to locking
> issues. These calculations need to be performed even when data is
> replicated
> and I don't know another method then using a trigger. Does anyone have a
> suggestion?
> --
> Greetings,
> Ramon de Klein
sql
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Thursday, March 29, 2012
Deadlocks and multiple Indexed VIEWs on a table.
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I can get help with.
I have a Table A that has a trigger to update Table B. There are about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, maybe they're getting
updated in a different *order* for different sessions? I would have assumed that it'd
always update in the same order, but I could see where maybe SQL Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas and
opinions!
Thanks! :-)
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_02AA_01C37229.4A089D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are about =6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, maybe =they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL Server =says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and Session =2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any further =ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_02AA_01C37229.4A089D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. SUM() =or BIG_COUNT().
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have a =Table A that has a trigger to update Table B. There are about 6 Indexed VIEWs(some of which are pretty "heavy") that use Table B. When =I have multiple sessions try toinsert into Table A, I'm getting consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And then =we'd have theclassic deadlock conditions of Session 1 requesting X and Y, =and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson
--=_NextPart_000_02AA_01C37229.4A089D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_008F_01C37211.C231F410
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_008F_01C37211.C231F410
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks, Tom! Would lock escalation contribute =to the propensity for a deadlock? I'm using the Profiler, and I see the =Deadlocks -- but I didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using =aggregation -- they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau"= wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. =SUM() or BIG_COUNT().
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have =a Table A that has a trigger to update Table B. There are about 6 =Indexed VIEWs(some of which are pretty "heavy") that use Table B. =When I have multiple sessions try toinsert into Table A, I'm getting =consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And =then we'd have theclassic deadlock conditions of Session 1 requesting X and =Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson
--=_NextPart_000_008F_01C37211.C231F410--|||This is a multi-part message in MIME format.
--=_NextPart_000_02DF_01C3722C.17629510
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Lock escalation converts a row or page lock to a table lock. If you =have an exclusive table lock (as in a monstrous INSERT or UPDATE), =that's going to block everything else from accessing the table. Now, if =you have a number of indexed views that access table B, then access to =those views is also blocked. The longer a lock is held, the greater the =chance for a deadlock.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:#UTgyxkcDHA.3620@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_02DF_01C3722C.17629510
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Lock escalation converts a row or page =lock to a table lock. If you have an exclusive table lock (as in a monstrous INSERT or UPDATE), that's going to block everything else =from accessing the table. Now, if you have a number of indexed views =that access table B, then access to those views is also blocked. The =longer a lock is held, the greater the chance for a deadlock.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:#UTgyxkcDHA.3620=@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute =to the propensity for a deadlock? I'm using the Profiler, and I see the =Deadlocks -- but I didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using =aggregation -- they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau"= wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. =SUM() or BIG_COUNT().
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I have =a Table A that has a trigger to update Table B. There are about 6 =Indexed VIEWs(some of which are pretty "heavy") that use Table B. =When I have multiple sessions try toinsert into Table A, I'm getting =consistent deadlocks.I'm postulating that perhaps, when the Indexed VIEWs =get updated, maybe they're gettingupdated in a different *order* for =different sessions? I would have assumed that it'dalways update in the =same order, but I could see where maybe SQL Server says, ="Oops...thisindex is busy, so I'll go ahead and update this other one first." And =then we'd have theclassic deadlock conditions of Session 1 requesting X and =Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas andopinions!Thanks! :-)John Peterson
--=_NextPart_000_02DF_01C3722C.17629510--|||This is a multi-part message in MIME format.
--=_NextPart_000_00BB_01C37219.8C7BD360
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks again, Tom!
Yeah -- I don't think we have large INSERTs/UPDATEs, but rather just =row-at-a-time. It's puzzling to me why we're seeing these deadlocks, =but we are. Even if we have a bunch of processes just INSERT into this =Table A and have all the Indexed VIEWs get updated we get deadlocked. =I'd think that this process would always have the same "flow" and =potentially avoid deadlocks because there isn't much processing going on =with the exception of the Indexed VIEW updates that are "behind the =scenes".
Do you have any recommendations beyond looking for Escalating Locks that =I should examine? And, if we see Escalation, do you have =tips/techniques for what I can do about that?
Thanks!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23at1k2kcDHA.1204@.TK2MSFTNGP12.phx.gbl...
Lock escalation converts a row or page lock to a table lock. If you =have an exclusive table lock (as in a monstrous INSERT or UPDATE), =that's going to block everything else from accessing the table. Now, if =you have a number of indexed views that access table B, then access to =those views is also blocked. The longer a lock is held, the greater the =chance for a deadlock.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:#UTgyxkcDHA.3620@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case =you can detect this through the Profiler. It's also possible that you =are using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful =I can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." =And then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_00BB_01C37219.8C7BD360
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks again, Tom!
Yeah -- I don't think we have large INSERTs/UPDATEs, =but rather just row-at-a-time. It's puzzling to me why we're seeing =these deadlocks, but we are. Even if we have a bunch of processes just =INSERT into this Table A and have all the Indexed VIEWs get updated we get deadlocked. I'd think that this process would always have the same ="flow" and potentially avoid deadlocks because there isn't much processing =going on with the exception of the Indexed VIEW updates that are "behind the scenes".
Do you have any recommendations beyond looking for =Escalating Locks that I should examine? And, if we see Escalation, do you =have tips/techniques for what I can do about that?
Thanks!
John Peterson
"Tom Moreau"= wrote in message news:%23at1k2kcDHA.=1204@.TK2MSFTNGP12.phx.gbl...
Lock escalation converts a row or =page lock to a table lock. If you have an exclusive table lock (as in a monstrous INSERT or UPDATE), that's going to block everything =else from accessing the table. Now, if you have a number of indexed views =that access table B, then access to those views is also blocked. The =longer a lock is held, the greater the chance for a deadlock.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:#UTgyxkcDHA.3620=@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation =contribute to the propensity for a deadlock? I'm using the Profiler, and I see the = Deadlocks -- but I didn't include the Lock:Escalation =Event.
I don't think any of those Indexed VIEWs are using = aggregation -- they're just pulling a lot of data from a lot of =disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau"= wrote in message news:OiCuHrkcDHA.2960=@.tk2msftngp13.phx.gbl...
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - =e.g. SUM() or BIG_COUNT().
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:Oz8EOmkcDHA.384@.T=K2MSFTNGP12.phx.gbl...(SQL Server 2000, SP3)Hello all!I'm wrestling with some =deadlock issues on a table that I'm hopeful I can get help with.I =have a Table A that has a trigger to update Table B. There are about =6 Indexed VIEWs(some of which are pretty "heavy") that use Table =B. When I have multiple sessions try toinsert into Table A, I'm =getting consistent deadlocks.I'm postulating that perhaps, when the =Indexed VIEWs get updated, maybe they're gettingupdated in a different =*order* for different sessions? I would have assumed that =it'dalways update in the same order, but I could see where maybe SQL Server =says, "Oops...thisindex is busy, so I'll go ahead and update this =other one first." And then we'd have theclassic deadlock conditions =of Session 1 requesting X and Y, and Session 2 requesting Yand X.Clearly, I'm just speculating here. I'm hopeful to =solicit any further ideas andopinions!Thanks! =:-)John Peterson
--=_NextPart_000_00BB_01C37219.8C7BD360--
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I can get help with.
I have a Table A that has a trigger to update Table B. There are about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, maybe they're getting
updated in a different *order* for different sessions? I would have assumed that it'd
always update in the same order, but I could see where maybe SQL Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any further ideas and
opinions!
Thanks! :-)
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_02AA_01C37229.4A089D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are about =6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, maybe =they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL Server =says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and Session =2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any further =ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_02AA_01C37229.4A089D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. SUM() =or BIG_COUNT().
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_02AA_01C37229.4A089D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_008F_01C37211.C231F410
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_008F_01C37211.C231F410
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks, Tom! Would lock escalation contribute =to the propensity for a deadlock? I'm using the Profiler, and I see the =Deadlocks -- but I didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using =aggregation -- they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau"
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. =SUM() or BIG_COUNT().
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_008F_01C37211.C231F410--|||This is a multi-part message in MIME format.
--=_NextPart_000_02DF_01C3722C.17629510
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Lock escalation converts a row or page lock to a table lock. If you =have an exclusive table lock (as in a monstrous INSERT or UPDATE), =that's going to block everything else from accessing the table. Now, if =you have a number of indexed views that access table B, then access to =those views is also blocked. The longer a lock is held, the greater the =chance for a deadlock.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:#UTgyxkcDHA.3620@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case you =can detect this through the Profiler. It's also possible that you are =using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful I =can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." And =then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_02DF_01C3722C.17629510
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Lock escalation converts a row or page =lock to a table lock. If you have an exclusive table lock (as in a monstrous INSERT or UPDATE), that's going to block everything else =from accessing the table. Now, if you have a number of indexed views =that access table B, then access to those views is also blocked. The =longer a lock is held, the greater the chance for a deadlock.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
Thanks, Tom! Would lock escalation contribute =to the propensity for a deadlock? I'm using the Profiler, and I see the =Deadlocks -- but I didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using =aggregation -- they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau"
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - e.g. =SUM() or BIG_COUNT().
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_02DF_01C3722C.17629510--|||This is a multi-part message in MIME format.
--=_NextPart_000_00BB_01C37219.8C7BD360
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks again, Tom!
Yeah -- I don't think we have large INSERTs/UPDATEs, but rather just =row-at-a-time. It's puzzling to me why we're seeing these deadlocks, =but we are. Even if we have a bunch of processes just INSERT into this =Table A and have all the Indexed VIEWs get updated we get deadlocked. =I'd think that this process would always have the same "flow" and =potentially avoid deadlocks because there isn't much processing going on =with the exception of the Indexed VIEW updates that are "behind the =scenes".
Do you have any recommendations beyond looking for Escalating Locks that =I should examine? And, if we see Escalation, do you have =tips/techniques for what I can do about that?
Thanks!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23at1k2kcDHA.1204@.TK2MSFTNGP12.phx.gbl...
Lock escalation converts a row or page lock to a table lock. If you =have an exclusive table lock (as in a monstrous INSERT or UPDATE), =that's going to block everything else from accessing the table. Now, if =you have a number of indexed views that access table B, then access to =those views is also blocked. The longer a lock is held, the greater the =chance for a deadlock.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:#UTgyxkcDHA.3620@.TK2MSFTNGP11.phx.gbl...
Thanks, Tom! Would lock escalation contribute to the propensity for a =deadlock? I'm using the Profiler, and I see the Deadlocks -- but I =didn't include the Lock:Escalation Event.
I don't think any of those Indexed VIEWs are using aggregation -- =they're just pulling a lot of data from a lot of disparate tables.
Thanks again for any help you can provide!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OiCuHrkcDHA.2960@.tk2msftngp13.phx.gbl...
It's possible that you are getting lock escalation, in which case =you can detect this through the Profiler. It's also possible that you =are using aggregation in those views - e.g. SUM() or BIG_COUNT().
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:Oz8EOmkcDHA.384@.TK2MSFTNGP12.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I'm wrestling with some deadlock issues on a table that I'm hopeful =I can get help with.
I have a Table A that has a trigger to update Table B. There are =about 6 Indexed VIEWs
(some of which are pretty "heavy") that use Table B. When I have =multiple sessions try to
insert into Table A, I'm getting consistent deadlocks.
I'm postulating that perhaps, when the Indexed VIEWs get updated, =maybe they're getting
updated in a different *order* for different sessions? I would have =assumed that it'd
always update in the same order, but I could see where maybe SQL =Server says, "Oops...this
index is busy, so I'll go ahead and update this other one first." =And then we'd have the
classic deadlock conditions of Session 1 requesting X and Y, and =Session 2 requesting Y
and X.
Clearly, I'm just speculating here. I'm hopeful to solicit any =further ideas and
opinions!
Thanks! :-)
John Peterson
--=_NextPart_000_00BB_01C37219.8C7BD360
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks again, Tom!
Yeah -- I don't think we have large INSERTs/UPDATEs, =but rather just row-at-a-time. It's puzzling to me why we're seeing =these deadlocks, but we are. Even if we have a bunch of processes just =INSERT into this Table A and have all the Indexed VIEWs get updated we get deadlocked. I'd think that this process would always have the same ="flow" and potentially avoid deadlocks because there isn't much processing =going on with the exception of the Indexed VIEW updates that are "behind the scenes".
Do you have any recommendations beyond looking for =Escalating Locks that I should examine? And, if we see Escalation, do you =have tips/techniques for what I can do about that?
Thanks!
John Peterson
"Tom Moreau"
Lock escalation converts a row or =page lock to a table lock. If you have an exclusive table lock (as in a monstrous INSERT or UPDATE), that's going to block everything =else from accessing the table. Now, if you have a number of indexed views =that access table B, then access to those views is also blocked. The =longer a lock is held, the greater the chance for a deadlock.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
Thanks, Tom! Would lock escalation =contribute to the propensity for a deadlock? I'm using the Profiler, and I see the = Deadlocks -- but I didn't include the Lock:Escalation =Event.
I don't think any of those Indexed VIEWs are using = aggregation -- they're just pulling a lot of data from a lot of =disparate tables.
Thanks again for any help you can =provide!
John Peterson
"Tom Moreau"
It's possible that you are getting =lock escalation, in which case you can detect this through the =Profiler. It's also possible that you are using aggregation in those views - =e.g. SUM() or BIG_COUNT().
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson"
--=_NextPart_000_00BB_01C37219.8C7BD360--
Tuesday, March 27, 2012
Deadlocks
I have in a table with some 2000 records with a Document ID which is a Unique Seq Number and details with a Status Flag. Multiple Users access the Table. When a user accesses a particular Document ID the Status flag changes from "N" to "W". Once he finishes working with data on that particular Document ID the Status Changes to "C". When another user tries to pickup a Record the next record in the Seq with the Status as "N" will get fetched. I use a Stored Procedure to assign a Document id to a user and update the Record/status details in the Table. I have used the No lock clause in the SP while retrieving a particular Document ID. It was working fine till sometime. Now it has started giving the following error
Error: Run-time error '-2147467259(80004005)'
[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction(process ID#127)was deadlocked with another process and as been chosen as the deadlock victim. Return your transaction.
Some one please Help me on how to go abt the problem. Thanks in advance.
Regards
Dinesh1. Run sp_recompile 'object_name' for all objects used.
2. Post it all. SP+DDL
Good luck !
Error: Run-time error '-2147467259(80004005)'
[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction(process ID#127)was deadlocked with another process and as been chosen as the deadlock victim. Return your transaction.
Some one please Help me on how to go abt the problem. Thanks in advance.
Regards
Dinesh1. Run sp_recompile 'object_name' for all objects used.
2. Post it all. SP+DDL
Good luck !
Sunday, March 25, 2012
Deadlock updating different rows
Here is the condensed version of my question: why do I get a deadlock
when multiple threads are updating different rows in the same table?
Details: I have a table with a clustered index spread across multiple
columns. I run multiple threads, each of which accesses a separate row
in the table. Nevertheless, I see deadlocks.
SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
is
requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
is
requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
It is my understanding that the KEY locks are essentially row-level
locks because with clustered indexes the data are leaf nodes of the
index. As you can see, each thread is requesting a lock held by the
other. The locks are on the same index but different rows (the long
hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
I can't understand why different threads updating distinct rows would
ever want to lock the same rows. Granted, the rows may be adjacent,
but should that cause the acquisition of locks on rows other than the
one being updated? I could understand a broader locking if INSERTs
were happening, but that is not the case.
Thanks for any help you can offer.Is the index unique? Please post the complete table DDL and UPDATE
statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135189033.769214.220820@.f14g2000cwb.googlegroups.com...
> Here is the condensed version of my question: why do I get a deadlock
> when multiple threads are updating different rows in the same table?
> Details: I have a table with a clustered index spread across multiple
> columns. I run multiple threads, each of which accesses a separate row
> in the table. Nevertheless, I see deadlocks.
> SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
> is
> requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
> SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
> is
> requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
> It is my understanding that the KEY locks are essentially row-level
> locks because with clustered indexes the data are leaf nodes of the
> index. As you can see, each thread is requesting a lock held by the
> other. The locks are on the same index but different rows (the long
> hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
> I can't understand why different threads updating distinct rows would
> ever want to lock the same rows. Granted, the rows may be adjacent,
> but should that cause the acquisition of locks on rows other than the
> one being updated? I could understand a broader locking if INSERTs
> were happening, but that is not the case.
> Thanks for any help you can offer.
>|||Dan,
Thanks for your reply. Index is unique. Here is the table definition:
create TABLE [Sum_Item_Revenue] (
[tendered_business_period_dim_id] [int] NOT NULL ,
[posted_business_period_dim_id] [int] NOT NULL ,
[event_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___event__5B78929E] DEFAULT (0),
[profit_center_dim_id] [int] NOT NULL ,
[misc_period_dim_id] [int] NOT NULL ,
[pay_type_dim_id] [int] NOT NULL ,
[emp_dim_id] [int] NOT NULL ,
[item_dim_id] [int] NOT NULL ,
[total_sales_gross_amount] [decimal](18, 4) NULL ,
[total_discount_amount] [decimal](18, 4) NULL ,
[ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___order__45FE52CB] DEFAULT (0),
CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
(
[tendered_business_period_dim_id],
[posted_business_period_dim_id],
[event_dim_id],
[profit_center_dim_id],
[misc_period_dim_id],
[pay_type_dim_id],
[emp_dim_id],
[item_dim_id],
[ordered_profit_center_dim_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
It turns out it's not just a straight UPDATE but a stored procedure. I
realize the stored procedure has the potential to do INSERTs but for
the testing I've been doing, it's all been updates, because the records
already exist. Here is the stored proc:
create procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
Declare @.count int
SELECT @.count = count(*)
FROM Sum_Item_Revenue
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
IF @.count = 0
INSERT INTO Sum_Item_Revenue
( tendered_business_period_dim_id ,
posted_business_period_dim_id ,
profit_center_dim_id ,
ordered_profit_center_dim_id ,
misc_period_dim_id ,
pay_type_dim_id ,
emp_dim_id ,
item_dim_id ,
total_sales_gross_amount ,
total_discount_amount
)
VALUES
( @.tendered_business_period_dim_id ,
@.posted_business_period_dim_id ,
@.profit_center_dim_id ,
@.ordered_profit_center_dim_id ,
@.misc_period_dim_id ,
@.pay_type_dim_id ,
@.emp_dim_id ,
@.item_dim_id ,
@.total_sales_gross_amount ,
@.total_discount_amount
)
ELSE
UPDATE Sum_Item_Revenue SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount ,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
I've removed some non-essential columns from the table for the purposes
of this posting, to remove clutter.
I call this routine from multiple threads, where each thread passes in
a unique profit_center_dim_id. Other values of the key are similar.
So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
SELECT is manifested by one of my SPIDs above attempting to obtain a
shared lock.
Thanks, rand|||Try this:
BEGIN TRAN
IF EXISTS(SELECT 1 FROM...WITH(UPDLOCK, HOLDLOCK) WHERE...)
BEGIN
UPDATE...
--error handling here
END
ELSE
BEGIN
INSERT...
--error handling here
END
COMMIT TRAN
WITH(UPDLOCK,HOLDLOCK) places an update range-lock on the table that is
about to be modified. This does not affect select concurrency, because
other transactions can obtain shared locks on rows that already have an
update lock. It only affects insert/update concurrency and not by much. It
will eliminate the deadlock that you're encountering.
The construct below doesn't take into account the fact that another
transaction can obtain a lock on the row to be updated between the SELECT
and the UPDATE or INSERT.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Brian, Thanks I'll give it a try. I should add that the lack of
transaction semantics within my stored procedure is because this
procedure is invoked from .NET code within BeginTransaction() and
Commit() using the default isolation level of Read Committed. Slightly
bigger picture: I'm multi-threading code that has heretofore been
single-threaded. The thing that has me baffled is why there is any
lock contention at all, given that different threads should be
accessing different rows. Unless my assumption is incorrect and the
locks I see are really not row-level, but are table- or page-level.|||First, I prefer to handle transaction processing within the stored
procedure. This makes it a lot easier to troubleshoot deadlocks and to
change code--for example, to implement optimistic concurrency.
Second, READ COMMITTED is good for reporting; for modifications, it is a
disaster waiting to happen. You should use REPEATABLE READ or preferably
SERIALIZABLE if the information you're reading will be used in a subsequent
modification within the same transaction. As a rule, Rows selected that may
be updated should have an update lock applied and held until the transaction
commits; rows selected that will not be updated but whose value will be used
either directly or indirectly as values that will be inserted or updated
should have a shared lock applied and held. This is extremely important to
keep garbage out of your database. Any change to the source data between
the SELECT and the UPDATE/INSERT renders the results you've just read out
stale, which can introduce incorrect information into the database. If the
update involves inserting or updating summary information, then you should
use SERIALIZABLE because an INSERT will cause the results to become stale.
READ COMMITTED doesn't prevent changes from occuring between the SELECT and
the UPDATE/INSERT, and REPEATABLE READ doesn't prevent new rows that meet
the criteria used for summarization from being inserted.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135217308.843942.88810@.z14g2000cwz.googlegroups.com...
> Brian, Thanks I'll give it a try. I should add that the lack of
> transaction semantics within my stored procedure is because this
> procedure is invoked from .NET code within BeginTransaction() and
> Commit() using the default isolation level of Read Committed. Slightly
> bigger picture: I'm multi-threading code that has heretofore been
> single-threaded. The thing that has me baffled is why there is any
> lock contention at all, given that different threads should be
> accessing different rows. Unless my assumption is incorrect and the
> locks I see are really not row-level, but are table- or page-level.
>|||I see that the event_dim_id column is part of the primary key but is not
included in the where clause of the SELECT or UPDATE. This could increase
the likelihood of your deadlocks.
Brian pointed out that you are vulnerable to changes between the SELECT and
INSERT/UPDATE. Since you run the proc is run as part of a transaction,
below is another 'UPSERT' technique that I like to use. I hard-coded a zero
value for event_dim_id in this example.
alter procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
SET NOCOUNT ON
INSERT INTO Sum_Item_Revenue
(
tendered_business_period_dim_id,
posted_business_period_dim_id,
profit_center_dim_id,
ordered_profit_center_dim_id,
misc_period_dim_id,
pay_type_dim_id,
emp_dim_id,
item_dim_id,
total_sales_gross_amount,
total_discount_amount
)
SELECT
@.tendered_business_period_dim_id,
@.posted_business_period_dim_id,
@.profit_center_dim_id,
@.ordered_profit_center_dim_id,
@.misc_period_dim_id,
@.pay_type_dim_id,
@.emp_dim_id,
@.item_dim_id,
@.total_sales_gross_amount,
@.total_discount_amount
WHERE NOT EXISTS
(
SELECT *
FROM Sum_Item_Revenue WITH (UPDLOCK, HOLDLOCK)
WHERE
tendered_business_period_dim_id =@.tendered_business_period_dim_id
AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
)
IF @.@.ROWCOUNT = 0
BEGIN
UPDATE Sum_Item_Revenue
SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id
=@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id
AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Thanks Dan & Brian. I will experiment. Any idea why different threads
accessing different rows should even be contending for resources at
all? Dan, event_dim_id is not significant since it is not used and
always has a default value of 0.|||Look at the lock information in your original post. It's not enough that
you're only modifying one row at a time. Your procedure also reads rows:
that's why you get deadlocks. Both SPIDs have exclusive locks on one row,
but before the transactions commit, they also are trying to obtain shared or
update locks on the other transaction's row. What's strange is that the
locks represented in the original post don't match what you would get from
your procedure. I suspect that there is another procedure involved. You
have an update lock, but the procedure you posted doesn't have
WITH(UPDLOCK). It is my understanding that update locks are only obtained
when an explicit locking hint is specified.
Is it possible that other statements are issued by the application. There
are many other things that could be causing the deadlocks. That's why I
prefer to encapsulate database updates in procedures, and whenever possible,
to handle any transaction processing within those procedures. It makes
troubleshooting much, MUCH easier.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>|||> Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
The event_dim_id column might not be significant from your perspective but
SQL Server can't make the assumption that only one row will be returned
unless you include it in your WHERE clause. Also, the column is badly
needed to use the primary key index effectively. Check out the details of
the SEEK operator in the query plan without and with event_dim_id:
--without event_dim_id: scans all values with specified
tendered_business_period_dim_id
--and posted_business_period_dim_id
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]),
WHERE:((((([Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id]
AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d]) AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id]) AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id]) AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id]) AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
--without event_dim_id: single row retrieved via s
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]
AND
[Sum_Item_Revenue].[event_dim_id]=0 AND
[Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id] AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d] AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id] AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id] AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id] AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
Not only will the inefficient plan hurt performance, it can contribute to
the likelihood of deadlocks.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>
when multiple threads are updating different rows in the same table?
Details: I have a table with a clustered index spread across multiple
columns. I run multiple threads, each of which accesses a separate row
in the table. Nevertheless, I see deadlocks.
SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
is
requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
is
requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
It is my understanding that the KEY locks are essentially row-level
locks because with clustered indexes the data are leaf nodes of the
index. As you can see, each thread is requesting a lock held by the
other. The locks are on the same index but different rows (the long
hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
I can't understand why different threads updating distinct rows would
ever want to lock the same rows. Granted, the rows may be adjacent,
but should that cause the acquisition of locks on rows other than the
one being updated? I could understand a broader locking if INSERTs
were happening, but that is not the case.
Thanks for any help you can offer.Is the index unique? Please post the complete table DDL and UPDATE
statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135189033.769214.220820@.f14g2000cwb.googlegroups.com...
> Here is the condensed version of my question: why do I get a deadlock
> when multiple threads are updating different rows in the same table?
> Details: I have a table with a clustered index spread across multiple
> columns. I run multiple threads, each of which accesses a separate row
> in the table. Nevertheless, I see deadlocks.
> SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
> is
> requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
> SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
> is
> requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
> It is my understanding that the KEY locks are essentially row-level
> locks because with clustered indexes the data are leaf nodes of the
> index. As you can see, each thread is requesting a lock held by the
> other. The locks are on the same index but different rows (the long
> hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
> I can't understand why different threads updating distinct rows would
> ever want to lock the same rows. Granted, the rows may be adjacent,
> but should that cause the acquisition of locks on rows other than the
> one being updated? I could understand a broader locking if INSERTs
> were happening, but that is not the case.
> Thanks for any help you can offer.
>|||Dan,
Thanks for your reply. Index is unique. Here is the table definition:
create TABLE [Sum_Item_Revenue] (
[tendered_business_period_dim_id] [int] NOT NULL ,
[posted_business_period_dim_id] [int] NOT NULL ,
[event_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___event__5B78929E] DEFAULT (0),
[profit_center_dim_id] [int] NOT NULL ,
[misc_period_dim_id] [int] NOT NULL ,
[pay_type_dim_id] [int] NOT NULL ,
[emp_dim_id] [int] NOT NULL ,
[item_dim_id] [int] NOT NULL ,
[total_sales_gross_amount] [decimal](18, 4) NULL ,
[total_discount_amount] [decimal](18, 4) NULL ,
[ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___order__45FE52CB] DEFAULT (0),
CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
(
[tendered_business_period_dim_id],
[posted_business_period_dim_id],
[event_dim_id],
[profit_center_dim_id],
[misc_period_dim_id],
[pay_type_dim_id],
[emp_dim_id],
[item_dim_id],
[ordered_profit_center_dim_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
It turns out it's not just a straight UPDATE but a stored procedure. I
realize the stored procedure has the potential to do INSERTs but for
the testing I've been doing, it's all been updates, because the records
already exist. Here is the stored proc:
create procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
Declare @.count int
SELECT @.count = count(*)
FROM Sum_Item_Revenue
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
IF @.count = 0
INSERT INTO Sum_Item_Revenue
( tendered_business_period_dim_id ,
posted_business_period_dim_id ,
profit_center_dim_id ,
ordered_profit_center_dim_id ,
misc_period_dim_id ,
pay_type_dim_id ,
emp_dim_id ,
item_dim_id ,
total_sales_gross_amount ,
total_discount_amount
)
VALUES
( @.tendered_business_period_dim_id ,
@.posted_business_period_dim_id ,
@.profit_center_dim_id ,
@.ordered_profit_center_dim_id ,
@.misc_period_dim_id ,
@.pay_type_dim_id ,
@.emp_dim_id ,
@.item_dim_id ,
@.total_sales_gross_amount ,
@.total_discount_amount
)
ELSE
UPDATE Sum_Item_Revenue SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount ,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
I've removed some non-essential columns from the table for the purposes
of this posting, to remove clutter.
I call this routine from multiple threads, where each thread passes in
a unique profit_center_dim_id. Other values of the key are similar.
So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
SELECT is manifested by one of my SPIDs above attempting to obtain a
shared lock.
Thanks, rand|||Try this:
BEGIN TRAN
IF EXISTS(SELECT 1 FROM...WITH(UPDLOCK, HOLDLOCK) WHERE...)
BEGIN
UPDATE...
--error handling here
END
ELSE
BEGIN
INSERT...
--error handling here
END
COMMIT TRAN
WITH(UPDLOCK,HOLDLOCK) places an update range-lock on the table that is
about to be modified. This does not affect select concurrency, because
other transactions can obtain shared locks on rows that already have an
update lock. It only affects insert/update concurrency and not by much. It
will eliminate the deadlock that you're encountering.
The construct below doesn't take into account the fact that another
transaction can obtain a lock on the row to be updated between the SELECT
and the UPDATE or INSERT.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Brian, Thanks I'll give it a try. I should add that the lack of
transaction semantics within my stored procedure is because this
procedure is invoked from .NET code within BeginTransaction() and
Commit() using the default isolation level of Read Committed. Slightly
bigger picture: I'm multi-threading code that has heretofore been
single-threaded. The thing that has me baffled is why there is any
lock contention at all, given that different threads should be
accessing different rows. Unless my assumption is incorrect and the
locks I see are really not row-level, but are table- or page-level.|||First, I prefer to handle transaction processing within the stored
procedure. This makes it a lot easier to troubleshoot deadlocks and to
change code--for example, to implement optimistic concurrency.
Second, READ COMMITTED is good for reporting; for modifications, it is a
disaster waiting to happen. You should use REPEATABLE READ or preferably
SERIALIZABLE if the information you're reading will be used in a subsequent
modification within the same transaction. As a rule, Rows selected that may
be updated should have an update lock applied and held until the transaction
commits; rows selected that will not be updated but whose value will be used
either directly or indirectly as values that will be inserted or updated
should have a shared lock applied and held. This is extremely important to
keep garbage out of your database. Any change to the source data between
the SELECT and the UPDATE/INSERT renders the results you've just read out
stale, which can introduce incorrect information into the database. If the
update involves inserting or updating summary information, then you should
use SERIALIZABLE because an INSERT will cause the results to become stale.
READ COMMITTED doesn't prevent changes from occuring between the SELECT and
the UPDATE/INSERT, and REPEATABLE READ doesn't prevent new rows that meet
the criteria used for summarization from being inserted.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135217308.843942.88810@.z14g2000cwz.googlegroups.com...
> Brian, Thanks I'll give it a try. I should add that the lack of
> transaction semantics within my stored procedure is because this
> procedure is invoked from .NET code within BeginTransaction() and
> Commit() using the default isolation level of Read Committed. Slightly
> bigger picture: I'm multi-threading code that has heretofore been
> single-threaded. The thing that has me baffled is why there is any
> lock contention at all, given that different threads should be
> accessing different rows. Unless my assumption is incorrect and the
> locks I see are really not row-level, but are table- or page-level.
>|||I see that the event_dim_id column is part of the primary key but is not
included in the where clause of the SELECT or UPDATE. This could increase
the likelihood of your deadlocks.
Brian pointed out that you are vulnerable to changes between the SELECT and
INSERT/UPDATE. Since you run the proc is run as part of a transaction,
below is another 'UPSERT' technique that I like to use. I hard-coded a zero
value for event_dim_id in this example.
alter procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
SET NOCOUNT ON
INSERT INTO Sum_Item_Revenue
(
tendered_business_period_dim_id,
posted_business_period_dim_id,
profit_center_dim_id,
ordered_profit_center_dim_id,
misc_period_dim_id,
pay_type_dim_id,
emp_dim_id,
item_dim_id,
total_sales_gross_amount,
total_discount_amount
)
SELECT
@.tendered_business_period_dim_id,
@.posted_business_period_dim_id,
@.profit_center_dim_id,
@.ordered_profit_center_dim_id,
@.misc_period_dim_id,
@.pay_type_dim_id,
@.emp_dim_id,
@.item_dim_id,
@.total_sales_gross_amount,
@.total_discount_amount
WHERE NOT EXISTS
(
SELECT *
FROM Sum_Item_Revenue WITH (UPDLOCK, HOLDLOCK)
WHERE
tendered_business_period_dim_id =@.tendered_business_period_dim_id
AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
)
IF @.@.ROWCOUNT = 0
BEGIN
UPDATE Sum_Item_Revenue
SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id
=@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id
AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Thanks Dan & Brian. I will experiment. Any idea why different threads
accessing different rows should even be contending for resources at
all? Dan, event_dim_id is not significant since it is not used and
always has a default value of 0.|||Look at the lock information in your original post. It's not enough that
you're only modifying one row at a time. Your procedure also reads rows:
that's why you get deadlocks. Both SPIDs have exclusive locks on one row,
but before the transactions commit, they also are trying to obtain shared or
update locks on the other transaction's row. What's strange is that the
locks represented in the original post don't match what you would get from
your procedure. I suspect that there is another procedure involved. You
have an update lock, but the procedure you posted doesn't have
WITH(UPDLOCK). It is my understanding that update locks are only obtained
when an explicit locking hint is specified.
Is it possible that other statements are issued by the application. There
are many other things that could be causing the deadlocks. That's why I
prefer to encapsulate database updates in procedures, and whenever possible,
to handle any transaction processing within those procedures. It makes
troubleshooting much, MUCH easier.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>|||> Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
The event_dim_id column might not be significant from your perspective but
SQL Server can't make the assumption that only one row will be returned
unless you include it in your WHERE clause. Also, the column is badly
needed to use the primary key index effectively. Check out the details of
the SEEK operator in the query plan without and with event_dim_id:
--without event_dim_id: scans all values with specified
tendered_business_period_dim_id
--and posted_business_period_dim_id
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]),
WHERE:((((([Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id]
AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d]) AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id]) AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id]) AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id]) AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
--without event_dim_id: single row retrieved via s
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]
AND
[Sum_Item_Revenue].[event_dim_id]=0 AND
[Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id] AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d] AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id] AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id] AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id] AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
Not only will the inefficient plan hurt performance, it can contribute to
the likelihood of deadlocks.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>
Thursday, March 22, 2012
Deadlock Question
Hi All,
Can multiple updates on one table using single
query generate deadlock ?
For example, at the same time, there are 2 users
run 2 queries as follows :
User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no
User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no
Note :
The content of the column "no" on table tab2 :
('A','B','C',...,'X','Y','Z')
The content of the column "no" on table tab3
is like in table tab2, but in different order :
('Z','Y','X',....,'C','B','A')
Thanks in advance
Anita Hery
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Anita (anonymous@.devdex.com) writes:
> Can multiple updates on one table using single
> query generate deadlock ?
> For example, at the same time, there are 2 users
> run 2 queries as follows :
> User1 runs :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> User2 runs :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab3 on tab1.no = tab3.no
> Note :
> The content of the column "no" on table tab2 :
> ('A','B','C',...,'X','Y','Z')
> The content of the column "no" on table tab3
> is like in table tab2, but in different order :
> ('Z','Y','X',....,'C','B','A')
Tables in a relational database are sets, and data has no order.
But, of course, for the evaluation of a query the physical order may
affect such things as deadlock.
Anyway, I am not going to answer the question directly, because there
is a lot of unknown elements. Is tbl.v a primary key or at least
indexed? What about tbl2.no and tbl3.no? And what exactly is
different order?
CREATE TABLE statements for the tables and INSERT statemetns for the
data may help.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,
Thanks for your reply.
Deadlock can be found easily in several command steps.
But, how can I find it in multiple updates using
only one step of command ?
This question is posted because I do not know
exactly how SQL Server handles my sample query.
And I become worry after reading many deadlock
articles here. Especially deadlock that is caused
by table index.
Below is the description of the tables :
Table tb1 :
- no CHAR(10); no2 CHAR(10); v INT
- Index possibility : only one index, on no or on no2
- no is unique, no2 is unique
- v is not a key.
Table tb2 :
- x CHAR(10); no CHAR(10)
- Index : on x
- x is not unique, no is unique
Table tb3 :
- x CHAR(10); no CHAR(10)
- Index : on x
- x is not unique, no is unique
Regards,
Anita Hery
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> Deadlock can be found easily in several command steps.
> But, how can I find it in multiple updates using
> only one step of command ?
Testing deadlocks that may occur from single statements are indeed not
trivial to construct at will.
One possibility is to write a small app - could even be a stored procedure
- that runs the supicious SQL statement all over again in an infinite
loop. If you get a deadlock, you now know that it can happen. If you
don't get a deadlock - well you still don't know, because may the test
was not good enough.
Another approach is to introduce a waitstate somewhere, so that you get
a chance to start a second query window with the competing query. This
is not trivial either. For a simple case, I used this function some
time ago:
create function nisse () returns int as
begin
exec master..xp_cmdshell 'osql -E -n -Q "WAITFOR DELAY ''00:00:20''"'
return 1
end
In your case, at least one your updates should read:
UPDATE tbl
SET col = dbo.nisse() -- Or some expression including dbo.nisse().
...
But of course, this constructs a situation which is not really the same
as the real-world scenario, and the observations may not be transferrable.
(But it seems to me that in this case, they could.)
Since you did not provide CREATE TABLE statements and INSERT statements
with sample data, I was too lazy to actually try this technique with
your example. :-)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||When SQL Server receive query :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no
I expect it follows the procedure like this :
a. Find the rows that will be updated.
b. If they are not found then exit.
c. Try locking the rows found.
d. If locking is successfull then update the
rows and exit.
e. Wait for miliseconds.
f. If query timeout expires then exit.
g. goto c.
Since I do not have information about how SQL Server
handles the query, I usually insert tablock hint in the query :
update tab1 with (tablock) set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no
Though by using tablock hint it will lock all rows
in the table (prevent other rows from being updated
by other users) but, I think I should take this way.
It is free from deadlock.
Anita Hery
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> When SQL Server receive query :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> I expect it follows the procedure like this :
> a. Find the rows that will be updated.
> b. If they are not found then exit.
> c. Try locking the rows found.
> d. If locking is successfull then update the
> rows and exit.
> e. Wait for miliseconds.
> f. If query timeout expires then exit.
> g. goto c.
I have to admit that I don't fully master the internal procedure, but
I would expect it to be somewhat different. I would expect that already
when SQL Server finds the matching rows that it applies at least
shared locks, possible also intent locks. Once a row is found to
qualify, I would suppose SQL Server puts an exclusive lock on a
row.
You mention "query timeout". I suppose you mean lock timeout, which you
control with SET LOCK_TIMEOUT. Query timeout is a client (mis)feature,
and does not affect locking.
Going back to your original post, you had these two statements:
User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no
User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no
Working from my assumptions above - which I like to stress are nothing
but assumptions, you could get a deadlock here, if the statistics on
the table are such that the optimizer chooses different query plans.
For instance, for the first query, the optimizer decides to scan tab1
and then perform a nested join with tab2. But for the second query,
the optimizer scans tab3, and performs a nested join with index seek
on tab1. If the two queries start at the same time, they will find
matching rows in tab1 in different order, and therefor they will
deadlock.
> Since I do not have information about how SQL Server
> handles the query, I usually insert tablock hint in the query :
> update tab1 with (tablock) set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> Though by using tablock hint it will lock all rows
> in the table (prevent other rows from being updated
> by other users) but, I think I should take this way.
> It is free from deadlock.
Yes, this should be deadlock free. But there are of course other issues
with tablock. If most updates are on single rows, tablock might be too
heavy-handed and lead to concurrency issues.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,
Thanks for your reply,
Yes, your assumption is somewhat different with what I expect. I expect
: if there are 10 matching rows and SQL Server can lock only 9 rows,
then : SQL Server unlock 9 rows, wait for a moment, and try locking 10
rows again.
If my expectation is true, then the query is deadlock free and I will
avoid using tablock hint.
My last question is where I can get information that tell us your
assumption or my expectation is true ?
Regards,
Anita Hery
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> Yes, your assumption is somewhat different with what I expect. I expect
>: if there are 10 matching rows and SQL Server can lock only 9 rows,
> then : SQL Server unlock 9 rows, wait for a moment, and try locking 10
> rows again.
> If my expectation is true, then the query is deadlock free and I will
> avoid using tablock hint.
> My last question is where I can get information that tell us your
> assumption or my expectation is true ?
So much I can tell with confidence, that SQL Server never releases locks
because it cannot get all locks it needs to carry out a task. While such
a strategy could reduce deadlock, it could have other nasty effects like
lock starvation. A process that needs to access many rows in a busy system
would never get all locks.
Also, I believe that the work order is something like: lock one row,
update that row, lock next row and so on. In this case, it is of course
even less possible to release rows.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,
Thanks for all your replies.
Regards,
Anita Hery
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Wednesday, March 21, 2012
deadlock on a single table but multiple processes
Hi! We have a third party application that calls same stored procedure
simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
Deadlock trace shows both spids are running exactly same statement within
the procedure. Depending upon input parameter the statement does either
insert or update. But the deadlock trace shows that deadlock happens when
both are running update statements. Multiple thread supposed to update same
table but different rows (at most couple of rows).
The object (key) they are deadlocking on is a non clustered index used to
search data for update. Update statement doesn't modify any column that
belongs to this non clustered index. Database is running on default
(read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
the stored procedrue, so I assume that the statement is not a part of
explicit transaction.
Questions:
1. Why sql server is using update lock (And not the shared lock) on the non
clustered index which used to search the data. The update statement doesn't
modify this non clustered index. In below statement Index id 5 is on
position_id, security_alias and long_short_indicator.
2. Why deadlock and not just blocking? What is a fix for this?
Below is the update_statement that both SPID are running:
UPDATE CA
SET CANCEL_STATUS = 'Y',
UPDATE_SOURCE = @.in_update_source,
UPDATE_DATE = GETDATE()
from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
WHERE POSITION_ID = @.nTargetPositionId
AND SECURITY_ALIAS = @.in_security_alias
AND LONG_SHORT_INDICATOR = 'L'
AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
AND STAR_TAG25 = @.in_event_id
AND CASH_BAL_INST = @.in_event_sequence
AND CANCEL_FLAG = 'N'
AND REFLEXIVE_FLOW = 'Y'
Below is output of deadlock trace:
Deadlock encountered ... Printing deadlock information
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Wait-for graph
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:1
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
CleanCnt:2 Mode: X Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 3::
2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:589 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:470 ECID:0 Ec
0x72B99520) Value:0xb61fa660 Cost0/7280)
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:2
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
CleanCnt:2 Mode: U Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 2::
2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:470 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
Hi,
May I know why Index hint is used in update statement
(index(IND_CASH_ACT_SPD1))?
Manu
"James" wrote:
> Hi! We have a third party application that calls same stored procedure
> simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
> Deadlock trace shows both spids are running exactly same statement within
> the procedure. Depending upon input parameter the statement does either
> insert or update. But the deadlock trace shows that deadlock happens when
> both are running update statements. Multiple thread supposed to update same
> table but different rows (at most couple of rows).
> The object (key) they are deadlocking on is a non clustered index used to
> search data for update. Update statement doesn't modify any column that
> belongs to this non clustered index. Database is running on default
> (read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
> the stored procedrue, so I assume that the statement is not a part of
> explicit transaction.
> Questions:
> 1. Why sql server is using update lock (And not the shared lock) on the non
> clustered index which used to search the data. The update statement doesn't
> modify this non clustered index. In below statement Index id 5 is on
> position_id, security_alias and long_short_indicator.
> 2. Why deadlock and not just blocking? What is a fix for this?
> Below is the update_statement that both SPID are running:
> UPDATE CA
> SET CANCEL_STATUS = 'Y',
> UPDATE_SOURCE = @.in_update_source,
> UPDATE_DATE = GETDATE()
> from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
> WHERE POSITION_ID = @.nTargetPositionId
> AND SECURITY_ALIAS = @.in_security_alias
> AND LONG_SHORT_INDICATOR = 'L'
> AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
> AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
> AND STAR_TAG25 = @.in_event_id
> AND CASH_BAL_INST = @.in_event_sequence
> AND CANCEL_FLAG = 'N'
> AND REFLEXIVE_FLOW = 'Y'
> Below is output of deadlock trace:
> Deadlock encountered ... Printing deadlock information
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Wait-for graph
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:1
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
> CleanCnt:2 Mode: X Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 3::
> 2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:589 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:470 ECID:0 Ec0x72B99520) Value:0xb61fa660 Cost0/7280)
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:2
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
> CleanCnt:2 Mode: U Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 2::
> 2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
> Ref:0 Life:00000001 SPID:470 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
> 2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
>
>
simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
Deadlock trace shows both spids are running exactly same statement within
the procedure. Depending upon input parameter the statement does either
insert or update. But the deadlock trace shows that deadlock happens when
both are running update statements. Multiple thread supposed to update same
table but different rows (at most couple of rows).
The object (key) they are deadlocking on is a non clustered index used to
search data for update. Update statement doesn't modify any column that
belongs to this non clustered index. Database is running on default
(read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
the stored procedrue, so I assume that the statement is not a part of
explicit transaction.
Questions:
1. Why sql server is using update lock (And not the shared lock) on the non
clustered index which used to search the data. The update statement doesn't
modify this non clustered index. In below statement Index id 5 is on
position_id, security_alias and long_short_indicator.
2. Why deadlock and not just blocking? What is a fix for this?
Below is the update_statement that both SPID are running:
UPDATE CA
SET CANCEL_STATUS = 'Y',
UPDATE_SOURCE = @.in_update_source,
UPDATE_DATE = GETDATE()
from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
WHERE POSITION_ID = @.nTargetPositionId
AND SECURITY_ALIAS = @.in_security_alias
AND LONG_SHORT_INDICATOR = 'L'
AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
AND STAR_TAG25 = @.in_event_id
AND CASH_BAL_INST = @.in_event_sequence
AND CANCEL_FLAG = 'N'
AND REFLEXIVE_FLOW = 'Y'
Below is output of deadlock trace:
Deadlock encountered ... Printing deadlock information
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Wait-for graph
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:1
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
CleanCnt:2 Mode: X Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 3::
2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:589 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:470 ECID:0 Ec
0x72B99520) Value:0xb61fa660 Cost0/7280)2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:2
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
CleanCnt:2 Mode: U Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 2::
2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:470 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec
0x7445F520) Value:0x3dfb5460 Cost0/1FA4)2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec
0x7445F520) Value:0x3dfb5460 Cost0/1FA4)Hi,
May I know why Index hint is used in update statement
(index(IND_CASH_ACT_SPD1))?
Manu
"James" wrote:
> Hi! We have a third party application that calls same stored procedure
> simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
> Deadlock trace shows both spids are running exactly same statement within
> the procedure. Depending upon input parameter the statement does either
> insert or update. But the deadlock trace shows that deadlock happens when
> both are running update statements. Multiple thread supposed to update same
> table but different rows (at most couple of rows).
> The object (key) they are deadlocking on is a non clustered index used to
> search data for update. Update statement doesn't modify any column that
> belongs to this non clustered index. Database is running on default
> (read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
> the stored procedrue, so I assume that the statement is not a part of
> explicit transaction.
> Questions:
> 1. Why sql server is using update lock (And not the shared lock) on the non
> clustered index which used to search the data. The update statement doesn't
> modify this non clustered index. In below statement Index id 5 is on
> position_id, security_alias and long_short_indicator.
> 2. Why deadlock and not just blocking? What is a fix for this?
> Below is the update_statement that both SPID are running:
> UPDATE CA
> SET CANCEL_STATUS = 'Y',
> UPDATE_SOURCE = @.in_update_source,
> UPDATE_DATE = GETDATE()
> from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
> WHERE POSITION_ID = @.nTargetPositionId
> AND SECURITY_ALIAS = @.in_security_alias
> AND LONG_SHORT_INDICATOR = 'L'
> AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
> AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
> AND STAR_TAG25 = @.in_event_id
> AND CASH_BAL_INST = @.in_event_sequence
> AND CANCEL_FLAG = 'N'
> AND REFLEXIVE_FLOW = 'Y'
> Below is output of deadlock trace:
> Deadlock encountered ... Printing deadlock information
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Wait-for graph
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:1
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
> CleanCnt:2 Mode: X Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 3::
> 2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:589 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:470 ECID:0 Ec
0x72B99520) Value:0xb61fa660 Cost0/7280)> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:2
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
> CleanCnt:2 Mode: U Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 2::
> 2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
> Ref:0 Life:00000001 SPID:470 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec
0x7445F520) Value:0x3dfb5460 Cost0/1FA4)> 2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec
0x7445F520) Value:0x3dfb5460 Cost0/1FA4)>
>
deadlock on a single table but multiple processes
Hi! We have a third party application that calls same stored procedure
simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
Deadlock trace shows both spids are running exactly same statement within
the procedure. Depending upon input parameter the statement does either
insert or update. But the deadlock trace shows that deadlock happens when
both are running update statements. Multiple thread supposed to update same
table but different rows (at most couple of rows).
The object (key) they are deadlocking on is a non clustered index used to
search data for update. Update statement doesn't modify any column that
belongs to this non clustered index. Database is running on default
(read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
the stored procedrue, so I assume that the statement is not a part of
explicit transaction.
Questions:
1. Why sql server is using update lock (And not the shared lock) on the non
clustered index which used to search the data. The update statement doesn't
modify this non clustered index. In below statement Index id 5 is on
position_id, security_alias and long_short_indicator.
2. Why deadlock and not just blocking? What is a fix for this?
Below is the update_statement that both SPID are running:
UPDATE CA
SET CANCEL_STATUS = 'Y',
UPDATE_SOURCE = @.in_update_source,
UPDATE_DATE = GETDATE()
from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
WHERE POSITION_ID = @.nTargetPositionId
AND SECURITY_ALIAS = @.in_security_alias
AND LONG_SHORT_INDICATOR = 'L'
AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
AND STAR_TAG25 = @.in_event_id
AND CASH_BAL_INST = @.in_event_sequence
AND CANCEL_FLAG = 'N'
AND REFLEXIVE_FLOW = 'Y'
Below is output of deadlock trace:
Deadlock encountered ... Printing deadlock information
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Wait-for graph
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:1
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
CleanCnt:2 Mode: X Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 3::
2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:589 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:470 ECID:0 Ec0x72B99520) Value:0xb61fa660 Cost0/7280)
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:2
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
CleanCnt:2 Mode: U Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 2::
2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:470 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)Hi,
May I know why Index hint is used in update statement
(index(IND_CASH_ACT_SPD1))?
Manu
"James" wrote:
> Hi! We have a third party application that calls same stored procedure
> simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
> Deadlock trace shows both spids are running exactly same statement within
> the procedure. Depending upon input parameter the statement does either
> insert or update. But the deadlock trace shows that deadlock happens when
> both are running update statements. Multiple thread supposed to update sam
e
> table but different rows (at most couple of rows).
> The object (key) they are deadlocking on is a non clustered index used to
> search data for update. Update statement doesn't modify any column that
> belongs to this non clustered index. Database is running on default
> (read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
> the stored procedrue, so I assume that the statement is not a part of
> explicit transaction.
> Questions:
> 1. Why sql server is using update lock (And not the shared lock) on the no
n
> clustered index which used to search the data. The update statement doesn'
t
> modify this non clustered index. In below statement Index id 5 is on
> position_id, security_alias and long_short_indicator.
> 2. Why deadlock and not just blocking? What is a fix for this?
> Below is the update_statement that both SPID are running:
> UPDATE CA
> SET CANCEL_STATUS = 'Y',
> UPDATE_SOURCE = @.in_update_source,
> UPDATE_DATE = GETDATE()
> from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
> WHERE POSITION_ID = @.nTargetPositionId
> AND SECURITY_ALIAS = @.in_security_alias
> AND LONG_SHORT_INDICATOR = 'L'
> AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
> AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
> AND STAR_TAG25 = @.in_event_id
> AND CASH_BAL_INST = @.in_event_sequence
> AND CANCEL_FLAG = 'N'
> AND REFLEXIVE_FLOW = 'Y'
> Below is output of deadlock trace:
> Deadlock encountered ... Printing deadlock information
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Wait-for graph
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:1
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
> CleanCnt:2 Mode: X Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 3::
> 2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x
0
> Ref:0 Life:02000000 SPID:589 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDA
TE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:470 ECID:0 Ec0x72B99520) Value:0xb61fa660 Cost0/7280)
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:2
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
> CleanCnt:2 Mode: U Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 2::
> 2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x
0
> Ref:0 Life:00000001 SPID:470 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDA
TE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
> 2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec0x7445F520) Value:0x3dfb5460 Cost0/1FA4)
>
>sql
simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
Deadlock trace shows both spids are running exactly same statement within
the procedure. Depending upon input parameter the statement does either
insert or update. But the deadlock trace shows that deadlock happens when
both are running update statements. Multiple thread supposed to update same
table but different rows (at most couple of rows).
The object (key) they are deadlocking on is a non clustered index used to
search data for update. Update statement doesn't modify any column that
belongs to this non clustered index. Database is running on default
(read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
the stored procedrue, so I assume that the statement is not a part of
explicit transaction.
Questions:
1. Why sql server is using update lock (And not the shared lock) on the non
clustered index which used to search the data. The update statement doesn't
modify this non clustered index. In below statement Index id 5 is on
position_id, security_alias and long_short_indicator.
2. Why deadlock and not just blocking? What is a fix for this?
Below is the update_statement that both SPID are running:
UPDATE CA
SET CANCEL_STATUS = 'Y',
UPDATE_SOURCE = @.in_update_source,
UPDATE_DATE = GETDATE()
from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
WHERE POSITION_ID = @.nTargetPositionId
AND SECURITY_ALIAS = @.in_security_alias
AND LONG_SHORT_INDICATOR = 'L'
AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
AND STAR_TAG25 = @.in_event_id
AND CASH_BAL_INST = @.in_event_sequence
AND CANCEL_FLAG = 'N'
AND REFLEXIVE_FLOW = 'Y'
Below is output of deadlock trace:
Deadlock encountered ... Printing deadlock information
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Wait-for graph
2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:1
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
CleanCnt:2 Mode: X Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 3::
2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:589 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:470 ECID:0 Ec
0x72B99520) Value:0xb61fa660 Cost0/7280)2007-12-20 07:54:11.27 spid1
2007-12-20 07:54:11.27 spid1 Node:2
2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
CleanCnt:2 Mode: U Flags: 0x0
2007-12-20 07:54:11.27 spid1 Grant List 2::
2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:470 ECID:0
2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDATE
Line #: 42
2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
PACE_MASTER..INSERT_CASH_ACTIVITY;1
2007-12-20 07:54:11.27 spid1 Requested By:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec
0x7445F520) Value:0x3dfb5460 Cost0/1FA4)2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:589 ECID:0 Ec
0x7445F520) Value:0x3dfb5460 Cost0/1FA4)Hi,May I know why Index hint is used in update statement
(index(IND_CASH_ACT_SPD1))?
Manu
"James" wrote:
> Hi! We have a third party application that calls same stored procedure
> simultaneously (around 10 spids). We are seeing hundreds of deadlocks.
> Deadlock trace shows both spids are running exactly same statement within
> the procedure. Depending upon input parameter the statement does either
> insert or update. But the deadlock trace shows that deadlock happens when
> both are running update statements. Multiple thread supposed to update sam
e
> table but different rows (at most couple of rows).
> The object (key) they are deadlocking on is a non clustered index used to
> search data for update. Update statement doesn't modify any column that
> belongs to this non clustered index. Database is running on default
> (read_commited) mode and Its sql 2000 SP4. I haven't seen "begin tran" in
> the stored procedrue, so I assume that the statement is not a part of
> explicit transaction.
> Questions:
> 1. Why sql server is using update lock (And not the shared lock) on the no
n
> clustered index which used to search the data. The update statement doesn'
t
> modify this non clustered index. In below statement Index id 5 is on
> position_id, security_alias and long_short_indicator.
> 2. Why deadlock and not just blocking? What is a fix for this?
> Below is the update_statement that both SPID are running:
> UPDATE CA
> SET CANCEL_STATUS = 'Y',
> UPDATE_SOURCE = @.in_update_source,
> UPDATE_DATE = GETDATE()
> from CASH.DBO.CASH_ACTIVITY CA (index(IND_CASH_ACT_SPD1))
> WHERE POSITION_ID = @.nTargetPositionId
> AND SECURITY_ALIAS = @.in_security_alias
> AND LONG_SHORT_INDICATOR = 'L'
> AND SOURCE_SECURITY_ALIAS = @.in_source_security_alias
> AND SOURCE_LONG_SHORT_IND = @.in_source_long_short_ind
> AND STAR_TAG25 = @.in_event_id
> AND CASH_BAL_INST = @.in_event_sequence
> AND CANCEL_FLAG = 'N'
> AND REFLEXIVE_FLOW = 'Y'
> Below is output of deadlock trace:
> Deadlock encountered ... Printing deadlock information
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Wait-for graph
> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:1
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (5d01ef3a25c6)
> CleanCnt:2 Mode: X Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 3::
> 2007-12-20 07:54:11.27 spid1 Owner:0x3dfb4480 Mode: X Flg:0x
0
> Ref:0 Life:02000000 SPID:589 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 589 ECID: 0 Statement Type: UPDA
TE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:470 ECID:0 Ec
0x72B99520) Value:0xb61fa660 Cost0/7280)> 2007-12-20 07:54:11.27 spid1
> 2007-12-20 07:54:11.27 spid1 Node:2
> 2007-12-20 07:54:11.27 spid1 KEY: 10:738101670:5 (d5013fde36a9)
> CleanCnt:2 Mode: U Flags: 0x0
> 2007-12-20 07:54:11.27 spid1 Grant List 2::
> 2007-12-20 07:54:11.27 spid1 Owner:0xb69c32c0 Mode: U Flg:0x
0
> Ref:0 Life:00000001 SPID:470 ECID:0
> 2007-12-20 07:54:11.27 spid1 SPID: 470 ECID: 0 Statement Type: UPDA
TE
> Line #: 42
> 2007-12-20 07:54:11.27 spid1 Input Buf: RPC Event:
> PACE_MASTER..INSERT_CASH_ACTIVITY;1
> 2007-12-20 07:54:11.27 spid1 Requested By:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec
0x7445F520) Value:0x3dfb5460 Cost0/1FA4)> 2007-12-20 07:54:11.27 spid1 Victim Resource Owner:
> 2007-12-20 07:54:11.27 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:589 ECID:0 Ec
0x7445F520) Value:0x3dfb5460 Cost0/1FA4)>
>sql
Deadlock issue SQLServer2000
Hi,
I'm facing a deadlock issue in a stored procedure which only deletes
records from multiple tables.When i run this stotred proc. multiple
times I get deadlock between two SPIDs running the same stored
procedure's code.On drilling down in SQL trace using flag 1205 and the
SQL server trace I found that these are conversion deadlocks.The
records are deleted using PK in follwing sequence of
tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
TbSFUsage -> TbCID -> TbCustomer.
As is evident from names of tables - TbUserService
,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFU
sage,TbUser
depends(FK) on TbCID
tables -
TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
tables -
TbCID depends(FK) on TbCustomer.
This is SQL trace I get...
Deadlock encountered ... Printing deadlock information
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Wait-for graph
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Node:1
2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.27 spid4 Wait List:
2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollba
ck;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:78 ECID:0 Ec0x1B58F568) Value:0x193ff980 Cost0/614)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:2
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollba
ck;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:85 ECID:0 Ec0x1D915568) Value:0x1940e180 Cost0/518)
2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:3
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
CleanCnt:1 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageProfiInitializeRollbac
k;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
2004-10-04 19:06:30.34 spid4
the resource 277576027 above is table TbCIDAdmin!
Until and unless i change the sequence of deletes I get the deadlock
in same table.
Can anyone figure out why is this happening?
I feel the problem lies in FK constraint checking while deleting rows.
Because SQL server must be reading(Shared Lock) the child tables
before deleting row from a parent table. Also, when I disabled all
foreign key constraint checking I stopped getting the deadlock errors!
If this is the reason can anyone pl. tell me how can I fix this?
Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
this stored procedure and at the same time other stored
procedures/transaction can work with the constraint checking as
ususal.
There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
. Can we somehow replicate this functionlaity in SQL server 2000?
Pl. help because this problem has become a real pain ....
Thanks in advance
BipulConstranits are good so do not turn them off so eagerly.
You might want to try select .. with (updlock) to avoid conversion related
deadlocks.
Here is an example of a deadlock free sequence.
begin tran
select ... from dept-table with (updlock) where dept_id = 99
delete employee-table where dept_id = 99
delete dept-table where dept_id = 99
commit
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bipul" <itsbipul@.gmail.com> wrote in message
news:f280e5a9.0410112211.2245022e@.posting.google.com...
> Hi,
> I'm facing a deadlock issue in a stored procedure which only deletes
> records from multiple tables.When i run this stotred proc. multiple
> times I get deadlock between two SPIDs running the same stored
> procedure's code.On drilling down in SQL trace using flag 1205 and the
> SQL server trace I found that these are conversion deadlocks.The
> records are deleted using PK in follwing sequence of
> tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> TbSFUsage -> TbCID -> TbCustomer.
> As is evident from names of tables - TbUserService
> ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFU
sage,TbUser
> depends(FK) on TbCID
> tables -
> TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> tables -
> TbCID depends(FK) on TbCustomer.
> This is SQL trace I get...
> Deadlock encountered ... Printing deadlock information
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Wait-for graph
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Node:1
> 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.27 spid4 Wait List:
> 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollba
ck;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:78 ECID:0 Ec0x1B58F568) Value:0x193ff980 Cost0/614)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:2
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollba
ck;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:85 ECID:0 Ec0x1D915568) Value:0x1940e180 Cost0/518)
> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:3
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> CleanCnt:1 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageProfiInitializeRollbac
k;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
> 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:83 ECID:0 Ec0x1DA1B568) Value:0x1940f1c0 Cost0/518)
> 2004-10-04 19:06:30.34 spid4
> the resource 277576027 above is table TbCIDAdmin!
> Until and unless i change the sequence of deletes I get the deadlock
> in same table.
> Can anyone figure out why is this happening?
> I feel the problem lies in FK constraint checking while deleting rows.
> Because SQL server must be reading(Shared Lock) the child tables
> before deleting row from a parent table. Also, when I disabled all
> foreign key constraint checking I stopped getting the deadlock errors!
> If this is the reason can anyone pl. tell me how can I fix this?
> Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> this stored procedure and at the same time other stored
> procedures/transaction can work with the constraint checking as
> ususal.
> There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> . Can we somehow replicate this functionlaity in SQL server 2000?
> Pl. help because this problem has become a real pain ....
> Thanks in advance
> Bipul|||Hi xiao,
I dont have any SELECT statements inside the transaction in my stored
procedure.
I have only DELETE statements with WHERE clause on the PK(whihc is
clustered index).
I'm basically not able to understand why deadlock chain is happening?
If you see the SQL Log I have provided in the first mail, all the
SPIDs are locking on the same Table and on the same IndId(index)and
each are having X lock and requesting for S lock. How is this possible
that each have been granted a X lock on same resource (since hash
values are different I guess they corresond to different records in
same table)? Should not they Block instead of deadlock? Will TABLOCK
help? But,then I would have to have TABLOCK on all tables from which
I'm deleteing in the transaction, which won't be a good idea?
Awaiting your comments?
Regds,
Bipul
yahoo/MSN id : itsbipul
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message news:<umxXUROsEHA.3564@.tk
2msftngp13.phx.gbl>...[vbcol=seagreen]
> Constranits are good so do not turn them off so eagerly.
> You might want to try select .. with (updlock) to avoid conversion related
> deadlocks.
> Here is an example of a deadlock free sequence.
> begin tran
> select ... from dept-table with (updlock) where dept_id = 99
> delete employee-table where dept_id = 99
> delete dept-table where dept_id = 99
> commit
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Bipul" <itsbipul@.gmail.com> wrote in message
> news:f280e5a9.0410112211.2245022e@.posting.google.com...|||On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
>Awaiting your comments?
Are you doing joins in your delete statements?
Can you show your code?
It is curious, since you own the locks on early deletes, but I'd like
to try to figure out what SQLServer *thinks* it's doing!
Can you break the transaction into several independent pieces - quick
workaround, probably.
J.|||Hi,
This indeed is an intriguing problem. I don't have any joins in the
Delete queries. These are just pure delete statements with 'where'
clause on PK of the table from which I delete.
But,yes as you can see from my first mail there is child parent
relationship between the tables I'm deleting.
I can not break the transaction into smaller pieces.
What do you think will be the problem?
What I think is that when SQL server is deleting from the parent table
it searches the child tables for checking FK violations. For this it
takes locks on the indexes of these child tables and then 2 pids doing
the same get deadlocked on index resource.
Am I thinking on right lines ? or there is something else?
Awaiting responses...
Regds,
Bipul
JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh
@.4ax.com>...
> On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> Are you doing joins in your delete statements?
> Can you show your code?
> It is curious, since you own the locks on early deletes, but I'd like
> to try to figure out what SQLServer *thinks* it's doing!
> Can you break the transaction into several independent pieces - quick
> workaround, probably.
> J.|||Hi Bipul,
Is this problem resolved ? I was going thru the newsgroup to learn more
about deadlocks. Reading the thread and the response from Wei Xiao, I have a
feeling that he had the select statement with updlock in his transaction to
prevent sql server from allowing any sharing of those rows. so make your
delete work, you can try this:
begin tran
get upd lock on childTab
delete from childTab
delete from parentTab
commit tran
One thing that is confusing is that Wei had a select on the parentTab with
updlock, whereas your deadlock was due to contention on a child table. My
above suggestion is based on your assumption that sql server is not able to
establish the shared lock on the child table while checking the constraint.
Regards,
Mani.
"Bipul" wrote:
> Hi,
> This indeed is an intriguing problem. I don't have any joins in the
> Delete queries. These are just pure delete statements with 'where'
> clause on PK of the table from which I delete.
> But,yes as you can see from my first mail there is child parent
> relationship between the tables I'm deleting.
> I can not break the transaction into smaller pieces.
> What do you think will be the problem?
> What I think is that when SQL server is deleting from the parent table
> it searches the child tables for checking FK violations. For this it
> takes locks on the indexes of these child tables and then 2 pids doing
> the same get deadlocked on index resource.
> Am I thinking on right lines ? or there is something else?
> Awaiting responses...
> Regds,
> Bipul
>
>
> JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n6
3buuhoh85qte4asjvh@.4ax.com>...
>
I'm facing a deadlock issue in a stored procedure which only deletes
records from multiple tables.When i run this stotred proc. multiple
times I get deadlock between two SPIDs running the same stored
procedure's code.On drilling down in SQL trace using flag 1205 and the
SQL server trace I found that these are conversion deadlocks.The
records are deleted using PK in follwing sequence of
tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
TbSFUsage -> TbCID -> TbCustomer.
As is evident from names of tables - TbUserService
,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFU
sage,TbUser
depends(FK) on TbCID
tables -
TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
tables -
TbCID depends(FK) on TbCustomer.
This is SQL trace I get...
Deadlock encountered ... Printing deadlock information
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Wait-for graph
2004-10-04 19:06:25.27 spid4
2004-10-04 19:06:25.27 spid4 Node:1
2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.27 spid4 Wait List:
2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollba
ck;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:78 ECID:0 Ec
0x1B58F568) Value:0x193ff980 Cost0/614)2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:2
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
CleanCnt:2 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageExpertInitializeRollba
ck;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:85 ECID:0 Ec
0x1D915568) Value:0x1940e180 Cost0/518)2004-10-04 19:06:25.32 spid4
2004-10-04 19:06:25.32 spid4 Node:3
2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
CleanCnt:1 Mode: X Flags: 0x0
2004-10-04 19:06:25.32 spid4 Grant List 0::
2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
DELETE Line #: 241
2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
SpCreateCIDWebPageProfiInitializeRollbac
k;1
2004-10-04 19:06:25.32 spid4 Requested By:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:83 ECID:0 Ec
0x1DA1B568) Value:0x1940f1c0 Cost0/518)2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:83 ECID:0 Ec
0x1DA1B568) Value:0x1940f1c0 Cost0/518)2004-10-04 19:06:30.34 spid4
the resource 277576027 above is table TbCIDAdmin!
Until and unless i change the sequence of deletes I get the deadlock
in same table.
Can anyone figure out why is this happening?
I feel the problem lies in FK constraint checking while deleting rows.
Because SQL server must be reading(Shared Lock) the child tables
before deleting row from a parent table. Also, when I disabled all
foreign key constraint checking I stopped getting the deadlock errors!
If this is the reason can anyone pl. tell me how can I fix this?
Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
this stored procedure and at the same time other stored
procedures/transaction can work with the constraint checking as
ususal.
There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
. Can we somehow replicate this functionlaity in SQL server 2000?
Pl. help because this problem has become a real pain ....
Thanks in advance
BipulConstranits are good so do not turn them off so eagerly.
You might want to try select .. with (updlock) to avoid conversion related
deadlocks.
Here is an example of a deadlock free sequence.
begin tran
select ... from dept-table with (updlock) where dept_id = 99
delete employee-table where dept_id = 99
delete dept-table where dept_id = 99
commit
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bipul" <itsbipul@.gmail.com> wrote in message
news:f280e5a9.0410112211.2245022e@.posting.google.com...
> Hi,
> I'm facing a deadlock issue in a stored procedure which only deletes
> records from multiple tables.When i run this stotred proc. multiple
> times I get deadlock between two SPIDs running the same stored
> procedure's code.On drilling down in SQL trace using flag 1205 and the
> SQL server trace I found that these are conversion deadlocks.The
> records are deleted using PK in follwing sequence of
> tables:-TbCIDAdmin ->TbCIDProduct ->TbUserService -> TbUserRole ->
> TbUserAction -> TbCIDUsage ->TbUser ->TbPhoneNumber -> TbAddress ->
> TbSFUsage -> TbCID -> TbCustomer.
> As is evident from names of tables - TbUserService
> ,TbUserRole,TbUserAction,TbCIDAdmin depends(FK) on TbUser
> tables - TbCIDAdmin,TbCIDProduct,TbCIDUsage,TbSFU
sage,TbUser
> depends(FK) on TbCID
> tables -
> TbUser depends(FK) on TbPhoneNumber,TbAddress,TbCID
> tables -
> TbCID depends(FK) on TbCustomer.
> This is SQL trace I get...
> Deadlock encountered ... Printing deadlock information
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Wait-for graph
> 2004-10-04 19:06:25.27 spid4
> 2004-10-04 19:06:25.27 spid4 Node:1
> 2004-10-04 19:06:25.27 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.27 spid4 Wait List:
> 2004-10-04 19:06:25.27 spid4 Owner:0x1940e180 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:85 ECID:0
> 2004-10-04 19:06:25.27 spid4 SPID: 85 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.31 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollba
ck;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:78 ECID:0 Ec
0x1B58F568) Value:0x193ff980 Cost0/614)> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:2
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (170315753ddb)
> CleanCnt:2 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940dc80 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:83 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 83 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageExpertInitializeRollba
ck;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:85 ECID:0 Ec
0x1D915568) Value:0x1940e180 Cost0/518)> 2004-10-04 19:06:25.32 spid4
> 2004-10-04 19:06:25.32 spid4 Node:3
> 2004-10-04 19:06:25.32 spid4 KEY: 17:277576027:1 (1d034e61a3fa)
> CleanCnt:1 Mode: X Flags: 0x0
> 2004-10-04 19:06:25.32 spid4 Grant List 0::
> 2004-10-04 19:06:25.32 spid4 Owner:0x1940e340 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:78 ECID:0
> 2004-10-04 19:06:25.32 spid4 SPID: 78 ECID: 0 Statement Type:
> DELETE Line #: 241
> 2004-10-04 19:06:25.32 spid4 Input Buf: RPC Event:
> SpCreateCIDWebPageProfiInitializeRollbac
k;1
> 2004-10-04 19:06:25.32 spid4 Requested By:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:83 ECID:0 Ec
0x1DA1B568) Value:0x1940f1c0 Cost0/518)> 2004-10-04 19:06:25.32 spid4 Victim Resource Owner:
> 2004-10-04 19:06:25.32 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:83 ECID:0 Ec
0x1DA1B568) Value:0x1940f1c0 Cost0/518)> 2004-10-04 19:06:30.34 spid4
> the resource 277576027 above is table TbCIDAdmin!
> Until and unless i change the sequence of deletes I get the deadlock
> in same table.
> Can anyone figure out why is this happening?
> I feel the problem lies in FK constraint checking while deleting rows.
> Because SQL server must be reading(Shared Lock) the child tables
> before deleting row from a parent table. Also, when I disabled all
> foreign key constraint checking I stopped getting the deadlock errors!
> If this is the reason can anyone pl. tell me how can I fix this?
> Can we anyhow delay constraint cheking till I COMMIT TRANSACTION in
> this stored procedure and at the same time other stored
> procedures/transaction can work with the constraint checking as
> ususal.
> There used to be something like DISABLE_DEF_CNST_CHK in SQL server 6.5
> . Can we somehow replicate this functionlaity in SQL server 2000?
> Pl. help because this problem has become a real pain ....
> Thanks in advance
> Bipul|||Hi xiao,
I dont have any SELECT statements inside the transaction in my stored
procedure.
I have only DELETE statements with WHERE clause on the PK(whihc is
clustered index).
I'm basically not able to understand why deadlock chain is happening?
If you see the SQL Log I have provided in the first mail, all the
SPIDs are locking on the same Table and on the same IndId(index)and
each are having X lock and requesting for S lock. How is this possible
that each have been granted a X lock on same resource (since hash
values are different I guess they corresond to different records in
same table)? Should not they Block instead of deadlock? Will TABLOCK
help? But,then I would have to have TABLOCK on all tables from which
I'm deleteing in the transaction, which won't be a good idea?
Awaiting your comments?
Regds,
Bipul
yahoo/MSN id : itsbipul
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message news:<umxXUROsEHA.3564@.tk
2msftngp13.phx.gbl>...[vbcol=seagreen]
> Constranits are good so do not turn them off so eagerly.
> You might want to try select .. with (updlock) to avoid conversion related
> deadlocks.
> Here is an example of a deadlock free sequence.
> begin tran
> select ... from dept-table with (updlock) where dept_id = 99
> delete employee-table where dept_id = 99
> delete dept-table where dept_id = 99
> commit
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Bipul" <itsbipul@.gmail.com> wrote in message
> news:f280e5a9.0410112211.2245022e@.posting.google.com...|||On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
>Awaiting your comments?
Are you doing joins in your delete statements?
Can you show your code?
It is curious, since you own the locks on early deletes, but I'd like
to try to figure out what SQLServer *thinks* it's doing!
Can you break the transaction into several independent pieces - quick
workaround, probably.
J.|||Hi,
This indeed is an intriguing problem. I don't have any joins in the
Delete queries. These are just pure delete statements with 'where'
clause on PK of the table from which I delete.
But,yes as you can see from my first mail there is child parent
relationship between the tables I'm deleting.
I can not break the transaction into smaller pieces.
What do you think will be the problem?
What I think is that when SQL server is deleting from the parent table
it searches the child tables for checking FK violations. For this it
takes locks on the indexes of these child tables and then 2 pids doing
the same get deadlocked on index resource.
Am I thinking on right lines ? or there is something else?
Awaiting responses...
Regds,
Bipul
JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n63buuhoh85qte4asjvh
@.4ax.com>...
> On 13 Oct 2004 23:07:38 -0700, itsbipul@.gmail.com (Bipul) wrote:
> Are you doing joins in your delete statements?
> Can you show your code?
> It is curious, since you own the locks on early deletes, but I'd like
> to try to figure out what SQLServer *thinks* it's doing!
> Can you break the transaction into several independent pieces - quick
> workaround, probably.
> J.|||Hi Bipul,
Is this problem resolved ? I was going thru the newsgroup to learn more
about deadlocks. Reading the thread and the response from Wei Xiao, I have a
feeling that he had the select statement with updlock in his transaction to
prevent sql server from allowing any sharing of those rows. so make your
delete work, you can try this:
begin tran
get upd lock on childTab
delete from childTab
delete from parentTab
commit tran
One thing that is confusing is that Wei had a select on the parentTab with
updlock, whereas your deadlock was due to contention on a child table. My
above suggestion is based on your assumption that sql server is not able to
establish the shared lock on the child table while checking the constraint.
Regards,
Mani.
"Bipul" wrote:
> Hi,
> This indeed is an intriguing problem. I don't have any joins in the
> Delete queries. These are just pure delete statements with 'where'
> clause on PK of the table from which I delete.
> But,yes as you can see from my first mail there is child parent
> relationship between the tables I'm deleting.
> I can not break the transaction into smaller pieces.
> What do you think will be the problem?
> What I think is that when SQL server is deleting from the parent table
> it searches the child tables for checking FK violations. For this it
> takes locks on the indexes of these child tables and then 2 pids doing
> the same get deadlocked on index resource.
> Am I thinking on right lines ? or there is something else?
> Awaiting responses...
> Regds,
> Bipul
>
>
> JXStern <JXSternChangeX2R@.gte.net> wrote in message news:<cd9in0h4qtg9g9n6
3buuhoh85qte4asjvh@.4ax.com>...
>
Subscribe to:
Posts (Atom)