Re: Fwd: IIUG 2008 survey for new features

On 9 Oct, 00:37, Madison Pruet <mpru...@verizon.net> wrote: > DA Morgan wrote: > > Art Kagel wrote: > > >> Or perhaps you don't know that a deadlock is the result of a poorly > >> designed set of interacting applications accessing multiple resources > >> in undisciplined order causing two or more users to lock each other > >> out of required resources? > > > You should consider going into politics or selling used cars. > > > The issue is not whether I know what a deadlock is or the cause. > > The issue is that all other major RDBMS products already contain > > deadlock detection capabilities that are just now being considered > > for IDS as a "new" feature. > > Dan, > > You're showing a lot of ignorance with this one.  IDS/Online/RDS etc. > had deadlock detection pretty much since day one.  The fact that it's > error code (143) is a fairly low number is yet another indicator that it > has been in the product for quite a long time. > > > > > > > If you want to rant then instead of targeting the messenger why > > don't you ask yourself why this feature was listed as something > > "new" for IDS in the survey? It wasn't my survey ... it was yours.- Hide quoted text - > > - Show quoted text - OK so my session gets a 143 error but 1 - what locks did I hold at the time 2 which other session(s) were involved in the deadlock 3. what locks did they hold at the time and what sql statement were they executing. That info is the information that we do not have in IDS at the moment and no, you cannot guarantee you will be able to run onstat -k and onstat -g ses at the right times to see ALL this information.

david@smooth1.co.uk wrote: > On 9 Oct, 00:37, Madison Pruet <mpru...@verizon.net> wrote: >> DA Morgan wrote: >>> Art Kagel wrote: >>>> Or perhaps you don't know that a deadlock is the result of a poorly >>>> designed set of interacting applications accessing multiple resources >>>> in undisciplined order causing two or more users to lock each other >>>> out of required resources? >>> You should consider going into politics or selling used cars. >>> The issue is not whether I know what a deadlock is or the cause. >>> The issue is that all other major RDBMS products already contain >>> deadlock detection capabilities that are just now being considered >>> for IDS as a "new" feature. >> Dan, >> >> You're showing a lot of ignorance with this one. IDS/Online/RDS etc. >> had deadlock detection pretty much since day one. The fact that it's >> error code (143) is a fairly low number is yet another indicator that it >> has been in the product for quite a long time. >> >> >> >> >> >>> If you want to rant then instead of targeting the messenger why >>> don't you ask yourself why this feature was listed as something >>> "new" for IDS in the survey? It wasn't my survey ... it was yours.- Hide quoted text - >> - Show quoted text - > > OK so my session gets a 143 error but > > 1 - what locks did I hold at the time > 2 which other session(s) were involved in the deadlock > 3. what locks did they hold at the time and what sql statement were > they executing. > > That info is the information that we do not have in IDS at the moment > and no, you cannot guarantee you will be able to run onstat -k > and onstat -g ses at the right times to see ALL this information. Precisely my point. Whereas: SQL> SELECT ( 2 SELECT username 3 FROM gv$session 4 WHERE sid=a.sid) blocker, 5 a.sid, ' is blocking ', ( 6 SELECT username 7 FROM gv$session 8 WHERE sid=b.sid) blockee, 9 b.sid 10 FROM gv$lock a, gv$lock b 11 WHERE a.block = 1 12 AND b.request > 0 13 AND a.id1 = b.id1 14 AND a.id2 = b.id2; BLOCKER SID 'ISBLOCKING' BLOCKEE SID ---------- ---------- ------------ ---------- ---------- UWCLASS 125 is blocking UWCLASS 127 SQL> select session_id, lock_type, mode_held, mode_requested, blocking_others 2 from dba_locks 3 where session_id IN (125,127); SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED BLOCKING_OTHERS ---------- --------------- ---------- --------------- --------------- 127 AE Share None Not Blocking 125 AE Share None Not Blocking 127 Transaction None Exclusive Not Blocking 127 DML Row-X (SX) None Not Blocking 125 DML Row-X (SX) None Not Blocking 127 Transaction Exclusive None Not Blocking 125 Transaction Exclusive None Blocking 7 rows selected. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)

