Interesting 4gl lock problem on IDS 7
A 4GL program on IDS 7.31 updates one row of sn_master via a FOR UPDATE cursor on a unique index, yet other sessions running SELECT ... WHERE extra_product = ... fail with error 244/ISAM 107 (record is locked), and syslocks shows several locks for the one rowid with different keynum values. Replies explained that the failing SELECT has no usable index, so it table-scans and hits the locked row (committed read or an added index would help), and that the extra locks with keynum are index-node locks, since the engine must lock the row's entry in each affected index. No confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
4GL 7.20 on IDS 7.31.UC5 on AIX 4.3.2
I am getting an interesting locking problem whenever I do an update on
a single row within the attached table.
The program is a 4gl program within an UPDATE cursor:-
BEGIN WORK;
SET ISOLATION DIRTY READ;
DECLARE c_master CURSOR FOR
SELECT * from sn_master
WHERE master_code = "330179531929621" -- Unique master_code
FOR UPDATE
OPEN c_master
FETCH c_master INTO rec_record.*
UPDATE sn_master
SET master_status =
master_sim =
blah
blah
blah
WHERE CURRENT OF c_master
COMMIT WORK
Can anyone please explain why I cant perform the following select
statement and why I get so many locks on the database for a single row
update.
SELECT * FROM sn_master
WHERE extra_product = "anything"# ^
# 244: Could not do a physical-order read to fetch next row.
# 107: ISAM error: record is locked.
#
where "anything" can be a unique extra_product code (other than that
already being locked), nothing or literally anything.
The problem isn't with the SELECT statement as all I have to do is SET
ISOLATION TO DIRTY READ and this solves the problem. But I have a
number of other routines that update different rows with different
extra_product values but they appear to be locked out of the table
completely. Even PERForm forms won't allow selects on that field.
I have attached the table schema as it stands and an example row with
associated output.
Thanks as always.....
S.
===============================
Row locked is:-
rowid 4355
master_key 2079358
pallet_key -20207913
batch_key 33457
master_code 330179531929621
extra_product P04531001
master_dbname sys
master_wh 01
master_imei
master_pcn 07833631121
master_sim 89441000000194834156
master_pcn_data
master_pcn_fax
master_pcn_video
master_status CONFIRMED
master_weight
location_key -4423
master_po_no_key 007682
master_order_key 111186
master_country UK
master_station CRW
user_stamp uksysxrb
time_stamp 2000-02-24 12:10:00
master_supplier 5651
master_customer 6152462
man_pallet_id COL0176112
man_batch_no COL0176112
man_product 3
man_extra1
man_extra2
man_extra3 VFBULK
master_date_in 1999-11-24 15:42:35
master_date_out
When querying sysmaster:syslocks:-
dbsname tabname rowidlk keynum type Owner Waiter
dev sn_master 0 0 IX 1441
dev sn_master 4355 0 X 1441
dev sn_master 4355 4 X 1441
dev sn_master 4355 4 X 1441
dev sn_master 4355 9 X 1441
dev sn_master 4355 9 X 1441
dev sn_master 4355 11 X 1441
dev sn_master 4355 11 X 1441
TABLE SCHEMA
============
create table sn_master
(
master_key serial not null ,
pallet_key integer not null ,
batch_key integer not null ,
master_code char(20) not null ,
extra_product char(20) not null ,
master_dbname char(8) not null ,
master_wh char(2) not null ,
master_imei char(20),
master_pcn char(20),
master_sim char(20),
master_pcn_data char(20),
master_pcn_fax char(20),
master_pcn_video char(20),
master_status char(20),
master_weight decimal(12,4),
location_key integer not null ,
master_po_no_key char(14),
master_order_key char(14),
master_country char(2),
master_station char(3),
user_stamp char(8),
time_stamp datetime year to second,
master_supplier char(8),
master_customer char(8),
man_pallet_id char(20),
man_batch_no char(20),
man_product char(20),
man_extra1 char(20),
man_extra2 char(20),
man_extra3 char(20),
master_date_in datetime year to second,
master_date_out datetime year to second
);
create unique index sn_master_idx1 on sn_master (master_key);
create index sn_master_idx2 on sn_master (man_pallet_id,master_key);
create index sn_master_idx3 on sn_master (man_product);
create unique index sn_master_idx4 on sn_master (master_code);
create index sn_master_idx5 on sn_master (batch_key desc,master_key);
create index sn_master_idx6 on sn_master (pallet_key desc,master_key);
create index sn_master_idx7 on sn_master
(master_dbname,master_po_no_key,extra_product,master_key);
create index sn_master_idx8 on sn_master
(master_dbname,master_order_key,extra_product,master_key);
create index sn_master_idx9 on sn_master
(master_dbname,master_wh,extra_product,master_status,master_key);
create index sn_master_idx10 on sn_master (location_key
desc,master_key);
create index sn_master_idx11 on sn_master
(master_dbname,master_order_key,extra_product);
Sent via Deja.com http://www.deja.com/
Before you buy.
Your select is not using an index.
<chilliinc6230@my-deja.com> wrote in message
news:8pqpo8$sk8$1@nnrp1.deja.com...
> 4GL 7.20 on IDS 7.31.UC5 on AIX 4.3.2
>
> I am getting an interesting locking problem whenever I do an update on
> a single row within the attached table.
>
> The program is a 4gl program within an UPDATE cursor:-
>
> BEGIN WORK;
>
> SET ISOLATION DIRTY READ;>
> DECLARE c_master CURSOR FOR
> SELECT * from sn_master
> WHERE master_code = "330179531929621" -- Unique master_code
> FOR UPDATE>
> OPEN c_master
>
> FETCH c_master INTO rec_record.*
>
>
> UPDATE sn_master
> SET master_status =
> master_sim =
> blah
> blah
> blah
> WHERE CURRENT OF c_master
>
> COMMIT WORK
>
>
> Can anyone please explain why I cant perform the following select
> statement and why I get so many locks on the database for a single row
> update.
>
> SELECT * FROM sn_master
> WHERE extra_product = "anything"> # ^
> # 244: Could not do a physical-order read to fetch next row.
> # 107: ISAM error: record is locked.
> #
>
> where "anything" can be a unique extra_product code (other than that
> already being locked), nothing or literally anything.
>
> The problem isn't with the SELECT statement as all I have to do is SET
> ISOLATION TO DIRTY READ and this solves the problem. But I have a
> number of other routines that update different rows with different
> extra_product values but they appear to be locked out of the table
> completely. Even PERForm forms won't allow selects on that field.
>
> I have attached the table schema as it stands and an example row with
> associated output.
>
> Thanks as always.....
>
> S.
>
> ===============================
>
> Row locked is:-
>
> rowid 4355
> master_key 2079358
> pallet_key -20207913
> batch_key 33457
> master_code 330179531929621
> extra_product P04531001
> master_dbname sys
> master_wh 01
> master_imei
> master_pcn 07833631121
> master_sim 89441000000194834156
> master_pcn_data
> master_pcn_fax
> master_pcn_video
> master_status CONFIRMED
> master_weight
> location_key -4423
> master_po_no_key 007682
> master_order_key 111186
> master_country UK
> master_station CRW
> user_stamp uksysxrb
> time_stamp 2000-02-24 12:10:00
> master_supplier 5651
> master_customer 6152462
> man_pallet_id COL0176112
> man_batch_no COL0176112
> man_product 3
> man_extra1
> man_extra2
> man_extra3 VFBULK
> master_date_in 1999-11-24 15:42:35
> master_date_out
>
> When querying sysmaster:syslocks:-
>
> dbsname tabname rowidlk keynum type Owner Waiter
> dev sn_master 0 0 IX 1441
> dev sn_master 4355 0 X 1441
> dev sn_master 4355 4 X 1441
> dev sn_master 4355 4 X 1441
> dev sn_master 4355 9 X 1441
> dev sn_master 4355 9 X 1441
> dev sn_master 4355 11 X 1441
> dev sn_master 4355 11 X 1441
>
> TABLE SCHEMA
> ============
>
> create table sn_master
> (
> master_key serial not null ,
> pallet_key integer not null ,
> batch_key integer not null ,
> master_code char(20) not null ,
> extra_product char(20) not null ,
> master_dbname char(8) not null ,
> master_wh char(2) not null ,
> master_imei char(20),
> master_pcn char(20),
> master_sim char(20),
> master_pcn_data char(20),
> master_pcn_fax char(20),
> master_pcn_video char(20),
> master_status char(20),
> master_weight decimal(12,4),
> location_key integer not null ,
> master_po_no_key char(14),
> master_order_key char(14),
> master_country char(2),
> master_station char(3),
> user_stamp char(8),
> time_stamp datetime year to second,
> master_supplier char(8),
> master_customer char(8),
> man_pallet_id char(20),
> man_batch_no char(20),
> man_product char(20),
> man_extra1 char(20),
> man_extra2 char(20),
> man_extra3 char(20),
> master_date_in datetime year to second,
> master_date_out datetime year to second
> );>
>
> create unique index sn_master_idx1 on sn_master (master_key);
> create index sn_master_idx2 on sn_master (man_pallet_id,master_key);
> create index sn_master_idx3 on sn_master (man_product);
> create unique index sn_master_idx4 on sn_master (master_code);
> create index sn_master_idx5 on sn_master (batch_key desc,master_key);
> create index sn_master_idx6 on sn_master (pallet_key desc,master_key);
> create index sn_master_idx7 on sn_master
> (master_dbname,master_po_no_key,extra_product,master_key);
> create index sn_master_idx8 on sn_master
> (master_dbname,master_order_key,extra_product,master_key);
> create index sn_master_idx9 on sn_master
> (master_dbname,master_wh,extra_product,master_status,master_key);
> create index sn_master_idx10 on sn_master (location_key
> desc,master_key);
> create index sn_master_idx11 on sn_master
> (master_dbname,master_order_key,extra_product);>
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Depending on the ISOLATION level it is possible for the FOR UPDATE query to
lock every row that it examines. If the query is triggering a table scan
then that means it is examining every row in the table effectively locking
every row. Since DIRTY READ solves the problem that is most likely what is
happening. Two suggestions add the missing index and set the isolation to
committed read.
Art S. Kagel
chilliinc6230@my-deja.com wrote:
>
> 4GL 7.20 on IDS 7.31.UC5 on AIX 4.3.2
>
> I am getting an interesting locking problem whenever I do an update on
> a single row within the attached table.
>
> The program is a 4gl program within an UPDATE cursor:-
>
> BEGIN WORK;
>
> SET ISOLATION DIRTY READ;>
> DECLARE c_master CURSOR FOR
> SELECT * from sn_master
> WHERE master_code = "330179531929621" -- Unique master_code
> FOR UPDATE>
> OPEN c_master
>
> FETCH c_master INTO rec_record.*
>
> UPDATE sn_master
> SET master_status =
> master_sim =
> blah
> blah
> blah
> WHERE CURRENT OF c_master
>
> COMMIT WORK
>
> Can anyone please explain why I cant perform the following select
> statement and why I get so many locks on the database for a single row
> update.
>
> SELECT * FROM sn_master
> WHERE extra_product = "anything"> # ^
> # 244: Could not do a physical-order read to fetch next row.
> # 107: ISAM error: record is locked.
> #
>
> where "anything" can be a unique extra_product code (other than that
> already being locked), nothing or literally anything.
>
> The problem isn't with the SELECT statement as all I have to do is SET
> ISOLATION TO DIRTY READ and this solves the problem. But I have a
> number of other routines that update different rows with different
> extra_product values but they appear to be locked out of the table
> completely. Even PERForm forms won't allow selects on that field.
>
> I have attached the table schema as it stands and an example row with
> associated output.
>
> Thanks as always.....
>
> S.
>
> ===============================
>
> Row locked is:-
>
> rowid 4355
> master_key 2079358
> pallet_key -20207913
> batch_key 33457
> master_code 330179531929621
> extra_product P04531001
> master_dbname sys
> master_wh 01
> master_imei
> master_pcn 07833631121
> master_sim 89441000000194834156
> master_pcn_data
> master_pcn_fax
> master_pcn_video
> master_status CONFIRMED
> master_weight
> location_key -4423
> master_po_no_key 007682
> master_order_key 111186
> master_country UK
> master_station CRW
> user_stamp uksysxrb
> time_stamp 2000-02-24 12:10:00
> master_supplier 5651
> master_customer 6152462
> man_pallet_id COL0176112
> man_batch_no COL0176112
> man_product 3
> man_extra1
> man_extra2
> man_extra3 VFBULK
> master_date_in 1999-11-24 15:42:35
> master_date_out
>
> When querying sysmaster:syslocks:-
>
> dbsname tabname rowidlk keynum type Owner Waiter
> dev sn_master 0 0 IX 1441
> dev sn_master 4355 0 X 1441
> dev sn_master 4355 4 X 1441
> dev sn_master 4355 4 X 1441
> dev sn_master 4355 9 X 1441
> dev sn_master 4355 9 X 1441
> dev sn_master 4355 11 X 1441
> dev sn_master 4355 11 X 1441
>
> TABLE SCHEMA
> ============
>
> create table sn_master
> (
> master_key serial not null ,
> pallet_key integer not null ,
> batch_key integer not null ,
> master_code char(20) not null ,
> extra_product char(20) not null ,
> master_dbname char(8) not null ,
> master_wh char(2) not null ,
> master_imei char(20),
> master_pcn char(20),
> master_sim char(20),
> master_pcn_data char(20),
> master_pcn_fax char(20),
> master_pcn_video char(20),
> master_status char(20),
> master_weight decimal(12,4),
> location_key integer not null ,
> master_po_no_key char(14),
> master_order_key char(14),
> master_country char(2),
> master_station char(3),
> user_stamp char(8),
> time_stamp datetime year to second,
> master_supplier char(8),
> master_customer char(8),
> man_pallet_id char(20),
> man_batch_no char(20),
> man_product char(20),
> man_extra1 char(20),
> man_extra2 char(20),
> man_extra3 char(20),
> master_date_in datetime year to second,
> master_date_out datetime year to second
> );>
> create unique index sn_master_idx1 on sn_master (master_key);
> create index sn_master_idx2 on sn_master (man_pallet_id,master_key);
> create index sn_master_idx3 on sn_master (man_product);
> create unique index sn_master_idx4 on sn_master (master_code);
> create index sn_master_idx5 on sn_master (batch_key desc,master_key);
> create index sn_master_idx6 on sn_master (pallet_key desc,master_key);
> create index sn_master_idx7 on sn_master
> (master_dbname,master_po_no_key,extra_product,master_key);
> create index sn_master_idx8 on sn_master
> (master_dbname,master_order_key,extra_product,master_key);
> create index sn_master_idx9 on sn_master
> (master_dbname,master_wh,extra_product,master_status,master_key);
> create index sn_master_idx10 on sn_master (location_key
> desc,master_key);
> create index sn_master_idx11 on sn_master
> (master_dbname,master_order_key,extra_product);>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Thanks ASK for the response...
I understand what your saying but the cursor itself has been declared
using the unique index on the master_code column. I have isolated the
sql statement and it does use the indexes for the search.
Locking every row wouldn't be the problem either, as this only locks
one row (indicated by the rowidlk value within syslocks.). Its baffling
that there are 7 locks directly affecting just a single row (with
multiple keyid values). Where have all these come from ???
Thanks again for your response ASK.
In article <39C1317F.D43F964C@bloomberg.net>,
kagel@bloomberg.net wrote:
> Depending on the ISOLATION level it is possible for the FOR UPDATE
query to
> lock every row that it examines. If the query is triggering a table
scan
> then that means it is examining every row in the table effectively
locking
> every row. Since DIRTY READ solves the problem that is most likely
what is
> happening. Two suggestions add the missing index and set the
isolation to
> committed read.
>
> Art S. Kagel
>
> chilliinc6230@my-deja.com wrote:
> >
> > 4GL 7.20 on IDS 7.31.UC5 on AIX 4.3.2
> >
> > I am getting an interesting locking problem whenever I do an update
on
> > a single row within the attached table.
> >
> > The program is a 4gl program within an UPDATE cursor:-
> >
> > BEGIN WORK;
> >
> > SET ISOLATION DIRTY READ;> >
> > DECLARE c_master CURSOR FOR
> > SELECT * from sn_master
> > WHERE master_code = "330179531929621" -- Unique master_code
> > FOR UPDATE> >
> > OPEN c_master
> >
> > FETCH c_master INTO rec_record.*
> >
> > UPDATE sn_master
> > SET master_status =
> > master_sim =
> > blah
> > blah
> > blah
> > WHERE CURRENT OF c_master
> >
> > COMMIT WORK
> >
> > Can anyone please explain why I cant perform the following select
> > statement and why I get so many locks on the database for a single
row
> > update.
> >
> > SELECT * FROM sn_master
> > WHERE extra_product = "anything"> > # ^
> > # 244: Could not do a physical-order read to fetch next row.
> > # 107: ISAM error: record is locked.
> > #
> >
> > where "anything" can be a unique extra_product code (other than that
> > already being locked), nothing or literally anything.
> >
> > The problem isn't with the SELECT statement as all I have to do is
SET
> > ISOLATION TO DIRTY READ and this solves the problem. But I have a
> > number of other routines that update different rows with different
> > extra_product values but they appear to be locked out of the table
> > completely. Even PERForm forms won't allow selects on that field.
> >
> > I have attached the table schema as it stands and an example row
with
> > associated output.
> >
> > Thanks as always.....
> >
> > S.
> >
> > ===============================
> >
> > Row locked is:-
> >
> > rowid 4355
> > master_key 2079358
> > pallet_key -20207913
> > batch_key 33457
> > master_code 330179531929621
> > extra_product P04531001
> > master_dbname sys
> > master_wh 01
> > master_imei
> > master_pcn 07833631121
> > master_sim 89441000000194834156
> > master_pcn_data
> > master_pcn_fax
> > master_pcn_video
> > master_status CONFIRMED
> > master_weight
> > location_key -4423
> > master_po_no_key 007682
> > master_order_key 111186
> > master_country UK
> > master_station CRW
> > user_stamp uksysxrb
> > time_stamp 2000-02-24 12:10:00
> > master_supplier 5651
> > master_customer 6152462
> > man_pallet_id COL0176112
> > man_batch_no COL0176112
> > man_product 3
> > man_extra1
> > man_extra2
> > man_extra3 VFBULK
> > master_date_in 1999-11-24 15:42:35
> > master_date_out
> >
> > When querying sysmaster:syslocks:-
> >
> > dbsname tabname rowidlk keynum type Owner Waiter
> > dev sn_master 0 0 IX 1441
> > dev sn_master 4355 0 X 1441
> > dev sn_master 4355 4 X 1441
> > dev sn_master 4355 4 X 1441
> > dev sn_master 4355 9 X 1441
> > dev sn_master 4355 9 X 1441
> > dev sn_master 4355 11 X 1441
> > dev sn_master 4355 11 X 1441
> >
> > TABLE SCHEMA
> > ============
> >
> > create table sn_master
> > (
> > master_key serial not null ,
> > pallet_key integer not null ,
> > batch_key integer not null ,
> > master_code char(20) not null ,
> > extra_product char(20) not null ,
> > master_dbname char(8) not null ,
> > master_wh char(2) not null ,
> > master_imei char(20),
> > master_pcn char(20),
> > master_sim char(20),
> > master_pcn_data char(20),
> > master_pcn_fax char(20),
> > master_pcn_video char(20),
> > master_status char(20),
> > master_weight decimal(12,4),
> > location_key integer not null ,
> > master_po_no_key char(14),
> > master_order_key char(14),
> > master_country char(2),
> > master_station char(3),
> > user_stamp char(8),
> > time_stamp datetime year to second,
> > master_supplier char(8),
> > master_customer char(8),
> > man_pallet_id char(20),
> > man_batch_no char(20),
> > man_product char(20),
> > man_extra1 char(20),
> > man_extra2 char(20),
> > man_extra3 char(20),
> > master_date_in datetime year to second,
> > master_date_out datetime year to second
> > );> >
> > create unique index sn_master_idx1 on sn_master (master_key);
> > create index sn_master_idx2 on sn_master (man_pallet_id,master_key);
> > create index sn_master_idx3 on sn_master (man_product);
> > create unique index sn_master_idx4 on sn_master (master_code);
> > create index sn_master_idx5 on sn_master (batch_key
desc,master_key);
> > create index sn_master_idx6 on sn_master (pallet_key
desc,master_key);
> > create index sn_master_idx7 on sn_master
> > (master_dbname,master_po_no_key,extra_product,master_key);
> > create index sn_master_idx8 on sn_master
> > (master_dbname,master_order_key,extra_product,master_key);
> > create index sn_master_idx9 on sn_master
> > (master_dbname,master_wh,extra_product,master_status,master_key);
> > create index sn_master_idx10 on sn_master (location_key
> > desc,master_key);
> > create index sn_master_idx11 on sn_master
> > (master_dbname,master_order_key,extra_product);> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Thanks for the reply...but....
this doesn't explain why all of the locks are on the same row, just
with different keynum values !!!
(by the way, what is the significance of keynum ??)
Thanks again
S.
In article <DB8w5.25996$Z2.350675@nnrp1.uunet.ca>,
"Mariusz Malogrosz" <mariuszm@tecsys.com> wrote:
> Your select is not using an index.
>
> <chilliinc6230@my-deja.com> wrote in message
> news:8pqpo8$sk8$1@nnrp1.deja.com...
> > 4GL 7.20 on IDS 7.31.UC5 on AIX 4.3.2
> >
> > I am getting an interesting locking problem whenever I do an update
on
> > a single row within the attached table.
> >
> > The program is a 4gl program within an UPDATE cursor:-
> >
> > BEGIN WORK;
> >
> > SET ISOLATION DIRTY READ;> >
> > DECLARE c_master CURSOR FOR
> > SELECT * from sn_master
> > WHERE master_code = "330179531929621" -- Unique master_code
> > FOR UPDATE> >
> > OPEN c_master
> >
> > FETCH c_master INTO rec_record.*
> >
> >
> > UPDATE sn_master
> > SET master_status =
> > master_sim =
> > blah
> > blah
> > blah
> > WHERE CURRENT OF c_master
> >
> > COMMIT WORK
> >
> >
> > Can anyone please explain why I cant perform the following select
> > statement and why I get so many locks on the database for a single
row
> > update.
> >
> > SELECT * FROM sn_master
> > WHERE extra_product = "anything"> > # ^
> > # 244: Could not do a physical-order read to fetch next row.
> > # 107: ISAM error: record is locked.
> > #
> >
> > where "anything" can be a unique extra_product code (other than that
> > already being locked), nothing or literally anything.
> >
> > The problem isn't with the SELECT statement as all I have to do is
SET
> > ISOLATION TO DIRTY READ and this solves the problem. But I have a
> > number of other routines that update different rows with different
> > extra_product values but they appear to be locked out of the table
> > completely. Even PERForm forms won't allow selects on that field.
> >
> > I have attached the table schema as it stands and an example row
with
> > associated output.
> >
> > Thanks as always.....
> >
> > S.
> >
> > ===============================
> >
> > Row locked is:-
> >
> > rowid 4355
> > master_key 2079358
> > pallet_key -20207913
> > batch_key 33457
> > master_code 330179531929621
> > extra_product P04531001
> > master_dbname sys
> > master_wh 01
> > master_imei
> > master_pcn 07833631121
> > master_sim 89441000000194834156
> > master_pcn_data
> > master_pcn_fax
> > master_pcn_video
> > master_status CONFIRMED
> > master_weight
> > location_key -4423
> > master_po_no_key 007682
> > master_order_key 111186
> > master_country UK
> > master_station CRW
> > user_stamp uksysxrb
> > time_stamp 2000-02-24 12:10:00
> > master_supplier 5651
> > master_customer 6152462
> > man_pallet_id COL0176112
> > man_batch_no COL0176112
> > man_product 3
> > man_extra1
> > man_extra2
> > man_extra3 VFBULK
> > master_date_in 1999-11-24 15:42:35
> > master_date_out
> >
> > When querying sysmaster:syslocks:-
> >
> > dbsname tabname rowidlk keynum type Owner Waiter
> > dev sn_master 0 0 IX 1441
> > dev sn_master 4355 0 X 1441
> > dev sn_master 4355 4 X 1441
> > dev sn_master 4355 4 X 1441
> > dev sn_master 4355 9 X 1441
> > dev sn_master 4355 9 X 1441
> > dev sn_master 4355 11 X 1441
> > dev sn_master 4355 11 X 1441
> >
> > TABLE SCHEMA
> > ============
> >
> > create table sn_master
> > (
> > master_key serial not null ,
> > pallet_key integer not null ,
> > batch_key integer not null ,
> > master_code char(20) not null ,
> > extra_product char(20) not null ,
> > master_dbname char(8) not null ,
> > master_wh char(2) not null ,
> > master_imei char(20),
> > master_pcn char(20),
> > master_sim char(20),
> > master_pcn_data char(20),
> > master_pcn_fax char(20),
> > master_pcn_video char(20),
> > master_status char(20),
> > master_weight decimal(12,4),
> > location_key integer not null ,
> > master_po_no_key char(14),
> > master_order_key char(14),
> > master_country char(2),
> > master_station char(3),
> > user_stamp char(8),
> > time_stamp datetime year to second,
> > master_supplier char(8),
> > master_customer char(8),
> > man_pallet_id char(20),
> > man_batch_no char(20),
> > man_product char(20),
> > man_extra1 char(20),
> > man_extra2 char(20),
> > man_extra3 char(20),
> > master_date_in datetime year to second,
> > master_date_out datetime year to second
> > );> >
> >
> > create unique index sn_master_idx1 on sn_master (master_key);
> > create index sn_master_idx2 on sn_master (man_pallet_id,master_key);
> > create index sn_master_idx3 on sn_master (man_product);
> > create unique index sn_master_idx4 on sn_master (master_code);
> > create index sn_master_idx5 on sn_master (batch_key
desc,master_key);
> > create index sn_master_idx6 on sn_master (pallet_key
desc,master_key);
> > create index sn_master_idx7 on sn_master
> > (master_dbname,master_po_no_key,extra_product,master_key);
> > create index sn_master_idx8 on sn_master
> > (master_dbname,master_order_key,extra_product,master_key);
> > create index sn_master_idx9 on sn_master
> > (master_dbname,master_wh,extra_product,master_status,master_key);
> > create index sn_master_idx10 on sn_master (location_key
> > desc,master_key);
> > create index sn_master_idx11 on sn_master
> > (master_dbname,master_order_key,extra_product);> >
> >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Locks with a keynum are index node locks. The engine needs to lock not only
the data row but its entries in every index.
Art S. Kagel
sean.kelsey@2020log.com wrote:
>
> Thanks for the reply...but....
>
> this doesn't explain why all of the locks are on the same row, just
> with different keynum values !!!
>
> (by the way, what is the significance of keynum ??)
>
> Thanks again
>
> S.
>
> In article <DB8w5.25996$Z2.350675@nnrp1.uunet.ca>,
> "Mariusz Malogrosz" <mariuszm@tecsys.com> wrote:
> > Your select is not using an index.
> >
> > <chilliinc6230@my-deja.com> wrote in message
> > news:8pqpo8$sk8$1@nnrp1.deja.com...
> > > 4GL 7.20 on IDS 7.31.UC5 on AIX 4.3.2
> > >
> > > I am getting an interesting locking problem whenever I do an update
> on
> > > a single row within the attached table.
> > >
> > > The program is a 4gl program within an UPDATE cursor:-
> > >
> > > BEGIN WORK;
> > >
> > > SET ISOLATION DIRTY READ;> > >
> > > DECLARE c_master CURSOR FOR
> > > SELECT * from sn_master
> > > WHERE master_code = "330179531929621" -- Unique master_code
> > > FOR UPDATE> > >
> > > OPEN c_master
> > >
> > > FETCH c_master INTO rec_record.*
> > >
> > >
> > > UPDATE sn_master
> > > SET master_status =
> > > master_sim =
> > > blah
> > > blah
> > > blah
> > > WHERE CURRENT OF c_master
> > >
> > > COMMIT WORK
> > >
> > >
> > > Can anyone please explain why I cant perform the following select
> > > statement and why I get so many locks on the database for a single
> row
> > > update.
> > >
> > > SELECT * FROM sn_master
> > > WHERE extra_product = "anything"> > > # ^
> > > # 244: Could not do a physical-order read to fetch next row.
> > > # 107: ISAM error: record is locked.
> > > #
> > >
> > > where "anything" can be a unique extra_product code (other than that
> > > already being locked), nothing or literally anything.
> > >
> > > The problem isn't with the SELECT statement as all I have to do is
> SET
> > > ISOLATION TO DIRTY READ and this solves the problem. But I have a
> > > number of other routines that update different rows with different
> > > extra_product values but they appear to be locked out of the table
> > > completely. Even PERForm forms won't allow selects on that field.
> > >
> > > I have attached the table schema as it stands and an example row
> with
> > > associated output.
> > >
> > > Thanks as always.....
> > >
> > > S.
> > >
> > > ===============================
> > >
> > > Row locked is:-
> > >
> > > rowid 4355
> > > master_key 2079358
> > > pallet_key -20207913
> > > batch_key 33457
> > > master_code 330179531929621
> > > extra_product P04531001
> > > master_dbname sys
> > > master_wh 01
> > > master_imei
> > > master_pcn 07833631121
> > > master_sim 89441000000194834156
> > > master_pcn_data
> > > master_pcn_fax
> > > master_pcn_video
> > > master_status CONFIRMED
> > > master_weight
> > > location_key -4423
> > > master_po_no_key 007682
> > > master_order_key 111186
> > > master_country UK
> > > master_station CRW
> > > user_stamp uksysxrb
> > > time_stamp 2000-02-24 12:10:00
> > > master_supplier 5651
> > > master_customer 6152462
> > > man_pallet_id COL0176112
> > > man_batch_no COL0176112
> > > man_product 3
> > > man_extra1
> > > man_extra2
> > > man_extra3 VFBULK
> > > master_date_in 1999-11-24 15:42:35
> > > master_date_out
> > >
> > > When querying sysmaster:syslocks:-
> > >
> > > dbsname tabname rowidlk keynum type Owner Waiter
> > > dev sn_master 0 0 IX 1441
> > > dev sn_master 4355 0 X 1441
> > > dev sn_master 4355 4 X 1441
> > > dev sn_master 4355 4 X 1441
> > > dev sn_master 4355 9 X 1441
> > > dev sn_master 4355 9 X 1441
> > > dev sn_master 4355 11 X 1441
> > > dev sn_master 4355 11 X 1441
> > >
> > > TABLE SCHEMA
> > > ============
> > >
> > > create table sn_master
> > > (
> > > master_key serial not null ,
> > > pallet_key integer not null ,
> > > batch_key integer not null ,
> > > master_code char(20) not null ,
> > > extra_product char(20) not null ,
> > > master_dbname char(8) not null ,
> > > master_wh char(2) not null ,
> > > master_imei char(20),
> > > master_pcn char(20),
> > > master_sim char(20),
> > > master_pcn_data char(20),
> > > master_pcn_fax char(20),
> > > master_pcn_video char(20),
> > > master_status char(20),
> > > master_weight decimal(12,4),
> > > location_key integer not null ,
> > > master_po_no_key char(14),
> > > master_order_key char(14),
> > > master_country char(2),
> > > master_station char(3),
> > > user_stamp char(8),
> > > time_stamp datetime year to second,
> > > master_supplier char(8),
> > > master_customer char(8),
> > > man_pallet_id char(20),
> > > man_batch_no char(20),
> > > man_product char(20),
> > > man_extra1 char(20),
> > > man_extra2 char(20),
> > > man_extra3 char(20),
> > > master_date_in datetime year to second,
> > > master_date_out datetime year to second
> > > );> > >
> > >
> > > create unique index sn_master_idx1 on sn_master (master_key);
> > > create index sn_master_idx2 on sn_master (man_pallet_id,master_key);
> > > create index sn_master_idx3 on sn_master (man_product);
> > > create unique index sn_master_idx4 on sn_master (master_code);
> > > create index sn_master_idx5 on sn_master (batch_key
> desc,master_key);
> > > create index sn_master_idx6 on sn_master (pallet_key
> desc,master_key);
> > > create index sn_master_idx7 on sn_master
> > > (master_dbname,master_po_no_key,extra_product,master_key);
> > > create index sn_master_idx8 on sn_master
> > > (master_dbname,master_order_key,extra_product,master_key);
> > > create index sn_master_idx9 on sn_master
> > > (master_dbname,master_wh,extra_product,master_status,master_key);
> > > create index sn_master_idx10 on sn_master (location_key
> > > desc,master_key);
> > > create index sn_master_idx11 on sn_master
> > > (master_dbname,master_order_key,extra_product);> > >
> > >
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
See my other reply. You did not say that before, this is simply that you
apparently have 6 indexes on this table. Six key locks plus one row/page
lock and voila! Seven locks.
Art S. Kagel
sean.kelsey@2020log.com wrote:
>
> Thanks ASK for the response...
>
> I understand what your saying but the cursor itself has been declared
> using the unique index on the master_code column. I have isolated the
> sql statement and it does use the indexes for the search.
>
> Locking every row wouldn't be the problem either, as this only locks
> one row (indicated by the rowidlk value within syslocks.). Its baffling
> that there are 7 locks directly affecting just a single row (with
> multiple keyid values). Where have all these come from ???
>
> Thanks again for your response ASK.
>
> In article <39C1317F.D43F964C@bloomberg.net>,
> kagel@bloomberg.net wrote:
> > Depending on the ISOLATION level it is possible for the FOR UPDATE
> query to
> > lock every row that it examines. If the query is triggering a table
> scan
> > then that means it is examining every row in the table effectively
> locking
> > every row. Since DIRTY READ solves the problem that is most likely
> what is
> > happening. Two suggestions add the missing index and set the
> isolation to
> > committed read.
> >
> > Art S. Kagel
> >
> > chilliinc6230@my-deja.com wrote:
> > >
> > > 4GL 7.20 on IDS 7.31.UC5 on AIX 4.3.2
> > >
> > > I am getting an interesting locking problem whenever I do an update
> on
> > > a single row within the attached table.
> > >
> > > The program is a 4gl program within an UPDATE cursor:-
> > >
> > > BEGIN WORK;
> > >
> > > SET ISOLATION DIRTY READ;> > >
> > > DECLARE c_master CURSOR FOR
> > > SELECT * from sn_master
> > > WHERE master_code = "330179531929621" -- Unique master_code
> > > FOR UPDATE> > >
> > > OPEN c_master
> > >
> > > FETCH c_master INTO rec_record.*
> > >
> > > UPDATE sn_master
> > > SET master_status =
> > > master_sim =
> > > blah
> > > blah
> > > blah
> > > WHERE CURRENT OF c_master
> > >
> > > COMMIT WORK
> > >
> > > Can anyone please explain why I cant perform the following select
> > > statement and why I get so many locks on the database for a single
> row
> > > update.
> > >
> > > SELECT * FROM sn_master
> > > WHERE extra_product = "anything"> > > # ^
> > > # 244: Could not do a physical-order read to fetch next row.
> > > # 107: ISAM error: record is locked.
> > > #
> > >
> > > where "anything" can be a unique extra_product code (other than that
> > > already being locked), nothing or literally anything.
> > >
> > > The problem isn't with the SELECT statement as all I have to do is
> SET
> > > ISOLATION TO DIRTY READ and this solves the problem. But I have a
> > > number of other routines that update different rows with different
> > > extra_product values but they appear to be locked out of the table
> > > completely. Even PERForm forms won't allow selects on that field.
> > >
> > > I have attached the table schema as it stands and an example row
> with
> > > associated output.
> > >
> > > Thanks as always.....
> > >
> > > S.
> > >
> > > ===============================
> > >
> > > Row locked is:-
> > >
> > > rowid 4355
> > > master_key 2079358
> > > pallet_key -20207913
> > > batch_key 33457
> > > master_code 330179531929621
> > > extra_product P04531001
> > > master_dbname sys
> > > master_wh 01
> > > master_imei
> > > master_pcn 07833631121
> > > master_sim 89441000000194834156
> > > master_pcn_data
> > > master_pcn_fax
> > > master_pcn_video
> > > master_status CONFIRMED
> > > master_weight
> > > location_key -4423
> > > master_po_no_key 007682
> > > master_order_key 111186
> > > master_country UK
> > > master_station CRW
> > > user_stamp uksysxrb
> > > time_stamp 2000-02-24 12:10:00
> > > master_supplier 5651
> > > master_customer 6152462
> > > man_pallet_id COL0176112
> > > man_batch_no COL0176112
> > > man_product 3
> > > man_extra1
> > > man_extra2
> > > man_extra3 VFBULK
> > > master_date_in 1999-11-24 15:42:35
> > > master_date_out
> > >
> > > When querying sysmaster:syslocks:-
> > >
> > > dbsname tabname rowidlk keynum type Owner Waiter
> > > dev sn_master 0 0 IX 1441
> > > dev sn_master 4355 0 X 1441
> > > dev sn_master 4355 4 X 1441
> > > dev sn_master 4355 4 X 1441
> > > dev sn_master 4355 9 X 1441
> > > dev sn_master 4355 9 X 1441
> > > dev sn_master 4355 11 X 1441
> > > dev sn_master 4355 11 X 1441
> > >
> > > TABLE SCHEMA
> > > ============
> > >
> > > create table sn_master
> > > (
> > > master_key serial not null ,
> > > pallet_key integer not null ,
> > > batch_key integer not null ,
> > > master_code char(20) not null ,
> > > extra_product char(20) not null ,
> > > master_dbname char(8) not null ,
> > > master_wh char(2) not null ,
> > > master_imei char(20),
> > > master_pcn char(20),
> > > master_sim char(20),
> > > master_pcn_data char(20),
> > > master_pcn_fax char(20),
> > > master_pcn_video char(20),
> > > master_status char(20),
> > > master_weight decimal(12,4),
> > > location_key integer not null ,
> > > master_po_no_key char(14),
> > > master_order_key char(14),
> > > master_country char(2),
> > > master_station char(3),
> > > user_stamp char(8),
> > > time_stamp datetime year to second,
> > > master_supplier char(8),
> > > master_customer char(8),
> > > man_pallet_id char(20),
> > > man_batch_no char(20),
> > > man_product char(20),
> > > man_extra1 char(20),
> > > man_extra2 char(20),
> > > man_extra3 char(20),
> > > master_date_in datetime year to second,
> > > master_date_out datetime year to second
> > > );> > >
> > > create unique index sn_master_idx1 on sn_master (master_key);
> > > create index sn_master_idx2 on sn_master (man_pallet_id,master_key);
> > > create index sn_master_idx3 on sn_master (man_product);
> > > create unique index sn_master_idx4 on sn_master (master_code);
> > > create index sn_master_idx5 on sn_master (batch_key
> desc,master_key);
> > > create index sn_master_idx6 on sn_master (pallet_key
> desc,master_key);
> > > create index sn_master_idx7 on sn_master
> > > (master_dbname,master_po_no_key,extra_product,master_key);
> > > create index sn_master_idx8 on sn_master
> > > (master_dbname,master_order_key,extra_product,master_key);
> > > create index sn_master_idx9 on sn_master
> > > (master_dbname,master_wh,ext