RE: Locking issues

Posted in 2004

Savio Pinto (s) — Tue, 24 Aug 2004 17:06:34 -0500 — source: Usenet: comp.databases.informix

This is a multi-part message in MIME format. ------_=_NextPart_001_01C48A26.9EBFC65C Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable what about ..... 1) removing the "set isolation to dirty read;" statement.=20 2) changing the set lock mode statement to "set lock mode to wait 30;"=20 =20 -----Original Message----- From: owner-informix-list@iiug.org = [mailto:owner-informix-list@iiug.org]On Behalf Of Brian Minnick Sent: Tuesday, August 24, 2004 2:32 PM To: informix-list@iiug.org Subject: Locking issues We had to move from an old SE database to IDS 9.4 and=20 ran into some locking issues with one of our=20 applications. In SE, we had our non-logged database=20 communicating to another logged one. Since this isn't=20 allowed in IDS, we're tweaking the application to work=20 around it. However, we're running into some locking=20 problems. Here's a very quick test scenario:=20 SCHEMA:=20 create table t1 =20 (order_num integer, =20 line_num integer) =20 extent size 16 next size 16 lock mode row; =20 create unique index t11 on t1 (order_num,line_Num);=20 LOAD A FEW ROWS:=20 insert into t1(order_num,line_num) values (203,2); =20 =20 insert into t1(order_num,line_num) values (203,3); =20 =20 insert into t1(order_num,line_num) values (205,1); =20 =20 insert into t1(order_num,line_num) values (208,1); =20 =20 insert into t1(order_num,line_num) values (208,2); =20 =20 insert into t1(order_num,line_num) values (208,3); =20 =20 update statistics for table t1; =20 =20 select order_num,count(*) from t1 group by order_num;=20 USER PROCESS 1:=20 -- delete all rows for order # 203=20 begin work; =20 set isolation to dirty read; =20 set lock mode to not wait; =20 delete from t1 where order_num =3D 203; =20# 2 rows deleted=20 USER PROCESS 2:=20 -- delete all rows for order # 208=20 begin work; =20 set isolation to dirty read; =20 set lock mode to not wait; =20 delete from t1 where order_num =3D 208 =20 -- and we get this:=20 # 243: Could not position within a table=20 (informix.t1). =20 107: ISAM error: record is locked. =20 =20 -- a query from sysmaster shows an intent-exclusive=20 lock on the table level (first row of output):=20 dbsname tabname rowidr keynum type=20 sesid ownername=20 co_01 t1 0 0 IX =20 8884 USER1=20 co_01 t1 257 0 X =20 8884 USER1=20 co_01 t11 257 1 X =20 8884 USER1=20 co_01 t1 258 0 X =20 8884 USER1=20 co_01 t11 258 1 X =20 8884 USER1=20 ----=20 We also tried different variations of this withOUT the=20 index but we're always prevented from deleting rows=20 from the table while user 1's transaction is open,=20 even if they are different rows. I thought with an=20 isolation level of dirty read it would just blow by=20 the rows for order_num 203 and delete the rows for=20 user 208. What are we missing here and what is a good=20 workaround??=20 Thank you,=20 Brian Minnick=20 Brian Minnick=20 DBA / Systems Developer=20 Belle Tire Distributors Inc=20 (313) 203-2192=20 bminnick@belletire.com=20 ------_=_NextPart_001_01C48A26.9EBFC65C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <TITLE>Locking issues</TITLE> <META content=3D"MSHTML 5.50.4731.2200" name=3DGENERATOR></HEAD> <BODY> <DIV>what=20 about .....</DIV> <DIV>1)=20 removing the "set isolation to dirty read;" = statement. </DIV> <DIV>2)=20 changing the set lock mode statement to "set lock mode to wait 30;"=20 </DIV> <DIV> </DIV> <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px"> <DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft>-----Original Message----- From: = owner-informix-list@iiug.org=20 [mailto:owner-informix-list@iiug.org]On Behalf Of Brian=20 Minnick Sent: Tuesday, August 24, 2004 2:32 PM To: = informix-list@iiug.org Subject: Locking = issues </DIV> We had to move from an old SE database to IDS 9.4 = and=20 ran into some locking issues with one of our = <FONT=20 size=3D2>applications.  In SE, we had our non-logged = database=20 communicating to another logged one.  Since = this=20 isn't allowed in IDS, we're tweaking the = application=20 to work around it.  However, we're = running into=20 some locking problems.  Here's a very = quick test=20 scenario: SCHEMA: create table t1  (order_num=20 = integer,          &nbsp= ;            =            =20 line_num integer)  extent size 16=20 next size 16 lock mode=20 row;           = create unique index t11 on t1 = (order_num,line_Num);=20 LOAD A FEW ROWS: insert = into=20 t1(order_num,line_num) values (203,2);    = <FONT=20 size=3D2>  insert into = t1(order_num,line_num)=20 values (203,3);     =20 insert into t1(order_num,line_num) values=20 (205,1);      = <FONT=20 size=3D2>insert into t1(order_num,line_num) values = (208,1);   =20   <FONT siz

RE: Locking issues

Related threads

Your privacy choices