Re: deadlock question

Hi All, i've seen some deadlocks on our production DB. (IDS 94UC6, AIX 5). we have a select statement that detect if a deadlock is happening (sysptprof). As i know, deadlock happens when two or more concurrent sessions is trying to access the same table at the same time.. online.log doesn't show any error message with regards to deadlock. My question is how to eliminate the deadlocks, if not eliminate, minimize the occurence. My users are complaining that there are some transactions missing from their batch jobs...is deadlock related on this issue? Any help?? please?

Almost, deadlocks happen when two or more sessions are trying to acquire locks on two resources (say two particular rows in the same table or a particular single row in two tables) and each is successful in acquiring one resource of the two. Then these sessions are deadlocked because each wants a lock on the row that the other is already holding and will not release. Example: Two applications, both running with LOCK MODE WAIT, are each tasked with updating an order, say one is an order entry system that needs to update the quantity in an item line and update the total in the order header record because the customer called to increase the quantity of one item in the order. The other app is the warehouse picker app which is updating the order as items are picked and packed. The picker app needs to update the quantity left on the item record and the picked status in the order header record. The OE app locks the order header record and tried to lock the item record. The picker app locks the item record and tries to lock the order header record. Neither can get the second lock that it needs because the other app is holding that lock and is waiting on the second lock before performing the required updates and releasing its locks. That's a deadlock. Now one or both of these apps will get a deadlock error (or if the two tables are in different servers a deadlock timeout error). If it responds by attempting the second lock again, while continuing to hold the first lock, then we have worse than a deadlock, we have a deadly embrace. Poor solution: If you get a lock error, deadlock error, or lock wait timeout, release all locks and start over. Good Solution: All applications accessing sets of related tables MUST access the tables in the same order. In the scenario above, if both apps had attempted to lock the order header record first then only one app would have succeeded while the other blocked on the first lock (assuming LOCK MODE WAIT). Then whichever app succeeds in acquiring the lock on the order header record will be able to acquire the second lock on the order item table, complete its work, commit releasing all of the locks it holds, the other app can continue, and all is well. So, deadlocks are almost exclusively an application design and discipline issue. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 6, 2011 at 7:36 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All, > > i've seen some deadlocks on our production DB. (IDS 94UC6, AIX 5). we have > a > select statement that detect if a deadlock is happening (sysptprof). > > As i know, deadlock happens when two or more concurrent sessions is trying > to > access the same table at the same time.. > > online.log doesn't show any error message with regards to deadlock. > > My question is how to eliminate the deadlocks, if not eliminate, minimize > the > occurence. My users are complaining that there are some transactions > missing > from their batch jobs...is deadlock related on this issue? > > Any help?? please? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba2121bd6e2ee304b36c466d

Hi Art, well explained Art. I appreciate your effort and time of explaining the whole scenario. It;s an application design issue. Kudos to you! Have a good day!!

JACK PAPA Wrote: ================================================================================ My question is how to eliminate the deadlocks, if not eliminate, minimize the occurence. My users are complaining that there are some transactions missing from their batch jobs...is deadlock related on this issue? Any help?? please? ================================================================================ My Response: Review systables.locklevel of tables that are getting deadlocks. Are they set to page or row level locking? If they are set to page, you should strongly consider changing them to row. Row level can consume more locks, but it will also reduce the possibility of a deadlock. Dave Griffen

Start with onstat -p. It will tell you if there have been deadlocks. Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVE GRIFFEN Sent: Tuesday, December 06, 2011 11:16 AM To: ids@iiug.org Subject: Re: deadlock question [25563] JACK PAPA Wrote: ================================================================================ My question is how to eliminate the deadlocks, if not eliminate, minimize the occurence. My users are complaining that there are some transactions missing from their batch jobs...is deadlock related on this issue? Any help?? please? ================================================================================ My Response: Review systables.locklevel of tables that are getting deadlocks. Are they set to page or row level locking? If they are set to page, you should strongly consider changing them to row. Row level can consume more locks, but it will also reduce the possibility of a deadlock. Dave Griffen ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

