Can't delete records

[cdprete]

Hi.
From time to time, my delete statements fails with the error below:

Caused by: org.h2.mvstore.MVStoreException: Writing to sun.nio.ch.FileChannelImpl@983e85b failed; length 1814528 at 2145886208 [2.2.224/2]
at org.h2.mvstore.DataUtils.newMVStoreException(DataUtils.java:996)
at org.h2.mvstore.DataUtils.writeFully(DataUtils.java:478)
at org.h2.mvstore.SingleFileStore.writeFully(SingleFileStore.java:67)
at org.h2.mvstore.SingleFileStore.writeFully(SingleFileStore.java:28)
at org.h2.mvstore.RandomAccessStore.writeChunk(RandomAccessStore.java:366)
at org.h2.mvstore.RandomAccessStore.writeChunk(RandomAccessStore.java:28)
at org.h2.mvstore.FileStore.storeBuffer(FileStore.java:1524)
at org.h2.mvstore.FileStore.lambda$serializeAndStore$3(FileStore.java:1432)
at org.h2.mvstore.FileStore.submitOrRun(FileStore.java:1401)
at org.h2.mvstore.FileStore.serializeAndStore(FileStore.java:1432)
at org.h2.mvstore.FileStore.lambda$storeIt$2(FileStore.java:1376)
at org.h2.mvstore.FileStore.submitOrRun(FileStore.java:1401)
at org.h2.mvstore.FileStore.storeIt(FileStore.java:1375)
at org.h2.mvstore.MVStore.storeNow(MVStore.java:843)
at org.h2.mvstore.MVStore.store(MVStore.java:808)
at org.h2.mvstore.MVStore.tryCommit(MVStore.java:744)
at org.h2.mvstore.MVStore.beforeWrite(MVStore.java:1275)
at org.h2.mvstore.MVMap.beforeWrite(MVMap.java:968)
at org.h2.mvstore.MVMap.append(MVMap.java:1379)
at org.h2.mvstore.db.MVPlainTempResult.addRow(MVPlainTempResult.java:81)
at org.h2.mvstore.db.MVTempResult.addRows(MVTempResult.java:199)
at org.h2.result.LocalResult.addRowsToDisk(LocalResult.java:443)
at org.h2.result.LocalResult.addRowInternal(LocalResult.java:434)
at org.h2.result.LocalResult.addRowForTable(LocalResult.java:398)
at org.h2.command.dml.Delete.update(Delete.java:77)

And the statement is a simple:

DELETE FROM FUTURE_PRICES WHERE INSERTION_TIMESTAMP <= ? [50000-224]

[katzyn]

You need to check cause of this MVStoreException.

Maybe your application calls Thread.interrupt() on this thread (don't do that with persistent embedded databases unless you use the async: file system abstraction layer) or maybe there is no enough disk space.

[cdprete]

Why would disk size matter here? I'm deleting records, therefore I'm reclaiming space. :)

The thread would be interrupted only in case of errors, but there aren't any apart that one.

[manticore-projects]

Greetings.

From my understanding, even a DELETE will occupy (much!) additional table space first because the transaction will need to be held somewhere (including index maintenance) while building before it gets committed (or rolled back).

You may be better of by
a) CTAS

CREATE TABLE tmp_future_prices  AS SELECT * FROM  FUTURE_PRICES WHERE INSERTION_TIMESTAMP > ? 

followed by DROP TABLE ... and ALTER TABLE ... RENAME ...

b) or partitioning the work by using intervals:

 DELETE FROM FUTURE_PRICES WHERE INSERTION_TIMESTAMP BETWEEN ?1 AND ?2;

and then executing this query many times by shifting the interval.

[katzyn]

Why would disk size matter here?

Because database systems have transactions and their isolation levels. If you've deleted something in one transaction, other transactions may still need this removed data. All DML operations usually increase disk space usage, at least for some time. If your case an I/O error appeared even before the actual deletion. Delete operation constructs list of rows to delete and they also need some temporary space if this list is too large to fit into memory, but this space is released after execution of this command.

If you want to remove all rows from a table and you don't care about concurrent transactions, a TRUNCATE TABLE command is significantly more efficient.