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--
No comments:
Post a Comment