DA Morgan wrote: > david@smooth1.co.uk wrote: >> On 9 Oct, 00:37, Madison Pruet <mpru...@verizon.net> wrote: >>> DA Morgan wrote: >>>> Art Kagel wrote: >>>>> Or perhaps you don't know that a deadlock is the result of a poorly >>>>> designed set of interacting applications accessing multiple resources >>>>> in undisciplined order causing two or more users to lock each other >>>>> out of required resources? >>>> You should consider going into politics or selling used cars. >>>> The issue is not whether I know what a deadlock is or the cause. >>>> The issue is that all other major RDBMS products already contain >>>> deadlock detection capabilities that are just now being considered >>>> for IDS as a "new" feature. >>> Dan, >>> >>> You're showing a lot of ignorance with this one. IDS/Online/RDS etc. >>> had deadlock detection pretty much since day one. The fact that it's >>> error code (143) is a fairly low number is yet another indicator that it >>> has been in the product for quite a long time. >>> >>> >>> >>> >>> >>>> If you want to rant then instead of targeting the messenger why >>>> don't you ask yourself why this feature was listed as something >>>> "new" for IDS in the survey? It wasn't my survey ... it was yours.- >>>> Hide quoted text - >>> - Show quoted text - >> >> OK so my session gets a 143 error but >> >> 1 - what locks did I hold at the time >> 2 which other session(s) were involved in the deadlock >> 3. what locks did they hold at the time and what sql statement were >> they executing. >> >> That info is the information that we do not have in IDS at the moment >> and no, you cannot guarantee you will be able to run onstat -k >> and onstat -g ses at the right times to see ALL this information. > > Precisely my point. Whereas: > > SQL> SELECT ( > 2 SELECT username > 3 FROM gv$session > 4 WHERE sid=a.sid) blocker, > 5 a.sid, ' is blocking ', ( > 6 SELECT username > 7 FROM gv$session > 8 WHERE sid=b.sid) blockee, > 9 b.sid > 10 FROM gv$lock a, gv$lock b > 11 WHERE a.block = 1 > 12 AND b.request > 0 > 13 AND a.id1 = b.id1 > 14 AND a.id2 = b.id2; > > BLOCKER SID 'ISBLOCKING' BLOCKEE SID > ---------- ---------- ------------ ---------- ---------- > UWCLASS 125 is blocking UWCLASS 127 Dan, If a deadlock should occur, wouldn't the engine have resolved the deadlock? If that is the case, then wouldn't gv$lock no longer contain the evidence of the deadly embrace? I think that what you are describing is not deadly embrace but simply locked resources in which one user is blocking some other user. If that is the case, then Informix has had the ability to display that type of information since (v5.0) 1990 at least. I know that's when I had scripts which would us tbstat to see who was blocking whom and what they were blocking on. If you want to use SQL to gather this, then you can use sysmaster in much the same way. Use syslocks or syslocktab and join with syssession in much the same way. All of this has been doable since the mid-1990s. What we are talking about doing is keeping information about a deadly embrace, not of a locked resource. M.P. > > SQL> select session_id, lock_type, mode_held, mode_requested, > blocking_others > 2 from dba_locks > 3 where session_id IN (125,127); > > SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED BLOCKING_OTHERS > ---------- --------------- ---------- --------------- --------------- > 127 AE Share None Not Blocking > 125 AE Share None Not Blocking > 127 Transaction None Exclusive Not Blocking > 127 DML Row-X (SX) None Not Blocking > 125 DML Row-X (SX) None Not Blocking > 127 Transaction Exclusive None Not Blocking > 125 Transaction Exclusive None Blocking > > 7 rows selected.

In article <1223956769.14352@bubbleator.drizzle.com>, DA Morgan says... >> That info is the information that we do not have in IDS at the moment >> and no, you cannot guarantee you will be able to run onstat -k >> and onstat -g ses at the right times to see ALL this information. > >Precisely my point. Whereas: the sql u wrote below can be written in informix also using syssessions and syslocks table of sysmaster. But the question is, if deadlock is detected and one transaction rolled back, how will this SQL help. For this SQL to be of any use, you should run it when the deadlock is happening. > >SQL> SELECT ( > 2 SELECT username > 3 FROM gv$session > 4 WHERE sid=a.sid) blocker, > 5 a.sid, ' is blocking ', ( > 6 SELECT username > 7 FROM gv$session > 8 WHERE sid=b.sid) blockee, > 9 b.sid > 10 FROM gv$lock a, gv$lock b > 11 WHERE a.block = 1 > 12 AND b.request > 0 > 13 AND a.id1 = b.id1 > 14 AND a.id2 = b.id2; > >BLOCKER SID 'ISBLOCKING' BLOCKEE SID >---------- ---------- ------------ ---------- ---------- >UWCLASS 125 is blocking UWCLASS 127 > >SQL> select session_id, lock_type, mode_held, mode_requested, >blocking_others > 2 from dba_locks > 3 where session_id IN (125,127); > >SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED BLOCKING_OTHERS >---------- --------------- ---------- --------------- --------------- > 127 AE Share None Not Blocking > 125 AE Share None Not Blocking > 127 Transaction None Exclusive Not Blocking > 127 DML Row-X (SX) None Not Blocking > 125 DML Row-X (SX) None Not Blocking > 127 Transaction Exclusive None Not Blocking > 125 Transaction Exclusive None Blocking > >7 rows selected.

