Hello,

I have problems to set up Row Level Locking for my DB Connect.
Following Source Code produces the error code:

"Could not do a physical-order read to fetch next row."

Here is the sql statement for the table.

CREATE TABLE Dummy (
ID integer NOT NULL,
Counter integer
) LOCK MODE ROW;

And here is the part of the program.

{
st = conn1.createStatement();
st.executeUpdate("insert into dummy values (1, 1)");

st = conn1.createStatement();
st.executeUpdate("insert into dummy values (2, 2)");

conn1.commit();
conn2.commit();


st = conn1.createStatement();
st.executeUpdate("update dummy set counter = 3 where id = 1");

st = conn2.createStatement();
st.executeUpdate("update dummy set counter = 3 where id = 2");

conn1.commit();
conn2.commit();
}

Maybe you can help me.

Thanks in advance
Soeren Ehm

On Tue, 18 May 2004 08:55:15 -0400, S?ren Ehm wrote:

CREATE TABLE Dummy (
ID integer NOT NULL,
Counter integer
) LOCK MODE (ROW); -- Note the parenthesis!

Art S. Kagel

More precisely, I set row level locking in the table dummy.

CREATE TABLE Dummy (
ID integer NOT NULL,
Counter integer
) LOCK MODE ROW;

The focus is laying on the following update statements. Why I can't
update different rows in the same table by two connections which
simulating two clients.

conn1.setAutoCommit(false);
conn2.setAutoCommit(false);

st = conn1.createStatement();
st.executeUpdate("update dummy set counter = 3 where id = 1");

st = conn2.createStatement();
st.executeUpdate("update dummy set counter = 3 where id = 2");

conn1.commit();
conn2.commit();

I don't understand, why I get the error message
- "Could not do a physical-order read to fetch next row.".

Normally, this could't be right with row level locking.

Soeren

On Wed, 19 May 2004 03:25:12 -0400, S?ren Ehm wrote:

Ahh, different question. The phrase 'physical-order read' is the give-away.
The table has no index so the engine has to performa sequential scan of the
table to find rows to update. The default ANSI isolation level requires that
all rows that are visited have a shared lock placed on them and the rows
actually being updated have that lock promoted to an exclusive lock. The
problem is that one session/command has a shared lock on row#1 so that the
other session cannot promote its lock to exclusive mode. If you add an index
(probably should be a UNIQUE index given how you are using it) on the ID
column the problem will disappear.

Art S. Kagel

you will get the error message if the query does a sequential scan on the
table, do the following from dbaccess to verify this
1)run the following statement from "Query-Language"
set explain on;
select * from where id = 1;

2) check the sqexplain.out file and see if the optimizer does a sequential
scan on the table

you can avoid this error by doing the following,
1) creating an index on id, like
create index idx1 on dummy(id);

2) if the dummy table is small, use the INDEX optimizer directives to force
the optimizer to pick the index, like
update {+ index(dummy idx1) } dummy set counter = 3 where id = 1



-----Original Message-----
From: owner-informix-list (AT) iiug (DOT) org
[mailtowner-informix-list (AT) iiug (DOT) org]On Behalf Of soeren.ehm (AT) evodion (DOT) de
Sent: Wednesday, May 19, 2004 2:25 AM
To: informix-list (AT) iiug (DOT) org
Subject: Re: Row Level Locking via JDBC


More precisely, I set row level locking in the table dummy.

CREATE TABLE Dummy (
ID integer NOT NULL,
Counter integer
) LOCK MODE ROW;

The focus is laying on the following update statements. Why I can't
update different rows in the same table by two connections which
simulating two clients.

conn1.setAutoCommit(false);
conn2.setAutoCommit(false);

st = conn1.createStatement();
st.executeUpdate("update dummy set counter = 3 where id = 1");

st = conn2.createStatement();
st.executeUpdate("update dummy set counter = 3 where id = 2");

conn1.commit();
conn2.commit();

I don't understand, why I get the error message
- "Could not do a physical-order read to fetch next row.".

Normally, this could't be right with row level locking.

Soeren
sending to informix-list