IDS Forum

Re: deadlock question

Posted By: Fernando Nunes
Date: Tuesday, 6 December 2011, at 7:04 p.m.

In Response To: Re: deadlock question (Art Kagel)

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
> 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
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba2121bdc9198e04b375217c
>
>
>
> *******************************************************************************
> 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...

--0016e648a630012f0804b3754d2e

Messages In This Thread

• deadlock question
  JACK PAPA -- Wednesday, 11 May 2011, at 2:09 a.m.
  • Re: deadlock question
    rroussey@comcast.net -- Wednesday, 11 May 2011, at 8:55 a.m.
  • Re: deadlock question
    DAVE GRIFFEN -- Thursday, 12 May 2011, at 5:52 p.m.
    • Re: deadlock question
      JACK PAPA -- Monday, 16 May 2011, at 7:24 a.m.
      • Re: deadlock question
        JACK PAPA -- Tuesday, 6 December 2011, at 7:36 a.m.
        • Re: deadlock question
          Art Kagel -- Tuesday, 6 December 2011, at 8:18 a.m.
          • Re: deadlock question
            JACK PAPA -- Tuesday, 6 December 2011, at 8:58 a.m.
          • Re: deadlock question
            Bill Hamilton -- Tuesday, 6 December 2011, at 6:03 p.m.
            • Re: deadlock question
              Art Kagel -- Tuesday, 6 December 2011, at 6:09 p.m.
              • Re: deadlock question
                Fernando Nunes -- Tuesday, 6 December 2011, at 6:47 p.m.
                • Re: deadlock question
                  Art Kagel -- Tuesday, 6 December 2011, at 6:52 p.m.
                  • Re: deadlock question
                    Fernando Nunes -- Tuesday, 6 December 2011, at 7:04 p.m.
        • Re: deadlock question
          DAVE GRIFFEN -- Tuesday, 6 December 2011, at 11:15 a.m.
          • RE: deadlock question
            Dan Mueller -- Tuesday, 6 December 2011, at 11:18 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.