Hi
I've got some problem with the situation below.
･MVCC mode
･Auto Commit off
The problem is after being updated from two different sessions the row
is gone.
I will state this as sequentially.
1. Create table, initial data and unique index as below.
>SELECT * FROM SATOU
ID |NAME
----|-----
1 |10000
2 |8000
3 |4000
4 |-3000
5 |2000
>SELECT * FROM INFORMATION_SCHEMA.INDEXES;
|TABLE_CATALOG |TABLE_SCHEMA |TABLE_NAME |NON_UNIQUE |INDEX_NAME
|MEMDB1 |PUBLIC |SATOU |FALSE |PRIMARY_KEY_4
|MEMDB1 |PUBLIC |SATOU |FALSE |UNIQUE_IDX
2.Updated a row without commit from one session(session1).
>update satou set name = '7777' where id = 1;
ID |NAME
----|--------
1 |7777
2 |8000
3 |4000
4 |-3000
5 |2000
3. This time the table is shown like this from another
session(session2. Same as before surely)
>SELECT * FROM SATOU
ID |NAME
----|--------
1 |10000
2 |8000
3 |4000
4 |-3000
5 |2000
4. Updated a row without commit from one session(session2).
See id is different while the value of the name is the same
>update satou set name = '7777' where id = 5;
Successfully updated
>SELECT * FROM SATOU
ID |NAME
----|------
1 |10000
2 |8000
3 |4000
4 |-3000
Even though the row I've tried to updated was gone(id = 5).
I looked at source code and realized what had happened.
When session1 updates the row it puts the row into the base index and
the delta index in MulitVersionIndex.
At first session2 removes the row where id = 5 when it updates.
Next even though session2 tries to add the row into the base index in
MulitVersionIndex first, it throws "Unique index or primary key
violation" because the base index already has the row where name =
"7777".
This exception is caught by catch block in a addRow method of
RegularTable class and if this database is run with MVCC mode, delta
index in MultiVersionIndex will be checked at
isUncommittedFromOtherSession method of MultiVersionIndex class.
This method re-throw DbException which has "CONCURRENT_UPDATE_1" error
code because the row session2 is trying to update has the same value
as the row session1 has updated just before and not yet committed.
This DbException will be caught again at the executeUpdate method of
the Command class. But after filterConcurrentUpdate method is executed
it try to execute update method again within the while unlimited block
because filterConcurrentUpdate method re-throw the exception when the
exception it caught is NOT CONCURRENT_UPDATE_1.
Although session2 tries to update the row, it cannot find the target
because it has been already removed and finishes the statement
normally.
That's why the row is gone in this situation.
I modified the source code little bit in order to fix this, which is
not perfect because when you run this with the same situation it
throws "Unique index or primary key violation" exception while the
"Timeout trying to lock table" exception is expected.
After this modification, this transaction tries to update the row
again and again, when it finally throws “time out exception”
transaction tries to execute addRow method for ROLLBACK but couldn't
because of this row has already recovered...
I am assuming that adding the removing undo log process before
throwing “CONCURRENT_UPDATE_1” fixes this though.
ⅰ. Add getDeltaIndex method into MulitVersionIndex.
public Row getDeltaRow(Session session, long key) {
Cursor cursor = delta.findFirstOrLast(session, true);
Row deltaRow = null;
while(cursor.next()){
deltaRow = cursor.get();
if(key == deltaRow.getKey()
&& session.getId() ==
deltaRow.getSessionId()){
break;
}
}
return deltaRow;
}
ⅱ. Put recovery logic into addRow method of RegularTable class
public void addRow(Session session, Row row) {
・・・
if (mv.isUncommittedFromOtherSession(session, row)) {
//add from here
long key = row.getKey();
Row oldRow = mv.getDeltaRow(session, key);
try {
for (int k = 0; k < indexes.size(); k++) {
Index idx = indexes.get(k);
idx.add(session, oldRow);
checkRowCount(session, idx, 0);
}
} catch (DbException e3) {
trace.error(e3, "could not undo operation");
throw e3;
}
//added to here
throw DbException.get(ErrorCode.CONCURRENT_UPDATE_1,
index.getName());
}}}}
throw de;
}
analyzeIfRequired(session);
}
Sorry for long explanation. I really appreciate if you could have time
to think about this situation.
If you need additional information please let me know.
Thanks!
Teruo
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
