Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Mark Rotteveel]

On 30-5-2015 10:49, Maxim Smyatkin wrote:
> Oh, and it worth mentioning - I tried it only with latest FB 2.5

It breaks with Firebird 3 (a snapshot of last week). The test 
TestFBDatabaseMetaData.testGetProcedureColumns hangs when executed 
against Firebird 3: the entire server hangs.

The test setup uses an explicit transaction start and commit, but never 
disables auto commit. The setup creates a stored procedure and adds a 
comment to the stored procedure. It looks like this fails when adding 
the comment with isc_tpb_autocommit (the server stops responding 
entirely with SuperServer).

Disabling autocommit makes the test pass.

> On Sat, May 30, 2015 at 11:33 AM, Mark Rotteveel  > wrote:
>
> On 30-5-2015 10:21, Maxim Smyatkin wrote:
> > Surely I would like to see the same optimization in Jaybird 3, but I'm
> > currently more interested in 2.2.*.
>
> I understand and it will end up in 2.2, just not in 2.2.8 ;). This
> change has some implications that I need to understand better before I
> can release it.

-- 
Mark Rotteveel

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Jim Starkey]

On 5/31/2015 5:33 AM, Dimitry Sibiryakov wrote:
> 31.05.2015 9:17, Mark Rotteveel wrote:
>>
>> The problem is probably that there is still an active transaction at
>> that point, where before it was already committed or rolled back.
> Prepared statements are not bound to a transaction, they (and I believe 
> their existence
> locks) exists till explicitly unprepared/freed or connection ends.
>

By design, compiled BLR blocks can have any number of instantiations; 
BLR runtime data is expressed in the compiled statement as an offset 
into an impure area that is part of the statement instantiation.  I 
don't remember if we ever got around to caching compiled statements for 
cross connection use, but that was part of the design and implementation.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Dmitry Yemanov]

31.05.2015 12:33, Dimitry Sibiryakov wrote:

> Prepared statements are not bound to a transaction, they (and I believe their 
> existence
> locks) exists till explicitly unprepared/freed or connection ends.

Existence locks are bound to *both* statement and transaction lifetime.


Dmitry


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Dimitry Sibiryakov]

31.05.2015 9:17, Mark Rotteveel wrote:
> On 30-5-2015 21:54, Maxim Smyatkin wrote:
>> Yes, it makes sence. I was wrong as the procedure still depends on the
>> table, so it's the intented behaviour. But what I still can't understand
>> is why did it work before? I mean, old tests were using Jaybird's
>> AutoCommitTransaction which is just a wrapper around Manageable
>> transaction, so it should give the same results. And my playing around
>> transactions supports it:
>
> The problem is probably that there is still an active transaction at
> that point, where before it was already committed or rolled back.

   Prepared statements are not bound to a transaction, they (and I believe 
their existence 
locks) exists till explicitly unprepared/freed or connection ends.

-- 
   WBR, SD.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Mark Rotteveel]

On 30-5-2015 21:54, Maxim Smyatkin wrote:
> Yes, it makes sence. I was wrong as the procedure still depends on the
> table, so it's the intented behaviour. But what I still can't understand
> is why did it work before? I mean, old tests were using Jaybird's
> AutoCommitTransaction which is just a wrapper around Manageable
> transaction, so it should give the same results. And my playing around
> transactions supports it:

The problem is probably that there is still an active transaction at 
that point, where before it was already committed or rolled back.

Mark
-- 
Mark Rotteveel

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Maxim Smyatkin]

Yes, it makes sence. I was wrong as the procedure still depends on the
table, so it's the intented behaviour. But what I still can't understand is
why did it work before? I mean, old tests were using Jaybird's
AutoCommitTransaction which is just a wrapper around Manageable
transaction, so it should give the same results. And my playing around
transactions supports it:

*cleanup...*

*In auto-commit transaction...*
*Successfully created table*
*Successfully created procedure*
*unsuccessful metadata update*
*-cannot delete*
*-COLUMN TEST_BLOB.ID *
*-there are 1 dependencies*
*Successfully dropped procedure*

*cleanup...*

*In manageable transaction...*
*Successfully created table*
*Successfully created procedure*
*Successfully dropped table*
*Successfully dropped procedure*

*cleanup...*