What if dirty read was set? Chaos? -----Original Message----- From: Art Kagel Sent: Tuesday, December 06, 2011 7:18 AM To: ids@iiug.org Subject: Re: deadlock question [25561] Almost, deadlocks happen when two or more sessions are trying to acquire locks on two resources (say two particular rows in the same table or a particular single row in two tables) and each is successful in acquiring one resource of the two. Then these sessions are deadlocked because each wants a lock on the row that the other is already holding and will not release. Example: Two applications, both running with LOCK MODE WAIT, are each tasked with updating an order, say one is an order entry system that needs to update the quantity in an item line and update the total in the order header record because the customer called to increase the quantity of one item in the order. The other app is the warehouse picker app which is updating the order as items are picked and packed. The picker app needs to update the quantity left on the item record and the picked status in the order header record. The OE app locks the order header record and tried to lock the item record. The picker app locks the item record and tries to lock the order header record. Neither can get the second lock that it needs because the other app is holding that lock and is waiting on the second lock before performing the required updates and releasing its locks. That's a deadlock. Now one or both of these apps will get a deadlock error (or if the two tables are in different servers a deadlock timeout error). If it responds by attempting the second lock again, while continuing to hold the first lock, then we have worse than a deadlock, we have a deadly embrace. Poor solution: If you get a lock error, deadlock error, or lock wait timeout, release all locks and start over. Good Solution: All applications accessing sets of related tables MUST access the tables in the same order. In the scenario above, if both apps had attempted to lock the order header record first then only one app would have succeeded while the other blocked on the first lock (assuming LOCK MODE WAIT). Then whichever app succeeds in acquiring the lock on the order header record will be able to acquire the second lock on the order item table, complete its work, commit releasing all of the locks it holds, the other app can continue, and all is well. So, deadlocks are almost exclusively an application design and discipline issue. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 6, 2011 at 7:36 AM, JACK PAPA <informix2009@gmail.com> wrote: > Hi All, > > i've seen some deadlocks on our production DB. (IDS 94UC6, AIX 5). we have > a > select statement that detect if a deadlock is happening (sysptprof). > > As i know, deadlock happens when two or more concurrent sessions is trying > to > access the same table at the same time.. > > online.log doesn't show any error message with regards to deadlock. > > My question is how to eliminate the deadlocks, if not eliminate, minimize > the > occurence. My users are complaining that there are some transactions > missing > from their batch jobs...is deadlock related on this issue? > > Any help?? please? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba2121bd6e2ee304b36c466d ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Yup. Chaos. There should not be any deadlocks under dirty read. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 6, 2011 at 6:03 PM, Bill Hamilton <garage_dba@hotmail.com>wrote: > What if dirty read was set? Chaos? > > -----Original Message----- > From: Art Kagel > Sent: Tuesday, December 06, 2011 7:18 AM > To: ids@iiug.org > Subject: Re: deadlock question [25561] > > Almost, deadlocks happen when two or more sessions are trying to acquire > locks on two resources (say two particular rows in the same table or a > particular single row in two tables) and each is successful in acquiring > one resource of the two. Then these sessions are deadlocked because each > wants a lock on the row that the other is already holding and will not > release. Example: > > Two applications, both running with LOCK MODE WAIT, are each tasked with > updating an order, say one is an order entry system that needs to update > the quantity in an item line and update the total in the order header > record because the customer called to increase the quantity of one item in > the order. The other app is the warehouse picker app which is updating the > order as items are picked and packed. The picker app needs to update the > quantity left on the item record and the picked status in the order header > record. The OE app locks the order header record and tried to lock the > item record. The picker app locks the item record and tries to lock the > order header record. Neither can get the second lock that it needs because > the other app is holding that lock and is waiting on the second lock before > performing the required updates and releasing its locks. That's a > deadlock. Now one or both of these apps will get a deadlock error (or if > the two tables are in different servers a deadlock timeout error). If it > responds by attempting the second lock again, while continuing to hold the > first lock, then we have worse than a deadlock, we have a deadly embrace. > > Poor solution: If you get a lock error, deadlock error, or lock wait > timeout, release all locks and start over. > > Good Solution: All applications accessing sets of related tables MUST > access the tables in the same order. In the scenario above, if both apps > had attempted to lock the order header record first then only one app would > have succeeded while the other blocked on the first lock (assuming LOCK > MODE WAIT). Then whichever app succeeds in acquiring the lock on the order > header record will be able to acquire the second lock on the order item > table, complete its work, commit releasing all of the locks it holds, the > other app can continue, and all is well. > > So, deadlocks are almost exclusively an application design and discipline > issue. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Tue, Dec 6, 2011 at 7:36 AM, JACK PAPA <informix2009@gmail.com> wrote: > > > Hi All, > > > > i've seen some deadlocks on our production DB. (IDS 94UC6, AIX 5). we > have > > a > > select statement that detect if a deadlock is happening (sysptprof). > > > > As i know, deadlock happens when two or more concurrent sessions is > trying > > to > > access the same table at the same time.. > > > > online.log doesn't show any error message with regards to deadlock. > > > > My question is how to eliminate the deadlocks, if not eliminate, minimize > > the > > occurence. My users are complaining that there are some transactions > > missing > > from their batch jobs...is deadlock related on this issue? > > > > Any help?? please? > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba2121bd6e2ee304b36c466d > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf303bff66eb6b4804b37485d8

