Re: Locking issues

Posted in 2004

Art S. Kagel — Wed, 25 Aug 2004 11:16:52 -0400 — source: Usenet: comp.databases.informix

On Tue, 24 Aug 2004 15:31:43 -0400, Brian Minnick wrote: SET LOCK MODE TO WAIT 10; That's the solution. Art S. Kagel > > We had to move from an old SE database to IDS 9.4 and ran into some locking > issues with one of our applications. In SE, we had our non-logged database > communicating to another logged one. Since this isn't allowed in IDS, we're > tweaking the application to work around it. However, we're running into > some locking problems. Here's a very quick test scenario: > > SCHEMA: > > create table t1 > (order_num integer,> > line_num integer) > extent size 16 next size 16 lock mode row; > > create unique index t11 on t1 (order_num,line_Num);> > LOAD A FEW ROWS: > insert into t1(order_num,line_num) values (203,2);> > insert into t1(order_num,line_num) values (203,3);> > insert into t1(order_num,line_num) values (205,1);> > insert into t1(order_num,line_num) values (208,1);> > insert into t1(order_num,line_num) values (208,2);> > insert into t1(order_num,line_num) values (208,3);> > > update statistics for table t1;> > select order_num,count(*) from t1 group by order_num;> > USER PROCESS 1: > > -- delete all rows for order # 203 > begin work; > set isolation to dirty read; > set lock mode to not wait; > delete from t1 where order_num = 203;> # 2 rows deleted > > USER PROCESS 2: > > -- delete all rows for order # 208 > begin work; > set isolation to dirty read; > set lock mode to not wait; > delete from t1 where order_num = 208> > -- and we get this: > # 243: Could not position within a table (informix.t1). 107: ISAM error: > record is locked. > > > -- a query from sysmaster shows an intent-exclusive lock on the table level > (first row of output): > > dbsname tabname rowidr keynum type > sesid ownername > co_01 t1 0 0 IX > 8884 USER1 > co_01 t1 257 0 X > 8884 USER1 > co_01 t11 257 1 X > 8884 USER1 > co_01 t1 258 0 X > 8884 USER1 > co_01 t11 258 1 X > 8884 USER1 > > ---- > > We also tried different variations of this withOUT the index but we're > always prevented from deleting rows from the table while user 1's > transaction is open, even if they are different rows. I thought with an > isolation level of dirty read it would just blow by the rows for order_num > 203 and delete the rows for user 208. What are we missing here and what is > a good workaround?? > > Thank you, > Brian Minnick > > Brian Minnick > DBA / Systems Developer > Belle Tire Distributors Inc > (313) 203-2192 > bminnick@belletire.com > > ------_=_NextPart_001_01C48A10.FCD7AFC0 Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3Diso-8859-1"> > <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = > 5.5.2654.45"> > <TITLE>Locking issues</TITLE> > </HEAD> > <BODY> > > We had to move from an old SE database to IDS 9.4 = > and > ran into some locking issues with one of our > applications.  In SE, we had our non-logged = > database > communicating to another logged one.  Since = this > isn't > allowed in IDS, we're tweaking the application to = > work > around it.  However, we're running into some = > locking > problems.  Here's a very quick test = > scenario: > > > SCHEMA: > > > create table t1  SIZE=3D2>(order_num = > integer,          &nbs= > p;           &nbs= > p;            = > > > > line_num integer)  extent > size 16 next size 16 lock mode = > row;           = > > > create unique index t11 on t1 = > (order_num,line_Num); > > > LOAD A FEW ROWS: insert into > t1(order_num,line_num) values = (203,2);    SIZE=3D2>  > insert into t1(order_num,line_num) values = > (203,3);    >   > insert into t1(order_num,line_num) values = > (205,1);    >   > insert into t1(order_num,line_num) values = > (208,1);    >   > insert into t1(order_num,line_num) values = > (208,2);    >   > insert into t1(order_num,line_num) values = > (208,3);    >   > > > update statistics for table = > t1;           &nb= > sp;          = > SIZE=3D2>          &nb= > sp;           &nb= > sp;           &nb= > sp;           &nb= > sp;      select > order_num,count(*) from t1 group by = order_num; > > USER PROCESS 1: > > -- delete all rows for order # 203 SIZE=3D2>begin = > work;           &= > nbsp;           &= > nbsp;     > set isolation to dirty = > read;            = > > set lock mode to not = > wait;           &= > nbsp;  > delete from t1 where order_num =3D 203;   = > > # 2 rows deleted > > USER PROCESS 2: > > -- delete all rows for order # 208 SIZE=3D2>begin = > work;    &nb

Related threads

Different instances with markedly different times