*In wrapped transaction...*
*Successfully created table*
*Successfully created procedure*
*Successfully dropped table*
*unsuccessful metadata update*
*-cannot delete*
*-COLUMN TEST_BLOB.ID *
*-there are 1 dependencies*
*Successfully dropped procedure*

*cleanup...*

Anyway, it looks like the current behaviour is actually the right one. But
it differs from the previous one.

On Sat, May 30, 2015 at 7:09 PM, Dmitry Yemanov  wrote:

> 29.05.2015 17:10, Maxim Smyatkin wrote:
> >
> > - we create a procedure P using a table T.
> > - the P states its interest in the T (while looking for P's
> dependencies);
> > - transaction commits the changes with retaining flag and forgets to say
> > that it isn't interested in the T anymore;
>
> Externally (for API user) it's the same transaction (even if having
> different ID), so it *is* still interested. If you restart some query
> against T in the same transaction, you should not get a "table not
> found" error. This is the idea behind existence locks.
>
>
> Dmitry
>
>
>
> --
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>



-- 
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin
#include 
#include 
#include 

using namespace std;

const char* db_name = "localhost:fbtest";

isc_db_handle attach();
bool detach(isc_db_handle* db);
bool executeStatement(const char* query, isc_tr_handle* tr, isc_db_handle* db, bool quiet = false);
isc_tr_handle startManageableTransaction(isc_db_handle* db);
isc_tr_handle startAutoCommitTransaction(isc_db_handle* db);
bool commit(isc_tr_handle* tr);
bool checkError(ISC_STATUS* status_vector, bool quiet = false);
void tryManageable();
void tryWrapped();
void tryAutocommit();
void cleanUp();

const char* CREATE_TABLE = "CREATE TABLE test_blob("
   "  id INTEGER, "
   "  bin_data BLOB, "
   "  char_data BLOB SUB_TYPE 1  "
   ")";

const char* DROP_TABLE = "DROP TABLE test_blob";

const char* CREATE_PROCEDURE =
"CREATE PROCEDURE test_procedure(id INTEGER, char_data BLOB SUB_TYPE 1) "
"AS BEGIN "
"  INSERT INTO test_blob(id, char_data) VALUES (:id, :char_data);"
"END";

const char* DROP_PROCEDURE = "DROP PROCEDURE test_procedure";

int main(void) {
cleanUp();
tryAutocommit();
cleanUp();
tryManageable();

// now in different order - to be 100% sure
/*cleanUp();
tryManageable();
cleanUp();
tryAutocommit();*/

cleanUp();
tryWrapped();
cleanUp();
}

void cleanUp() {
cout << endl << "cleanup..." << endl;
isc_db_handle db = attach();
isc_tr_handle tr = startManageableTransaction(&db);
executeStatement(DROP_TABLE, &tr, &db, true);
executeStatement(DROP_PROCEDURE, &tr, &db, true);
commit(&tr);
detach(&db);
}

void tryManageable() {
cout << endl << "In manageable transaction..." << endl;

isc_db_handle db = attach();
isc_tr_handle tr = startManageableTransaction(&db);
if (executeStatement(CREATE_TABLE, &tr, &db)) {
cout << "Successfully created table" << endl;
}
if (executeStatement(CREATE_PROCEDURE, &tr, &db)) {
cout << "Successfully created procedure" << endl;
}
if (executeStatement(DROP_TABLE, &tr, &db)) {
cout << "Successfully dropped table" << endl;
}
if (executeStatement(DROP_PROCEDURE, &tr, &db)) {
cout << "Successfully dropped procedure" << endl;
}
commit(&tr);
detach(&db);
}

void tryWrapped() {
cout << endl << "In wrapped transaction..." << endl;

isc_db_handle db = attach();
isc_tr_handle tr = startManageableTransaction(&db);
if (executeStatement(CREATE_TABLE, &tr, &db)) {
cout << "Successfully created table" << endl;
}
commit(&tr);
if (!tr) tr = startManageableTransaction(&db);
if (executeStatement(CREATE_PROCEDURE, &tr, &db)) {
cout << "Successfully created procedure" << endl;
}
commit(&tr);
if (!tr) tr = st

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Dmitry Yemanov]

29.05.2015 17:10, Maxim Smyatkin wrote:
>
> - we create a procedure P using a table T.
> - the P states its interest in the T (while looking for P's dependencies);
> - transaction commits the changes with retaining flag and forgets to say
> that it isn't interested in the T anymore;

Externally (for API user) it's the same transaction (even if having 
different ID), so it *is* still interested. If you restart some query 
against T in the same transaction, you should not get a "table not 
found" error. This is the idea behind existence locks.


