Locking issues

Posted in 2004

Topics: Storage & Space Management, SQL Development & Query Writing, Error Codes & Troubleshooting, Server Administration, Transactions, Locking & Isolation, Versions, Editions & End-of-Life

Brian Minnick — Tue, 24 Aug 2004 15:31:43 -0400 — source: Usenet: comp.databases.informix

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C48A10.FCD7AFC0 Content-Type: text/plain; charset="iso-8859-1" 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  (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);      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;                    &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 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 begin = work;           &= nbsp;           &= nbsp;     @@

↪ replying to Brian Minnick

Madison Pruet — Tue, 24 Aug 2004 19:57:49 GMT — source: Usenet: comp.databases.informix

Because your table is fairly small, a sequential scan is being used for the second delete query. That means that it is bumping into the prior delete. Also, be sure to use lock mode row on the table (which you have already done.) Finally, if you want, you can force the use of the index by using optimizer hints. M.P. "Brian Minnick" <BMinnick@belletire.com> wrote in message news:cgg5mg$l68$1@news.xmission.com... > > This message is in MIME format. Since your mail reader does not understand > this format, some or all of this message may not be legible. > > ------_=_NextPart_001_01C48A10.FCD7AFC0 > Content-Type: text/plain; > charset="iso-8859-1" > > 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  > (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);    >   > 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 > begin = > work;           &= > nbsp;           &=@

Locking issues

Related threads

Glossary

Your privacy choices