RE: Locking issues

All well and good but then the second application would wait 30 seconds before giving an error. I think Madison was onto something with the small table size and sequential scan of the index. Using Optimizer hints may be the only way out if that is the case. Throw 500K rows into a test table and see if you get the same results... Don't let delete transactions stay OPEN...if they want to DELETE then DELETE and COMMIT work. Then a SET LOCK MODE TO WAIT x strategy should work. Never LOCK the first set of rows until you REALLY mean it. Of course retrofitting a legacy application to use Logging, transactions and Units of Work is always a fun task. Best of luck. Rob Vorbroker - Say Hi to Ron for me...tell him I'm BUSY. --- "Savio Pinto (s)" <spinto@cap.org> wrote: > what about ..... > 1) removing the "set isolation to dirty read;" > statement. > 2) changing the set lock mode statement to "set lock > mode to wait 30;" > > > -----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 > 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 > > ===== Rob Vorbroker Phone: 513/336-8695 Vorbroker Consulting, Inc. Fax: 513/336-6812 www.vorbroker.com robv@vorbroker.com sending to informix-list