How to trouble shoot DEADLOCK ?

hi all, onstat -p as below :-Informix Dynamic Server Version 7.30.UC7A -- On-Line -- Up 20 days 19:32:45 -- 198920 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 82544 424721 2488561356 100.00 211640 397654 1252874 83.11 isamtot open start read write rewrite delete commit rollbk 54872319 710430 2999153 36090115 142725 286617 109083 340768 127 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 41863.15 3666.64 5577 11943 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 33078 1064 1437960508 129 0 179 11532 377263 ixda-RA idx-RA da-RA RA-pgsused lchwaits 1330 44 1260 2583 463851 No. of accumulate deadlock is 129. Error msg on the app. log file is Database Exception 2, sqlstate = IX000, sqlcode = -244, sqlerrd[1] = -143 finderr 244 -244 Could not do a physical-order read to fetch next row. The database server cannot read the disk page that contains a row of a table. Check the accompanying ISAM error code for more information. A hardware problem might exist, or the table or index file might have been corrupted. Unless the ISAM error code or an operating-system message points to another cause, run the bcheck or secheck utility to verify file integrity. How to I know which tables was involved in the deadlock situation ?? Thanks in advance. Lee __________________________________________________ Do You Yahoo!? Yahoo! Photos - Share your holiday photos online! http://photos.yahoo.com/

K.Hong Lee wrote in message <93f6dc$gc8$1@news.xmission.com>... > >Error msg on the app. log file is >Database Exception 2, sqlstate = IX000, sqlcode = >-244, sqlerrd[1] = -143 > >finderr 244 >-244 Could not do a physical-order read to fetch >next row. > <SNIP> > >How to I know which tables was involved in the >deadlock situation ?? > I just saw your message in passing and nobody has answered it. So if you haven't already fixed the problem... hope this helps: You can also do a finderr on the sqlerrd[1] error number - that's the "accompanying ISAM error" mentioned in the 244 description. Since the finderr 244 offers a few very scarey options, it's always worth a look at the meaning of the ISAM error code. The engine is built with several layers, and more than one lowlevel (ISAM) errors will result in one or more possible SQL error codes depending on the circumstances. In your case, the -143 error message proves that the problem is purely a programming issue. The last two sentences of the first paragraph offer the best advice: To prevent recurrence, review the design of the applications that use the same tables and execute concurrently. Various design strategies can minimize the probability of deadlock. I recently posted some discussion about this kind of problem. See messages with subjects and dates: 14 Jan 2001 Question about promotable locks 2 responses on the 15th Jan 2 responses on the 16th Jan 18 Jan 2001 Deadlock problem... 2 responses on the 18th Jan All your answers should be in those messages so it's pointless repeating the advice again. Feel free to ask further questions about any finer points.

Hi , Just a thought. If you are using Page level locking on tables might be worth while switching to Row level. If already using Row level then as suggested in previous mail check apps. Cheers Pankaj #In article <93f6dc$gc8$1@news.xmission.com>, "K.Hong Lee" <kwonghong@yahoo.com> wrote: > > hi all, > > onstat -p as below :-> Informix Dynamic Server Version 7.30.UC7A -- On-Line > -- Up 20 days 19:32:45 -- 198920 Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits > bufwrits %cached > 82544 424721 2488561356 100.00 211640 397654 > 1252874 83.11 > > isamtot open start read write rewrite > delete commit rollbk > 54872319 710430 2999153 36090115 142725 286617 > 109083 340768 127 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free > gp_curs > 0 0 0 0 0 0 > 0 > > ovlock ovuserthread ovbuff usercpu syscpu > numckpts flushes > 0 0 0 41863.15 3666.64 5577 > 11943 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits > compress seqscans > 33078 1064 1437960508 129 0 179 > 11532 377263 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 1330 44 1260 2583 463851 > > No. of accumulate deadlock is 129. > > Error msg on the app. log file is > Database Exception 2, sqlstate = IX000, sqlcode = > -244, sqlerrd[1] = -143 > > finderr 244 > -244 Could not do a physical-order read to fetch > next row. > > The database server cannot read the disk page that > contains a row of a > table. Check the accompanying ISAM error code for more > information. A > hardware problem might exist, or the table or index > file might have > been corrupted. Unless the ISAM error code or an > operating-system > message points to another cause, run the bcheck or > secheck utility to > verify file integrity. > > How to I know which tables was involved in the > deadlock situation ?? > > Thanks in advance. > Lee > > __________________________________________________ > Do You Yahoo!? > Yahoo! Photos - Share your holiday photos online! > http://photos.yahoo.com/ > Sent via Deja.com http://www.deja.com/

pankaj1000@my-deja.com wrote in message <94kg78$69i$1@nnrp1.deja.com>... > >Just a thought. If you are using Page level locking on tables might be >worth while switching to Row level. If already using Row level then as >suggested in previous mail check apps. > If he is currently using pagelevel locking (the default) and changes to rowlevel, then the number of occurrences of the problem may well reduce, but they won't go away, and the problem definitely needs to be 100% solved before a production system can be considered bug-free (at least in that area!)