RE: Are Deadlocks function of extents number ?
Forgot to mention the third reason -
has just hit it myself :-)
Lock occurs when table is extremely small
--------- EXAMPLE TESTED UNDER 9.21 --------
--SESSION 1:
create table tst_account (account_id SERIAL not null)
lock mode row;
create unique index xpk_tst_account on tst_account
( account_id );
set lock mode to wait 100;BEGIN WORK;
insert into tst_account (account_id) values (0); -- insert(1)
--SESSION 2:
set lock mode to wait 100;BEGIN WORK;
insert into tst_account (account_id) values (0); -- insert(2)
--SESSION 1:
delete from tst_account where account_id=1;
--SESSION 2:
delete from tst_account where account_id=2;
------ DEADLOCK DETECTED --------------------
Workaround: in both sessions do
delete --+ AVOID_FULL(tst_account)
from tst_account where account_id=1;
------------------------------------------
Alexey Sonkin
> -----Original Message-----
> From: Alexey Sonkin [mailto:alexeis@grandvirtual.com]
>
> Fred,
>
> Deadlocks usually have two major reasons:
> 1. Poorly written/designed application
> 2. Tables with high concurrent insert/update access
> are created with 'page' level locking.
>
> My advise is to verify the locking mode
> for those table, that cause deadlocks.
> To identify these tables, run
> select * from sysmaster:sysptprof order by deadlks desc>
> I can't imagine how deadlocks can relate to the number
> of extents in the table.
>
> ------------------------------------------
> Alexey Sonkin
>
>
> > -----Original Message-----
> > From: oxow@yahoo.com [mailto:oxow@yahoo.com]
> > Subject: Are Deadlocks function of extents number ?
> >
> > Hi,
> >
> > I have just a little question :
> >
> > I am wondering if the Deadlocks is linked to the number of extents ?
> >
> > I am asking this question because some developers of a third
> > application told me that the deadlock we have on the database and some
> > slowness problems were due to our number of extent on some specific
> > tables. Tables that they had designed.
> >
> > Btw, we have a 7.31.FC6 on Hp-ux11.
> >
> > So, could you help me to know if it is effectivly correct.
> > Thanks in advance.
> >
> > Best Regards
> > Fred
> sending to informix-list
sending to informix-list