Jaybird 3 - DDL - AutoCommit

[radek...@centrum.cz]

Hello,
After I did upgrade to Firebird3 (Jaybird3), I noticed strange problem with DDL statements on auto commit connection (no problem was registered till I used Firebird2 and Jaybird 2).

I have following scenario working with the same table (the connections are acquired from Apache DBCP pool in a reality):

Connection con1 = getConnection();
Connection con2 = getConnection();
createTable(con1);
selectFromTable(con1);
dropTable(con2);

The result is that dropTable(con2) method is never finished.

To make it running, it is enough to use "manual" commits, like this:

Connection con1 = getConnection(); con1.setAutoCommit(false);
Connection con2 = getConnection(); con2.setAutoCommit(false);
createTable(con1);
con1.commit()
selectFromTable(con1);
con1.commit()
dropTable(con2);
con2.commit();

I am very sorry, but I don't really understand, why auto commit mode cannot be used and why I have no problem in Firrebird2/Jaybird2.
I have already read https://firebirdsql.github.io/jaybird-manual/jaybird_manual.html#_auto_commit_mode. But I didn't catch the reason, why auto commit does not work in this case.
The simple test case is attached (without using DataSource for simplicity).

Thank you for any hint.
Radek Kraus.

[Mark Rotteveel]

You changed two things together, so it is a bit hard to attribute the
problem to either Jaybird or Firebird. What happens if you use Jaybird
2.2 with Firebird 3 or Jaybird 3 with Firebird 2?

In any case, the problem might be related to
http://tracker.firebirdsql.org/browse/JDBC-638 or
http://tracker.firebirdsql.org/browse/JDBC-637, together with how
Firebird locks metadata objects when a statement is prepared.

However, it would be helpful to have full reproduction program and not
just an outline. Could you create one and send it to the list, or create
a ticket in the tracker and upload it there?

Mark
--
Mark Rotteveel

[Arioch The]

> selectFromTable(con1);
> con1.commit()
> dropTable(con2);

Just guessing from common sense...

1) i suspect it is not about FB2 vs FB3 but about superserver vs
classic server (that is, shared/isolated metadata cache).
I suspect that your FB2 is one of them, and FB3 is another, and that
just coincided with server and Jaybird version

2) i suspect it is about the same as having two execution threads and
trying to delete a file while another thread is being reading from it
and holding it open

3) anyways, DDL autocommit is irrelevant here, since SELECT is not
DDL, it obviously belongs to DML
Accordingly, i expect the quoted above commit is the only thing that
makes the actual difference and other commits do not (assuming you
really DO have DDL autocommit on)

4) hoping you use Firebird 2.5 and above (you did not specify exact
version of fb2) there should be TraceAPI available in the server,
which lets you see all queries and transactions as they come and go to
the server. Raw database interaction, below all those layers of your
application, Jaybird, JDBC, Java. If you want to investigate the
server's behaviour then perhaps it would be the most accurate
information to work from.
TraceAPI can be used by console utility fbtracemgr or by GUI IDEs like
trial IBExpert or FLOSS http://fbprofiler.sf.net and perhaps others.
I expect you would be able to make two trace logs, with FB2 and FB3
and then just compare them line by line and see if there is or is no
difference.

[radek...@centrum.cz]

Thank you for quick reaction.

>You changed two things together, so it is a bit hard to attribute the
>problem to either Jaybird or Firebird. What happens if you use Jaybird
>2.2 with Firebird 3 or Jaybird 3 with Firebird 2?

I tried the Jaybird 3 with Firebird 2 too. I got the same result:
Database Product: Firebird 2.5 (LI-V2.5.9.27139)
JDBC Driver: Jaybird JCA/JDBC driver (3.0)/JDBC Driver: Jaybird JCA/JDBC driver (4.0.2)

Database Product: Firebird 3.0 (LI-V3.0.5.33220)
JDBC Driver: Jaybird JCA/JDBC driver (3.0)/Jaybird JCA/JDBC driver (4.0.2)


Only when I use older version of FB2, I got following "exception" from dropTable(con2) method (instead of stuck):
Resource Exception. unsuccessful metadata update; object TABLE "TEST" is in use [SQLState:42000, ISC error code:335544351]
Database Product: Firebird 2.1 (LI-V2.1.7.18553)
JDBC Driver: Jaybird JCA/JDBC driver (3.0)/Jaybird JCA/JDBC driver (4.0.2)

>However, it would be helpful to have full reproduction program and not
>just an outline. Could you create one and send it to the list, or create
>a ticket in the tracker and upload it there?

I have already attached simple tests in my first message (DDLAutommit.java), sorry for typo ;)
Java class contains 2 test methods, which can be used for reproduction (I hope it is enough, or?):
- testDDLAutoCommit()
- testDDLManualCommit()
I didn't want to create issue in tracker directly without discussion here, but yes I can create a ticket of course, when attached test is enough.

Radek.
______________________________________________________________
> Od: "Mark Rotteveel" <ma...@lawinegevaar.nl>
> Komu: firebi...@googlegroups.com
> Datum: 19.03.2021 18:21
> Předmět: Re: [firebird-java] Jaybird 3 - DDL - AutoCommit

>--
>You received this message because you are subscribed to the Google Groups "firebird-java" group.
>To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.
>To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/89d05c4c-3f59-93b6-25d8-53c426daedf0%40lawinegevaar.nl.
>
>

[radek...@centrum.cz]

Finally, I tried to build SNAPSHOT version of jaybird:3.0.11-SNAPSHOT directly from sources to test
the fix done in http://tracker.firebirdsql.org/browse/JDBC-638.

>In any case, the problem might be related to
>http://tracker.firebirdsql.org/browse/JDBC-638 or
>http://tracker.firebirdsql.org/browse/JDBC-637, together with how
>Firebird locks metadata objects when a statement is prepared.

And my test case works with 3.0.11-SNAPSHOT (against FB 3.0.5 and FB 2.5.9)

Thank you again for support and I am sorry that I didn't find the issue myself without need to waste your time here.
I am looking forward to jaybird:3.0.11. Do you have plan when it will be released?

Thank all.
Radek.

[Mark Rotteveel]

On 19-03-2021 21:36, radek...@centrum.cz wrote:
> Thank you for quick reaction.
>
>> You changed two things together, so it is a bit hard to attribute the
>> problem to either Jaybird or Firebird. What happens if you use Jaybird
>> 2.2 with Firebird 3 or Jaybird 3 with Firebird 2?
>
> I tried the Jaybird 3 with Firebird 2 too. I got the same result:
> Database Product: Firebird 2.5 (LI-V2.5.9.27139)
> JDBC Driver: Jaybird JCA/JDBC driver (3.0)/JDBC Driver: Jaybird JCA/JDBC driver (4.0.2)
>
> Database Product: Firebird 3.0 (LI-V3.0.5.33220)
> JDBC Driver: Jaybird JCA/JDBC driver (3.0)/Jaybird JCA/JDBC driver (4.0.2)

According to this you're using Jaybird 3 and Jaybird 4.

> Only when I use older version of FB2, I got following "exception" from dropTable(con2) method (instead of stuck):
> Resource Exception. unsuccessful metadata update; object TABLE "TEST" is in use [SQLState:42000, ISC error code:335544351]
> Database Product: Firebird 2.1 (LI-V2.1.7.18553)
> JDBC Driver: Jaybird JCA/JDBC driver (3.0)/Jaybird JCA/JDBC driver (4.0.2)

Ok, as Arioch The asked, are you using Firebird 2.1 Classic or
SuperClassic and are now using Firebird 3 SuperServer? That could
explain the difference between waiting on a lock and the in use error
message.

However, the big difference between Jaybird 2.2 and Jaybird 3 is that
the wire protocol was reimplemented and enhanced with newer features.
The problem you're describing seems to be
http://tracker.firebirdsql.org/browse/JDBC-638, or at least it goes away
when using your reproduction test with a Jaybird 4.0.3 snapshot.

When implementing these changes, I followed the implementation of the
native client, which includes a number of optimizations like not
immediately sending certain messages from client to server, but instead
waiting for other activity before sending them. And that not immediately
sending is what causes the problem.

The auto-commit occurs on statement completion (when you finish reading
the result set or close the result set, whichever comes first), followed
by the close of the statement. This close is not flushed as there is no
other activity on the connection, so serverside the statement is still
prepared holding locks on metadata objects.

When you use explicit commit, you commit after closing the statement, so
the close of the statement is flushed to the server, and the locks on
metadata objects are released.

>> However, it would be helpful to have full reproduction program and not
>> just an outline. Could you create one and send it to the list, or create
>> a ticket in the tracker and upload it there?
>
> I have already attached simple tests in my first message (DDLAutommit.java), sorry for typo ;)
> Java class contains 2 test methods, which can be used for reproduction (I hope it is enough, or?):
> - testDDLAutoCommit()
> - testDDLManualCommit()
> I didn't want to create issue in tracker directly without discussion here, but yes I can create a ticket of course, when attached test is enough.

