Re: Locking issues

Posted in 2004

scottishpoet — 25 Aug 2004 02:24:50 -0700 — source: Usenet: comp.databases.informix

True, IDS won't allowed distributed queries/transactions between a logged and a non logged database. But you say you had your 2 SE databases communicating with each other. Since SE does not support distributed databases, how were you doing this? Did you edit the systables table to point to the other database structure? Or was it coded? If it was coded then you should be able to use the same behaviour. The example below only uses 1 table (t1). How does this show a behaviour problem across a distributed database? Try altering the table to lock mode ROW as the default in IDS is PAGE where the only option on SE is ROW 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; > > > <FONT SIZE

Re: Locking issues

Related threads

Your privacy choices