Dmitry


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Vlad Khorsun]

29.05.2015 17:10, Maxim Smyatkin wrote:

> 2. Whole TestFBBlobStream family (6 tests) fails. The reason is bug in 
> Firebird transaction engine. When transaction started in
> auto-commit mode actually commits (via commit_retaining) it doesn't free any 
> resources. E.g., what happens in these tests is:
> - we create a procedure P using a table T.
> - the P states its interest in the T (while looking for P's dependencies);
> - transaction commits the changes with retaining flag and forgets to say that 
> it isn't interested in the T anymore;
> - we try to drop the T, but we can't because it is marked as "in use"!
> I attached a patch (for 2.5 branch) to release resources on commit_retaining. 
> But I believe the problem is rather in
> modify_procedure()'s code, because it should release rel_use_counts on its 
> own.

   Open cursors are left open after commit_retaining. Therefore i think it is
not correct to release existence locks as part of commit_retaining.

Regards,
Vlad

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Maxim Smyatkin]

Oh, and it worth mentioning - I tried it only with latest FB 2.5

On Sat, May 30, 2015 at 11:33 AM, Mark Rotteveel 
wrote:

> On 30-5-2015 10:21, Maxim Smyatkin wrote:
> > Surely I would like to see the same optimization in Jaybird 3, but I'm
> > currently more interested in 2.2.*.
>
> I understand and it will end up in 2.2, just not in 2.2.8 ;). This
> change has some implications that I need to understand better before I
> can release it.
>
> Mark
> --
> Mark Rotteveel
>
>
> --
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>



-- 
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin
--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Mark Rotteveel]

On 30-5-2015 10:21, Maxim Smyatkin wrote:
> Surely I would like to see the same optimization in Jaybird 3, but I'm
> currently more interested in 2.2.*.

I understand and it will end up in 2.2, just not in 2.2.8 ;). This 
change has some implications that I need to understand better before I 
can release it.

Mark
-- 
Mark Rotteveel

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Maxim Smyatkin]

Yes, that's right. Commit_retaining doesn't release acquired resources and
it shouldn't. But I still believe it's a bug in the Firebird itself,
because procedure after creation should decrement relation use count, but
it doesn't. Any of the steam blob tests fail even when executed separetely.
Surely I would like to see the same optimization in Jaybird 3, but I'm
currently more interested in 2.2.*.

On Sat, May 30, 2015 at 10:58 AM, Mark Rotteveel 
wrote:

> On 29-5-2015 16:10, Maxim Smyatkin wrote:
> > Hello,
> > Recently we've got an information from some company that they would like
> > to use Firebird/Red Database with OpenCMS, but they couldn't afford it
> > because of bad performance. We compared it with PostgreSQL and it was
> > true - on some page views PG showed 4 seconds while for Firebird it was
> > almost 27 seconds. Skimming through performance audit log explained us,
> > that CMS executes lots (thousends) of small selects per page view. I
> > agree that it seems like a horrible design, but anyway PG handled it
> > much faster.
> > The reasons were:
> > 1. In auto-commit mode Jaybird wraps any statement into start/commit
> > calls. So, it does two additional calls to DBMS per statement;
> > 2. It doesn't really matter if the statement changes anything or not. It
> > executes start/commit in both cases.
> > Here is a pull request with changes to fix this behaviour using DBMS
> > API's auto-commit flag:
> > https://github.com/FirebirdSQL/jaybird/pull/1
> > Any further information about implementation or results is in comment to
> > the pull request.
> >
> > It seems to work excellent with OpenCMS, but Jaybird's unit tests show
> > several problems:
> >
> > 1. testBatch shows failure, because now every statement in the batch
> > execution mode is commited separately. This behaviour is perfectly fine
> > as JDBC specification states that you can't rely on batch mode +
> > auto-commit mode. Maybe, Firebird has some clients who rely on this
> > behaviour, I don't know. Anyway, you can wrap batch execution into
> > freshly started non auto-commit transaction, if you wish. I decided to
> > leave this decision up to you and didn't touch it.
>
> I am not sure if others depend on this behavior, but I'd prefer to keep
> the behavior of this as it was. Your suggestion to wrap it in its own
> transaction seems a good one, but it might be complex to implement (a
> lot of Jaybird is built around a "single active transaction" model of
> transactions.
>
> > 2. Whole TestFBBlobStream family (6 tests) fails. The reason is bug in
> > Firebird transaction engine. When transaction started in auto-commit
> > mode actually commits (via commit_retaining) it doesn't free any
> > resources. E.g., what happens in these tests is:
> > - we create a procedure P using a table T.
> > - the P states its interest in the T (while looking for P's
> dependencies);
> > - transaction commits the changes with retaining flag and forgets to say
> > that it isn't interested in the T anymore;
> > - we try to drop the T, but we can't because it is marked as "in use"!
> > I attached a patch (for 2.5 branch) to release resources on
> > commit_retaining. But I believe the problem is rather in
> > modify_procedure()'s code, because it should release rel_use_counts on
> > its own.
>
> As far as I know the keeping resources like the cursor and blobs open on
> commit_retaining is intentional (I believe it is one of the reasons for
> the "retaining" in the name). The error itself is probably related to
> the fact that some Jaybird tests unnecessarily cleanup after themselves.
>
> > 3. TestDriverConnectionPoolDataSource.testPreparedStatement fails with
> > an Error. I don't know why it happens and how to fix it. Looks like some
> > kind of race conditions, but I'm not sure. If anyone could look into it,
> > it would be awesome.
>
> I'll look into it. I hadn't noticed before that you made the pull
> request for Branch_2_2, I will first release Jaybird 2.2.8 (this
> weekend) before looking at it.
>
> >
> > Here is my commit for OpenCMS, so that it could effectively use modified
> > Jaybird:
> >
> https://github.com/Smyatkin-Maxim/opencms-core/commit/4b1901f4d339c4ec710ad84d316e70f694068dc8
> > I will issue pull request as soon as I verify that this Jaybird
> > improvement is fine.
>
> Mark
>
> --
> Mark Rotteveel
>
>
> --
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>



-- 
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin
--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Mark Rotteveel]

On 29-5-2015 16:10, Maxim Smyatkin wrote:
> Hello,
> Recently we've got an information from some company that they would like
> to use Firebird/Red Database with OpenCMS, but they couldn't afford it
> because of bad performance. We compared it with PostgreSQL and it was
> true - on some page views PG showed 4 seconds while for Firebird it was
> almost 27 seconds. Skimming through performance audit log explained us,
> that CMS executes lots (thousends) of small selects per page view. I
> agree that it seems like a horrible design, but anyway PG handled it
> much faster.
> The reasons were:
> 1. In auto-commit mode Jaybird wraps any statement into start/commit
> calls. So, it does two additional calls to DBMS per statement;
> 2. It doesn't really matter if the statement changes anything or not. It
> executes start/commit in both cases.
> Here is a pull request with changes to fix this behaviour using DBMS
> API's auto-commit flag:
> https://github.com/FirebirdSQL/jaybird/pull/1
> Any further information about implementation or results is in comment to
> the pull request.
>
> It seems to work excellent with OpenCMS, but Jaybird's unit tests show
> several problems:
>
> 1. testBatch shows failure, because now every statement in the batch
> execution mode is commited separately. This behaviour is perfectly fine
> as JDBC specification states that you can't rely on batch mode +
> auto-commit mode. Maybe, Firebird has some clients who rely on this
> behaviour, I don't know. Anyway, you can wrap batch execution into
> freshly started non auto-commit transaction, if you wish. I decided to
> leave this decision up to you and didn't touch it.

