Re: Locking issues

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 Man, Mime does suck. I just deleted many lines that had nothing to do with the problem. The problem is that you are scanning the table because the optimizer, quite rightly, notes that the table is too small to bother using an index. Use the Optimizer directives to fix this query. Of course you shouldn't keep transactions open forever anyway. So you should wait a little while. Optimizer directive: delete {+ avoid_full(t1) } from t1 where order_num = 203; Or delete {+ index(t1 t11) } from t1 where order_num = 203;

Curtis Crowson wrote: > Man, Mime does suck. I just deleted many lines that had nothing to do > with the problem. I'm sure you'll find someone jumpin' in to say it's your fault for using an old newsreader and that you shouldn't be so inconsiderate as to complain. Me, I think people who use posting software that doesn't produce plain text are as unpleasant as a pair of fetid dingo's kidneys! -- Strewth! Stick a sock in it, Sheila!