Hello,

I get that the real solution is to not have a transaction open that long.
I'm working on getting some long transaction detection built into our
product. We support PostgreSQL as well, so while having something like that
in the database would be nice I'd like to have it be the same across any
supported database for us. Until we can find and address long transactions,
any thoughts on the PageStore changes? We are going with the changes I
posted above for now but I am concerned that I am missing something.

Thanks,
Ken



On Fri, Jan 23, 2015 at 3:00 AM, Thomas Mueller <
[email protected]> wrote:

> 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 a topic in the
> Google Groups "H2 Database" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/h2-database/BBlAZ3BajaM/unsubscribe.
> To unsubscribe from this group and all its topics, 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