I didn't follow this thread... But deadlocks can happen in dirty read. Just imagine two sessions that try to update customer_num 101 and 102 running concurrently on stores database in dirty read: T1: set isolation to dirty read; set lock mode to wait; update customer set lname = lname where customer_num = 101;--- pause.... T2: set isolation to dirty read; set lock mode to wait; update customer set lname = lname where customer_num = 102;--- pause.... T1: update customer set lname = lname where customer_num = 102;-- hangs waiting.... T2: update customer set lname = lname where customer_num = 101;-- BANG!: 243: Could not position within a table (fnunes.customer). 143: ISAM error: deadlock detected Regards. On Tue, Dec 6, 2011 at 11:09 PM, Art Kagel <art.kagel@gmail.com> wrote: > Yup. Chaos. There should not be any deadlocks under dirty read. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Tue, Dec 6, 2011 at 6:03 PM, Bill Hamilton <garage_dba@hotmail.com > >wrote: > > > What if dirty read was set? Chaos? > > > > -----Original Message----- > > From: Art Kagel > > Sent: Tuesday, December 06, 2011 7:18 AM > > To: ids@iiug.org > > Subject: Re: deadlock question [25561] > > > > Almost, deadlocks happen when two or more sessions are trying to acquire > > locks on two resources (say two particular rows in the same table or a > > particular single row in two tables) and each is successful in acquiring > > one resource of the two. Then these sessions are deadlocked because each > > wants a lock on the row that the other is already holding and will not > > release. Example: > > > > Two applications, both running with LOCK MODE WAIT, are each tasked with > > updating an order, say one is an order entry system that needs to update > > the quantity in an item line and update the total in the order header > > record because the customer called to increase the quantity of one item > in > > the order. The other app is the warehouse picker app which is updating > the > > order as items are picked and packed. The picker app needs to update the > > quantity left on the item record and the picked status in the order > header > > record. The OE app locks the order header record and tried to lock the > > item record. The picker app locks the item record and tries to lock the > > order header record. Neither can get the second lock that it needs > because > > the other app is holding that lock and is waiting on the second lock > before > > performing the required updates and releasing its locks. That's a > > deadlock. Now one or both of these apps will get a deadlock error (or if > > the two tables are in different servers a deadlock timeout error). If it > > responds by attempting the second lock again, while continuing to hold > the > > first lock, then we have worse than a deadlock, we have a deadly embrace. > > > > Poor solution: If you get a lock error, deadlock error, or lock wait > > timeout, release all locks and start over. > > > > Good Solution: All applications accessing sets of related tables MUST > > access the tables in the same order. In the scenario above, if both apps > > had attempted to lock the order header record first then only one app > would > > have succeeded while the other blocked on the first lock (assuming LOCK > > MODE WAIT). Then whichever app succeeds in acquiring the lock on the > order > > header record will be able to acquire the second lock on the order item > > table, complete its work, commit releasing all of the locks it holds, the > > other app can continue, and all is well. > > > > So, deadlocks are almost exclusively an application design and discipline > > issue. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > other organization with which I am associated either explicitly, > > implicitly, or by inference. Neither do those opinions reflect those of > > other individuals affiliated with any entity with which I am affiliated > nor > > those of the entities themselves. > > > > On Tue, Dec 6, 2011 at 7:36 AM, JACK PAPA <informix2009@gmail.com> > wrote: > > > > > Hi All, > > > > > > i've seen some deadlocks on our production DB. (IDS 94UC6, AIX 5). we > > have > > > a > > > select statement that detect if a deadlock is happening (sysptprof). > > > > > > As i know, deadlock happens when two or more concurrent sessions is > > trying > > > to > > > access the same table at the same time.. > > > > > > online.log doesn't show any error message with regards to deadlock. > > > > > > My question is how to eliminate the deadlocks, if not eliminate, > minimize > > > the > > > occurence. My users are complaining that there are some transactions > > > missing > > > from their batch jobs...is deadlock related on this issue? > > > > > > Any help?? please? > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --90e6ba2121bd6e2ee304b36c466d > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --20cf303bff66eb6b4804b37485d8 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf303344034ce94f04b3750e27

You're right, Fernando, I was thinking about an unlogged database rather than dirty read in a logged database. Locks should be so short lived in an unlogged database with no transactions that deadlocks shouldn't happen. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 6, 2011 at 6:47 PM, Fernando Nunes <domusonline@gmail.com>wrote: > I didn't follow this thread... But deadlocks can happen in dirty read. > Just imagine two sessions that try to update customer_num 101 and 102 > running concurrently on stores database in dirty read: > > T1: > > set isolation to dirty read; > set lock mode to wait; > update customer set lname = lname where customer_num = 101;> --- pause.... > > T2: > set isolation to dirty read; > set lock mode to wait; > update customer set lname = lname where customer_num = 102;> --- pause.... > > T1: > > update customer set lname = lname where customer_num = 102;> -- hangs waiting.... > > T2: > update customer set lname = lname where customer_num = 101;> -- BANG!: > > 243: Could not position within a table (fnunes.customer).> > 143: ISAM error: deadlock detected> > Regards. > > On Tue, Dec 6, 2011 at 11:09 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Yup. Chaos. There should not be any deadlocks under dirty read. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > other organization with which I am associated either explicitly, > > implicitly, or by inference. Neither do those opinions reflect those of > > other individuals affiliated with any entity with which I am affiliated > nor > > those of the entities themselves. > > > > On Tue, Dec 6, 2011 at 6:03 PM, Bill Hamilton <garage_dba@hotmail.com > > >wrote: > > > > > What if dirty read was set? Chaos? > > > > > > -----Original Message----- > > > From: Art Kagel > > > Sent: Tuesday, December 06, 2011 7:18 AM > > > To: ids@iiug.org > > > Subject: Re: deadlock question [25561] > > > > > > Almost, deadlocks happen when two or more sessions are trying to > acquire > > > locks on two resources (say two particular rows in the same table or a > > > particular single row in two tables) and each is successful in > acquiring > > > one resource of the two. Then these sessions are deadlocked because > each > > > wants a lock on the row that the other is already holding and will not > > > release. Example: > > > > > > Two applications, both running with LOCK MODE WAIT, are each tasked > with > > > updating an order, say one is an order entry system that needs to > update > > > the quantity in an item line and update the total in the order header > > > record because the customer called to increase the quantity of one item > > in > > > the order. The other app is the warehouse picker app which is updating > > the > > > order as items are picked and packed. The picker app needs to update > the > > > quantity left on the item record and the picked status in the order > > header > > > record. The OE app locks the order header record and tried to lock the > > > item record. The picker app locks the item record and tries to lock the > > > order header record. Neither can get the second lock that it needs > > because > > > the other app is holding that lock and is waiting on the second lock > > before > > > performing the required updates and releasing its locks. That's a > > > deadlock. Now one or both of these apps will get a deadlock error (or > if > > > the two tables are in different servers a deadlock timeout error). If > it > > > responds by attempting the second lock again, while continuing to hold > > the > > > first lock, then we have worse than a deadlock, we have a deadly > embrace. > > > > > > Poor solution: If you get a lock error, deadlock error, or lock wait > > > timeout, release all locks and start over. > > > > > > Good Solution: All applications accessing sets of related tables MUST > > > access the tables in the same order. In the scenario above, if both > apps > > > had attempted to lock the order header record first then only one app > > would > > > have succeeded while the other blocked on the first lock (assuming LOCK > > > MODE WAIT). Then whichever app succeeds in acquiring the lock on the > > order > > > header record will be able to acquire the second lock on the order item > > > table, complete its work, commit releasing all of the locks it holds, > the > > > other app can continue, and all is well. > > > > > > So, deadlocks are almost exclusively an application design and > discipline > > > issue. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor > any > > > other organization with which I am associated either explicitly, > > > implicitly, or by inference. Neither do those opinions reflect those of > > > other individuals affiliated with any entity with which I am affiliated > > nor > > > those of the entities themselves. > > > > > > On Tue, Dec 6, 2011 at 7:36 AM, JACK PAPA <informix2009@gmail.com> > > wrote: > > > > > > > Hi All, > > > > > > > > i've seen some deadlocks on our production DB. (IDS 94UC6, AIX 5). we > > > have > > > > a > > > > select statement that detect if a deadlock is happening (sysptprof). > > > > > > > > As i know, deadlock happens when two or more concurrent sessions is > > > trying > > > > to > > > > access the same table at the same time.. > > > > > > > > online.log doesn't show any error message with regards to deadlock. > > > > > > > > My question is how to eliminate the deadlocks, if not eliminate, > > minimize > > > > the > > > > occurence. My users are complaining that there are some transactions > > > > missing > > > > from their batch jobs...is deadlock related on this issue? > > > > > > > > Any help?? please? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --90e6ba2121bd6e2ee304b36c466d > > > > > > > > >@@NL

