FW: Locking issues
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_01C48ADE.D3FF1920
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks to Alexey and Curtis for the following suggestions to
use optimizer directives to utilize an index instead of
a sequential scan. Both worked very well in all my tests.=20
delete {+ avoid_full(t1) } from t1 where
order_num =3D 203;
delete {+ index(t1 t11) } from t1 where order_num =3D 203;
And thanks to all who have responded,
Brian Minnick
-----Original Message-----
From: Alexey Sonkin [mailto:alexeis@grandvirtual.com]
Sent: Tuesday, August 24, 2004 7:37 PM
To: 'Brian Minnick'
Subject: RE: Locking issues
Brian,
Try to make the server do the delete by index
using the optimizer directive:
delete --+AVOID_FULL(t1)=20
from t1 where order_num =3D 203; =20
In Your example, the table is too small and the server
can decide to go by sequential scan
------------------------------------------
Alexey Sonkin
=A0
-----Original Message-----
From: Brian Minnick [mailto:BMinnick@belletire.com]=20
We had to move from an old SE database to IDS 9.4 and=20
ran into some locking issues with one of our=20
applications.=A0 In SE, we had our non-logged database=20
communicating to another logged one.=A0 Since this isn't=20
allowed in IDS, we're tweaking the application to work=20
around it.=A0 However, we're running into some locking=20
problems.=A0 Here's a very quick test scenario:=20
SCHEMA:=20
create table t1=A0=20
(order_num =
integer,=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0==A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
line_num integer)=A0=20
extent size 16 next size 16 lock mode =
row;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
create unique index t11 on t1 (order_num,line_Num);=20LOAD A FEW ROWS:=20
insert into t1(order_num,line_num) values (203,2);=A0=A0=A0=20=A0=20
insert into t1(order_num,line_num) values (203,3);=A0=A0=A0=20=A0=20
insert into t1(order_num,line_num) values (205,1);=A0=A0=A0=20=A0=20
insert into t1(order_num,line_num) values (208,1);=A0=A0=A0=20=A0=20
insert into t1(order_num,line_num) values (208,2);=A0=A0=A0=20=A0=20
insert into t1(order_num,line_num) values (208,3);=A0=A0=A0=20=A0=20
update statistics for table =t1;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=20
select order_num,count(*) from t1 group by order_num;=20USER PROCESS 1:=20
-- delete all rows for order # 203=20
begin =
work;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=20
set isolation to dirty read;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
set lock mode to not wait;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
delete from t1 where order_num =3D 203;=A0=A0=20# 2 rows deleted=20
USER PROCESS 2:=20
-- delete all rows for order # 208=20
begin =
work;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=20
set isolation to dirty read;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
set lock mode to not wait;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
delete from t1 where order_num =3D 208=A0=A0=A0=20
-- and we get this:=20# 243: Could not position within a table=20
(informix.t1).=A0=A0=A0=A0=20
107: ISAM error:=A0 record is =
locked.=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20
=A0=A0=20Brian Minnick=20
DBA / Systems Developer=20
Belle Tire Distributors Inc=20
(313) 203-2192=20
bminnick@belletire.com=20
------_=_NextPart_001_01C48ADE.D3FF1920
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>FW: Locking issues</TITLE>
</HEAD>
<BODY>
<BR>
<P><FONT SIZE=3D2>Thanks to Alexey and Curtis for the following =
suggestions to</FONT>
<BR><FONT SIZE=3D2>use optimizer directives to utilize an index instead =
of</FONT>
<BR><FONT SIZE=3D2>a sequential scan. Both worked very well in all my =
tests. </FONT>
</P>
<P><FONT SIZE=3D2>delete {+ avoid_full(t1) } from t1 where</FONT>
<BR><FONT SIZE=3D2>order_num =3D 203;</FONT>
</P>
<P><FONT SIZE=3D2>delete {+ index(t1 t11) } from t1 where order_num =3D =
203;</FONT>
</P>
<P><FONT SIZE=3D2>And thanks to all who have responded,</FONT>
</P>
<P><FONT SIZE=3D2>Brian Minnick</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Alexey Sonkin [<A =
HREF=3D"mailto:alexeis@grandvirtual.com">mailto:alexeis@grandvirtual.com=
</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Tuesday, August 24, 2004 7:37 PM</FONT>
<BR><FONT SIZE=3D2>To: 'Brian Minnick'</FONT>
<BR><FONT SIZE=3D2>Subject: RE: Locking issues</FONT>
</P>
<BR>
<BR>
<BR>
<P><FONT SIZE=3D2>Brian,</FONT>
</P>
<P><FONT SIZE=3D2>Try to make the server do the delete by index</FONT>
<BR><FONT SIZE=3D2>using the optimizer directive:</FONT>
</P>
<P><FONT SIZE=3D2>delete --+AVOID_FULL(t1) </FONT>
<BR><FONT SIZE=3D2>from t1 where order_num =3D 203; </FONT>
</P>
<P><FONT SIZE=3D2>In Your example, the table is too small and the =
server</FONT>
<BR><FONT SIZE=3D2>can decide to go by sequential scan</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>------------------------------------------</FONT>
<BR><FONT SIZE=3D2>Alexey Sonkin</FONT>
<BR><FONT SIZE=3D2>=A0</FONT>
<BR><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Brian Minnick [<A =
HREF=3D"mailto:BMinnick@belletire.com">mailto:BMinnick@belletire.com</A>=
] </FONT>
</P>
<P><FONT SIZE=3D2>We had to move from an old SE database to IDS 9.4 and =
</FONT>
<BR><FONT SIZE=3D2>ran into some locking issues with one of our </FONT>
<BR><FONT SIZE=3D2>applications.=A0 In SE, we had our non-logged =
database </FONT>
<BR><FONT SIZE=3D2>communicating to another logged one.=A0 Since this =
isn't </FONT>
<BR><FONT SIZE=3D2>allowed in IDS, we're tweaking the application to =
work </FONT>
<BR><FONT SIZE=3D2>around it.=A0 However, we're running into some =
locking </FONT>
<BR><FONT SIZE=3D2>problems.=A0 Here's a very quick test scenario: =
</FONT>
<BR><FONT SIZE=3D2>SCHEMA: </FONT>
<BR><FONT SIZE=3D2>create table t1=A0 </FONT>
<BR><FONT SIZE=3D2>(order_num =
integer,=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 </FONT>
<BR><FONT SIZE=3D2>line_num integer)=A0 </FONT>
<BR><FONT SIZE=3D2>extent size 16 next size 16 lock mode =
row;=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 </FON