I am not sure if others depend on this behavior, but I'd prefer to keep 
the behavior of this as it was. Your suggestion to wrap it in its own 
transaction seems a good one, but it might be complex to implement (a 
lot of Jaybird is built around a "single active transaction" model of 
transactions.

> 2. Whole TestFBBlobStream family (6 tests) fails. The reason is bug in
> Firebird transaction engine. When transaction started in auto-commit
> mode actually commits (via commit_retaining) it doesn't free any
> resources. E.g., what happens in these tests is:
> - we create a procedure P using a table T.
> - the P states its interest in the T (while looking for P's dependencies);
> - transaction commits the changes with retaining flag and forgets to say
> that it isn't interested in the T anymore;
> - we try to drop the T, but we can't because it is marked as "in use"!
> I attached a patch (for 2.5 branch) to release resources on
> commit_retaining. But I believe the problem is rather in
> modify_procedure()'s code, because it should release rel_use_counts on
> its own.

As far as I know the keeping resources like the cursor and blobs open on 
commit_retaining is intentional (I believe it is one of the reasons for 
the "retaining" in the name). The error itself is probably related to 
the fact that some Jaybird tests unnecessarily cleanup after themselves.

> 3. TestDriverConnectionPoolDataSource.testPreparedStatement fails with
> an Error. I don't know why it happens and how to fix it. Looks like some
> kind of race conditions, but I'm not sure. If anyone could look into it,
> it would be awesome.

I'll look into it. I hadn't noticed before that you made the pull 
request for Branch_2_2, I will first release Jaybird 2.2.8 (this 
weekend) before looking at it.

>
> Here is my commit for OpenCMS, so that it could effectively use modified
> Jaybird:
> https://github.com/Smyatkin-Maxim/opencms-core/commit/4b1901f4d339c4ec710ad84d316e70f694068dc8
> I will issue pull request as soon as I verify that this Jaybird
> improvement is fine.

Mark

-- 
Mark Rotteveel

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Moving Jaybird auto-commit implementation into Firebird

[Mark Rotteveel]

Thank you for the pull request! I haven't looked at it closely yet (will do 
that this weekend), but I think it's great. I have some concerns regarding the 
commit demarcation as compared to the requirements of jdbc, but I will come 
back at that in comments on the pull request.
Mark

- Reply message -
Van: "Maxim Smyatkin" 
Aan: 
Onderwerp: [Firebird-devel] Moving Jaybird auto-commit implementation into 
Firebird
Datum: vr, mei 29, 2015 16:10

Hello,Recently we've got an information from some company that they would like 
to use Firebird/Red Database with OpenCMS, but they couldn't afford it because 
of bad performance. We compared it with PostgreSQL and it was true - on some 
page views PG showed 4 seconds while for Firebird it was almost 27 seconds. 
Skimming through performance audit log explained us, that CMS executes lots 
(thousends) of small selects per page view. I agree that it seems like a 
horrible design, but anyway PG handled it much faster.
The reasons were:
1. In auto-commit mode Jaybird wraps any statement into start/commit calls. So, 
it does two additional calls to DBMS per statement;
2. It doesn't really matter if the statement changes anything or not. It 
executes start/commit in both cases.
Here is a pull request with changes to fix this behaviour using DBMS API's 
auto-commit flag:
https://github.com/FirebirdSQL/jaybird/pull/1
Any further information about implementation or results is in comment to the 
pull request.

It seems to work excellent with OpenCMS, but Jaybird's unit tests show several 
problems:

1. testBatch shows failure, because now every statement in the batch execution 
mode is commited separately. This behaviour is perfectly fine as JDBC 
specification states that you can't rely on batch mode + auto-commit mode. 
Maybe, Firebird has some clients who rely on this behaviour, I don't know. 
Anyway, you can wrap batch execution into freshly started non auto-commit 
transaction, if you wish. I decided to leave this decision up to you and didn't 
touch it.

2. Whole TestFBBlobStream family (6 tests) fails. The reason is bug in Firebird 
transaction engine. When transaction started in auto-commit mode actually 
commits (via commit_retaining) it doesn't free any resources. E.g., what 
happens in these tests is:
- we create a procedure P using a table T.
- the P states its interest in the T (while looking for P's dependencies);
- transaction commits the changes with retaining flag and forgets to say that 
it isn't interested in the T anymore;
- we try to drop the T, but we can't because it is marked as "in use"!
I attached a patch (for 2.5 branch) to release resources on commit_retaining. 
But I believe the problem is rather in modify_procedure()'s code, because it 
should release rel_use_counts on its own.

3. TestDriverConnectionPoolDataSource.testPreparedStatement fails with an 
Error. I don't know why it happens and how to fix it. Looks like some kind of 
race conditions, but I'm not sure. If anyone could look into it, it would be 
awesome.

Here is my commit for OpenCMS, so that it could effectively use modified 
Jaybird: 
https://github.com/Smyatkin-Maxim/opencms-core/commit/4b1901f4d339c4ec710ad84d316e70f694068dc8
I will issue pull request as soon as I verify that this Jaybird improvement is 
fine.

-- 
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel