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--

Deadlock in Dataflow

Hi all,

I have encountered a SQL 2005 deadlock issue while executing dataflow in a SSIS package. The deadlock happens when I have indexed two columns. If I don't have index, deadlock does not happen.

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

The above causes the rest of the dataflow execution to be terminated.

What my dataflow does is to extract data from 14 flat files and then insert the records into a single table (no primary key, but with two columns indexed).

Can anyone please advise how I can avoid deadlock with indexes in a table?

Thank you and much appreciated!

Before the data flow, in the control flow, I'd issue an Execute SQL task to disable the indexes on the table. Then after the data flow, I'd re-enable them. Should help with performance as well.|||

Hi Phil,

Thank you very much for your response.

Would you mind tell me how do I disable and re-enable indexes (the SQL command/syntax ?) in a SQL task?

Thanks again.

|||http://msdn2.microsoft.com/en-us/library/ms177406.aspx

To disable:
ALTER INDEX your_index_name ON your_table_name DISABLE

To reenable:
ALTER INDEX your_index_name ON your_table_name REBUILD|||

I tried the ALTER INDEX ... DISABLE command and also in SQL Server Management Studio manually right-clicked INDEX folder to "disable all" index, but they didn't seem to disable the index. Deadlock still occurs.

Did I miss something?

Thanks!

|||

I think I got this deadlock solved.

Not sure why disabling index does not work for me, but instead of disabling it, I drop the index before the data load. After finishing data loading, I re-create the index and rebuild it. It works fine that way.

Thanks Phil!