Are you implying that Oracle will not resolve the deadlock, and will wait for you to run that SQL? Besides, the most important stuff to solve a deadlock it to look at the code, not the locks... As others told you, getting this info from Informix is trivial. Getting it before the system solves the deadlock is not, and that's why some people thinks It's a useful feature. I don't. I mean... it's useful, but I wouldn't put it on the top of my list of course. Regards. On Tue, Oct 14, 2008 at 4:59 AM, DA Morgan <damorgan@psoug.org> wrote: > david@smooth1.co.uk wrote: >> On 9 Oct, 00:37, Madison Pruet <mpru...@verizon.net> wrote: >>> DA Morgan wrote: >>>> Art Kagel wrote: >>>>> Or perhaps you don't know that a deadlock is the result of a poorly >>>>> designed set of interacting applications accessing multiple resources >>>>> in undisciplined order causing two or more users to lock each other >>>>> out of required resources? >>>> You should consider going into politics or selling used cars. >>>> The issue is not whether I know what a deadlock is or the cause. >>>> The issue is that all other major RDBMS products already contain >>>> deadlock detection capabilities that are just now being considered >>>> for IDS as a "new" feature. >>> Dan, >>> >>> You're showing a lot of ignorance with this one. IDS/Online/RDS etc. >>> had deadlock detection pretty much since day one. The fact that it's >>> error code (143) is a fairly low number is yet another indicator that it >>> has been in the product for quite a long time. >>> >>> >>> >>> >>> >>>> If you want to rant then instead of targeting the messenger why >>>> don't you ask yourself why this feature was listed as something >>>> "new" for IDS in the survey? It wasn't my survey ... it was yours.- Hide quoted text - >>> - Show quoted text - >> >> OK so my session gets a 143 error but >> >> 1 - what locks did I hold at the time >> 2 which other session(s) were involved in the deadlock >> 3. what locks did they hold at the time and what sql statement were >> they executing. >> >> That info is the information that we do not have in IDS at the moment >> and no, you cannot guarantee you will be able to run onstat -k >> and onstat -g ses at the right times to see ALL this information. > > Precisely my point. Whereas: > > SQL> SELECT ( > 2 SELECT username > 3 FROM gv$session > 4 WHERE sid=a.sid) blocker, > 5 a.sid, ' is blocking ', ( > 6 SELECT username > 7 FROM gv$session > 8 WHERE sid=b.sid) blockee, > 9 b.sid > 10 FROM gv$lock a, gv$lock b > 11 WHERE a.block = 1 > 12 AND b.request > 0 > 13 AND a.id1 = b.id1 > 14 AND a.id2 = b.id2; > > BLOCKER SID 'ISBLOCKING' BLOCKEE SID > ---------- ---------- ------------ ---------- ---------- > UWCLASS 125 is blocking UWCLASS 127 > > SQL> select session_id, lock_type, mode_held, mode_requested, > blocking_others > 2 from dba_locks > 3 where session_id IN (125,127); > > SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED BLOCKING_OTHERS > ---------- --------------- ---------- --------------- --------------- > 127 AE Share None Not Blocking > 125 AE Share None Not Blocking > 127 Transaction None Exclusive Not Blocking > 127 DML Row-X (SX) None Not Blocking > 125 DML Row-X (SX) None Not Blocking > 127 Transaction Exclusive None Not Blocking > 125 Transaction Exclusive None Blocking > > 7 rows selected. > -- > Daniel A. Morgan > University of Washington > damorgan@x.washington.edu (replace x with u to respond) > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...