Re: Row Level Locking via JDBC

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 > 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 to fetch > next row.". > > Normally, this could't be right with row level locking. > > Soeren