RE: Locking issues
Brian,
Try to make the server do the delete by index
using the optimizer directive:
delete --+AVOID_FULL(t1)
from t1 where order_num = 203;
In Your example, the table is too small and the server
can decide to go by sequential scan
------------------------------------------
Alexey Sonkin
'
-----Original Message-----
From: Brian Minnick [mailto:BMinnick@belletire.com]
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.''''''''''''''''''''
Brian Minnick
DBA / Systems Developer
Belle Tire Distributors Inc
(313) 203-2192
bminnick@belletire.com
sending to informix-list