Yep On Tue, Dec 6, 2011 at 11:52 PM, Art Kagel <art.kagel@gmail.com> wrote: > You're right, Fernando, I was thinking about an unlogged database rather > than dirty read in a logged database. Locks should be so short lived in an > unlogged database with no transactions that deadlocks shouldn't happen. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Tue, Dec 6, 2011 at 6:47 PM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > I didn't follow this thread... But deadlocks can happen in dirty read. > > Just imagine two sessions that try to update customer_num 101 and 102 > > running concurrently on stores database in dirty read: > > > > T1: > > > > set isolation to dirty read; > > set lock mode to wait; > > update customer set lname = lname where customer_num = 101;> > --- pause.... > > > > T2: > > set isolation to dirty read; > > set lock mode to wait; > > update customer set lname = lname where customer_num = 102;> > --- pause.... > > > > T1: > > > > update customer set lname = lname where customer_num = 102;> > -- hangs waiting.... > > > > T2: > > update customer set lname = lname where customer_num = 101;> > -- BANG!: > > > > 243: Could not position within a table (fnunes.customer).> > > > 143: ISAM error: deadlock detected> > > > Regards. > > > > On Tue, Dec 6, 2011 at 11:09 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > Yup. Chaos. There should not be any deadlocks under dirty read. > > > > > > Art > > > > > > Art S. Kagel > > > Advanced DataTools (www.advancedatatools.com) > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor > any > > > other organization with which I am associated either explicitly, > > > implicitly, or by inference. Neither do those opinions reflect those of > > > other individuals affiliated with any entity with which I am affiliated > > nor > > > those of the entities themselves. > > > > > > On Tue, Dec 6, 2011 at 6:03 PM, Bill Hamilton <garage_dba@hotmail.com > > > >wrote: > > > > > > > What if dirty read was set? Chaos? > > > > > > > > -----Original Message----- > > > > From: Art Kagel > > > > Sent: Tuesday, December 06, 2011 7:18 AM > > > > To: ids@iiug.org > > > > Subject: Re: deadlock question [25561] > > > > > > > > Almost, deadlocks happen when two or more sessions are trying to > > acquire > > > > locks on two resources (say two particular rows in the same table or > a > > > > particular single row in two tables) and each is successful in > > acquiring > > > > one resource of the two. Then these sessions are deadlocked because > > each > > > > wants a lock on the row that the other is already holding and will > not > > > > release. Example: > > > > > > > > Two applications, both running with LOCK MODE WAIT, are each tasked > > with > > > > updating an order, say one is an order entry system that needs to > > update > > > > the quantity in an item line and update the total in the order header > > > > record because the customer called to increase the quantity of one > item > > > in > > > > the order. The other app is the warehouse picker app which is > updating > > > the > > > > order as items are picked and packed. The picker app needs to update > > the > > > > quantity left on the item record and the picked status in the order > > > header > > > > record. The OE app locks the order header record and tried to lock > the > > > > item record. The picker app locks the item record and tries to lock > the > > > > order header record. Neither can get the second lock that it needs > > > because > > > > the other app is holding that lock and is waiting on the second lock > > > before > > > > performing the required updates and releasing its locks. That's a > > > > deadlock. Now one or both of these apps will get a deadlock error (or > > if > > > > the two tables are in different servers a deadlock timeout error). If > > it > > > > responds by attempting the second lock again, while continuing to > hold > > > the > > > > first lock, then we have worse than a deadlock, we have a deadly > > embrace. > > > > > > > > Poor solution: If you get a lock error, deadlock error, or lock wait > > > > timeout, release all locks and start over. > > > > > > > > Good Solution: All applications accessing sets of related tables MUST > > > > access the tables in the same order. In the scenario above, if both > > apps > > > > had attempted to lock the order header record first then only one app > > > would > > > > have succeeded while the other blocked on the first lock (assuming > LOCK > > > > MODE WAIT). Then whichever app succeeds in acquiring the lock on the > > > order > > > > header record will be able to acquire the second lock on the order > item > > > > table, complete its work, commit releasing all of the locks it holds, > > the > > > > other app can continue, and all is well. > > > > > > > > So, deadlocks are almost exclusively an application design and > > discipline > > > > issue. > > > > > > > > Art > > > > > > > > Art S. Kagel > > > > Advanced DataTools (www.advancedatatools.com) > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions > > > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor > > any > > > > other organization with which I am associated either explicitly, > > > > implicitly, or by inference. Neither do those opinions reflect those > of > > > > other individuals affiliated with any entity with which I am > affiliated > > > nor > > > > those of the entities themselves. > > > > > > > > On Tue, Dec 6, 2011 at 7:36 AM, JACK PAPA <informix2009@gmail.com> > > > wrote: > > > > > > > > > Hi All, > > > > > > > > > > i've seen some deadlocks on our production DB. (IDS 94UC6, AIX 5). > we > > > > have > > > > > a > > > > > select statement that detect if a deadlock is happening > (sysptprof). > > > > > > > > > > As i know, deadlock happens when two or more concurrent sessions is > > > > trying > > > > > to > > > > > access the same table at the same time.. > > > > > > > > > > online.log doesn't show any error message with regards to deadlock. > > > > > > > > > > My question is how to eliminate the deadlocks, if not eliminate, > > > minimize > > > > > the > > > > > occurence. My users are complaining that there are some@@