Sorry, I had entirely missed the attachment. I can confirm it reproduces
the problem, and also that it goes away after testing with a 4.0.3
snapshot version and 3.0.11 snapshot version that includes the fix for
JDBC-638.

If you want, I can provide you with a snapshot of those versions. The
only change compared to 3.0.10 / 4.0.2 is this fix.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 20-03-2021 08:45, radek...@centrum.cz wrote:
> Finally, I tried to build SNAPSHOT version of jaybird:3.0.11-SNAPSHOT directly from sources to test
> the fix done in http://tracker.firebirdsql.org/browse/JDBC-638.
>
>> In any case, the problem might be related to
>> http://tracker.firebirdsql.org/browse/JDBC-638 or
>> http://tracker.firebirdsql.org/browse/JDBC-637, together with how
>> Firebird locks metadata objects when a statement is prepared.
>
> And my test case works with 3.0.11-SNAPSHOT (against FB 3.0.5 and FB 2.5.9)
>
> Thank you again for support and I am sorry that I didn't find the issue myself without need to waste your time here.
> I am looking forward to jaybird:3.0.11. Do you have plan when it will be released?

Good to hear :)

This is definitely not a waste of my time! Bug reports are always
helpful, and so is confirmation that it was fixed.

It is a nice coincidence, just two weeks ago I stumbled across this bug,
4 years after Jaybird 3.0.0 release, and now you report that exact same
problem.

I'll see if I can release Jaybird 3.0.11 and 4.0.3 this weekend or later
next week.

Out of curiosity, is there a specific reason you're using Jaybird 3
instead of Jaybird 4?

Mark
--
Mark Rotteveel

[radek...@centrum.cz]

I think there is no specific reason for Jaybird 3 at the moment (it is why I tried to reproduce it with Jaybird 4.0.2 too).
We used FB 2.5 (and even 2.1.7) long time. And when we decided to start use FB 3, we choose Jaybird 3, probably to "minimize differences".

Radek.

> Od: "Mark Rotteveel" <ma...@lawinegevaar.nl>
> Komu: firebi...@googlegroups.com
> Datum: 20.03.2021 08:56
> Předmět: Re: [firebird-java] Jaybird 3 - DDL - AutoCommit
>

>--
>You received this message because you are subscribed to the Google Groups "firebird-java" group.
>To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.

>To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/e7e9b682-ebc4-47e8-3dff-bdea19e854b9%40lawinegevaar.nl.
>
>

[Mark Rotteveel]

On 20-03-2021 09:15, radek...@centrum.cz wrote:
> I think there is no specific reason for Jaybird 3 at the moment (it is why I tried to reproduce it with Jaybird 4.0.2 too).
> We used FB 2.5 (and even 2.1.7) long time. And when we decided to start use FB 3, we choose Jaybird 3, probably to "minimize differences".

To be clear: Jaybird 4 supports Firebird 2.5 and higher, and should work
on Firebird 1 and higher, though for example some metadata might not
work due to using newer features (especially in Firebird 1.5 and
earlier). The version numbers of Jaybird are not related to Firebird
version numbers.

The difference between Jaybird 2.2 and Jaybird 3 is gigantic due to the
reimplementation of the wire protocol, while the difference between
Jaybird 3 and Jaybird 4 is mostly incremental.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 20-03-2021 08:45, radek...@centrum.cz wrote:

> I am looking forward to jaybird:3.0.11. Do you have plan when it will be released?

It has been released just now, see
https://www.firebirdsql.org/en/news/jaybird-4-0-3-and-3-0-11-released/
and the email I sent twenty minutes ago.

Mark
--
Mark Rotteveel

[radek...@centrum.cz]

Great.

Thank you very much for all, support, additional information and for lightning speed to release new versions (3.0.11 an 4.0.3).
Radek Kraus.

> Od: "Mark Rotteveel" <ma...@lawinegevaar.nl>
> Komu: firebi...@googlegroups.com
> Datum: 20.03.2021 15:55
> Předmět: Re: [firebird-java] Jaybird 3 - DDL - AutoCommit
>

>--
>You received this message because you are subscribed to the Google Groups "firebird-java" group.
>To unsubscribe from this group and stop receiving emails from it, send an email to firebird-jav...@googlegroups.com.

>To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/03495a00-5d3a-0eb1-038a-fd0cb251d6ca%40lawinegevaar.nl.
>
>