Hi,

I could now fix last part of the problem ("An old transaction with the same
id is still open"). This was a strange limitation in the MVStore
transaction engine.

Regards,
Thomas

On Sat, Jan 17, 2015 at 5:26 PM, Ken Jorissen <[email protected]>
wrote:

>
> On Monday, January 12, 2015 at 2:27:42 PM UTC-6, Ken Jorissen wrote:
>>
>>
>> On Monday, January 5, 2015 at 2:58:17 AM UTC-6, Noel Grandin wrote:
>>>
>>> Hi
>>>
>>> Thanks for your problem report and investigation.
>>>
>>> I have committed a fix for this.
>>>
>>> Regards, Noel.
>>>
>>
>> The current logic still doesn't reset logSizeBase and I think that is
>> key. I also think that there are two "happy path" tests and the first one
>> is unnecessary because the second one will always be true if the first one
>> is. I think PageStore.commit() should look like:
>>
>> ---
>>
>> public synchronized void commit(Session session) {
>>
>>         checkOpen();
>>
>>         openForWriting();
>>
>>         log.commit(session.getId());
>>
>>         long size = log.getSize();
>>
>>         if (size - logSizeBase > maxLogSize / 2) {
>>
>>             checkpoint();
>>
>>             logSizeBase = log.getSize();
>>
>>             if (logSizeBase < size || size < maxLogSize) {
>>
>>                 ignoreBigLog = false;
>>
>>                 return;
>>
>>             }
>>
>>             if (!ignoreBigLog) {
>>
>>                 ignoreBigLog = true;
>>
>>                 trace.error(null,
>>
>>                         "Transaction log could not be truncated; size: "
>> +
>>
>>                         (newSize / 1024 / 1024) + " MB");
>>
>>             }
>>
>>         }
>>
>>     }
>>
>> ---
>> The above gets you the original behavior (r4138) back with the only
>> addition being the one-shot logging with that one-shot being reset when the
>> "happy path" happens.
>>
>>
> I wrote a program (it is attached) that starts a transaction on one table
> and then does a series of inserts on the second. With that and the tracing
> cranked up you can see the checkpoint behavior I am concerned about.
>
> I used this to analyze the trace file:
> ---
> awk '/\/\*SQL/ {sql=$0} /checkpoint/ {print substr(sql,0,60); print $0}
> /could not be truncated/ {print $0}' test.trace.db | less -S
> ---
>
> That will write out the last SQL statement before each checkpoint, the
> checkpoint itself, and the truncation trace line.
>
>
> In the implementation as of 1.3.174 you get these ranges of behavior
> assuming a transaction is pending, log.size() is growing during this -
> ---
> log.size() <= maxLogSize/2              : no checkpoint
> maxLogSize/2 < log.size() < maxLogSize  : checkpoint for every commit
> maxLogSize < log.size() < ~2*maxLogSize : no checkpoints - gap here
> because baseLogSize was reset, "Transaction log could not be truncated" is
> logged at the start
> ~2*maxLogSize < log.size()              : checkpoint for every commit
> ---
>
> For that test case the logging looks like this -
> First checkpoint, and then one for every insert:
> ---
> /*SQL #:1*/insert into test2 values(31058, '1234567890123456
> 01-17 09:03:56 pageStore: checkpoint
> /*SQL #:1 t:1*/insert into test2 values(31059, '123456789012
> 01-17 09:03:57 pageStore: checkpoint
> ---
>
> That continues up until the ignoreBigLog flag is set, logBaseSize is
> updated, and the trace message is written out:
> ---
> /*SQL #:1*/insert into test2 values(32839, '1234567890123456
> 01-17 09:03:58 pageStore: checkpoint
> /*SQL #:1*/insert into test2 values(32840, '1234567890123456
> 01-17 09:03:58 pageStore: checkpoint
> 01-17 09:03:58 pageStore: Transaction log could not be truncated; size: 16
> MB
> ---
>
> There are no more commits until the page log grows enough again, then you
> get them for every commit:
> ---
> /*SQL #:1*/insert into test2 values(63727, '1234567890123456
> 01-17 09:04:01 pageStore: checkpoint
> /*SQL #:1 t:1*/insert into test2 values(63728, '123456789012
> 01-17 09:04:01 pageStore: checkpoint
> ---
>
> That is the original performance problem.
>
> With the 1.4.185 it is better, it now resets logBaseSize one you are over
> maxLogSize but the earlier range still does a checkpoint for every commit:
> ----
> log.size() <= maxLogSize/2              : no checkpoint
> maxLogSize/2 < log.size() < maxLogSize  : checkpoint for every commit
> maxLogSize < log.size()                 : no checkpoints - gap here
> because baseLogSize was reset, "Transaction log could not be truncated" is
> logged at the start
> ---
>
> With the changes I suggested in my above post against 1.3.174, the run
> analyzed the same way looks like this:
> ---
> /*SQL #:1*/insert into test2 values(31058, '1234567890123456
> 01-17 09:38:10 pageStore: checkpoint
> /*SQL #:1*/insert into test2 values(61968, '1234567890123456
> 01-17 09:38:14 pageStore: checkpoint
> 01-17 09:38:14 pageStore: Transaction log could not be truncated; size: 16
> MB
> /*SQL #:1*/insert into test2 values(92848, '1234567890123456
> 01-17 09:38:17 pageStore: checkpoint
> /*SQL */COMMIT;
> 01-17 09:38:18 pageStore: checkpoint
> /*SQL */COMMIT;
> 01-17 09:38:19 pageStore: checkpoint
> ---
> That is a single checkpoint at intervals of maxLogSize/2 with one warning.
>
> Side note, using 1.4.185 without MV_STORE=false throws an exception and
> fails:
> ---
> org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
> insert into test2 values(65529,
> '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
> [50200-185]
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
> at org.h2.message.DbException.get(DbException.java:168)
> Caught: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL
> statement:
> insert into test2 values(65529,
> '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
> [50200-185]
> at org.h2.command.Command.filterConcurrentUpdate(Command.java:301)
> at org.h2.command.Command.executeUpdate(Command.java:256)
> at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:184)
> at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
> at biglogtest.BigLogTest.main(BigLogTest.java:29)
> Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table
> "TEST2": another transaction has updated or deleted the same row [90131-185]
> at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
> at org.h2.message.DbException.get(DbException.java:168)
> at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:145)
> at org.h2.mvstore.db.MVTable.addRow(MVTable.java:637)
> at org.h2.command.dml.Insert.insertRows(Insert.java:156)
> at org.h2.command.dml.Insert.update(Insert.java:114)
> at org.h2.command.CommandContainer.update(CommandContainer.java:78)
> at org.h2.command.Command.executeUpdate(Command.java:254)
> ... 3 more
> Caused by: java.lang.IllegalStateException: An old transaction with the
> same id is still open: 14 [1.4.185/102]
> at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:773)
> at org.h2.mvstore.db.TransactionStore.log(TransactionStore.java:265)
> at
> org.h2.mvstore.db.TransactionStore$Transaction.log(TransactionStore.java:689)
> at
> org.h2.mvstore.db.TransactionStore$TransactionMap.trySet(TransactionStore.java:1085)
> at
> org.h2.mvstore.db.TransactionStore$TransactionMap.set(TransactionStore.java:1008)
> at
> org.h2.mvstore.db.TransactionStore$TransactionMap.put(TransactionStore.java:986)
> at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:143)
> ... 8 more
> ---
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Reply via email to