Delete an uncommitted inserted row in DB2 (V8.2.7 - Fix 14)

Question

Upon client's request, I was asked to turn a web application on read-uncommitted isolation level (it's a probably a bad idea...).

While testing if the isolation was in place, I inserted a row without committing (DBVisualiser : @set autocommit off + stop VPN connection to the database) and I started testing my application towards that uncommitted insert.

select * from MYTABLE WHERE MY ID = "NON_COMMIT_INSERT_ID" WITH UR is working fine. Now I would like to "delete" this row and I did not find any way...

UPDATE : The row did disappear after some time (about 30min). I guess there is some kind of timeout before a rollback is automatically issued. Is there any way to remove an uncommitted row before this happens ?

Answer 1

I think that this will not be possible using normal SQL statements - the only way to delete the row will be to rollback the transaction which inserted it (or wait for tx to commit, then delete). As you have disconnected from DB on network level, then 30 minutes you talk about is probably TCP timeout enforced on operating system level. After TCP connection has been terminated, DB2 rollbacked client's transaction automatically.

Still I think you could administratively force application to disconnect from database (using FORCE APPLICATION with handle obtained from LIST APPLICATIONS) which should rollback the transaction, see http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0001951.htm for details on these commands.

Answer 2

It's one thing to read uncommitted rows from a data base. There are sometimes good reasons (lack of read locks) for doing this,

It's another to leave inserted, updated, or deleted rows on a data base without a commit or roll back. You should never do this. Either commit or roll back after a database change.