Transactions with Firebird 3.0.8
Stéphanie Cortelli
I would like to know if the handling of transactions has changed with the version 3.0.8 of Firebird.
With the version 3.0.5 I was able to manage transactions with: a unique transaction for inserting many records into a main table; and when some field needed to create a new record in a linked table (foreign key) I used single insertions with a different transaction immediately committed. In this way the foreign keys were respected during the insertion.
With version 3.0.8 the transaction on the main table does not seem to see the insertions made outside of it after the start of the transaction and therefore gives me an error for violation of foreign key, while the records have already been inserted and commited separately on the database.
I solved it by entering everything with the same transaction. However, I preferred the previous solution, because I can have multiple identical occurrences in secondary tables and as new records are not seen as long as the transaction is in progress, I've to use UPDATE OR INSERT INTO, it works correctly, but inevitably some records of secondary tables are inserted and then updated many times and it is unnecessary.
I cannot use a CommitRetaining because it is imperative that the block is inserted in full or not at all.
It's not a big problem, but I would like to understand why this behavior differs between version 3.0.5 and 3.0.8.
The exact same executable works with FB 3.0.5 and gives foreign key violation error on FB 3.0.8, importing the exact same data, if I don't apply the solution to do everything in one main transaction.
Sometimes I have ad hoc functions in my Pascal code to insert records into secondary tables, so it would be good if I could keep using them instead of managing everything with a global transaction even for the secondary tables.
For me it is not a problem if the new records of the secondary tables are commited even if the main transaction is rolled back.
Has anyone had the same experience?
I've tested this under Linux environnement (Ubuntu 22.04 with FB 3.0.8 from repositories and Debian Buster with FB 3.0.5 from repositories).
I haven't tested with FB 3.0.10 yet.
Thanks for any reply.
Sorry for my bad English.
Best regards,
Stephanie
Stanislav Hruška
It is probably important for you: all newly created data can be read within one transaction! Even without confirming the transaction. They are not readable for other transactions until confirmed.
Stéphanie Cortelli
Hello Stanislav,
and thanks for your reply!
Yes, I know that inserts within a transaction are considered
even before committing, within the same transaction.
However, sometimes I find useful to have separate procedures
for entering some data, which require special checks.
It is true that I can handle the code differently, do the
checks, and then insert all into the unique transaction.
However, I cannot understand why the single entries that have
been entered and committed separately between the Start and
the Commit of the main transaction are not seen.
I don't know if the opposite could also happen, if for example
a data is deleted with a separate transaction, if this is not
seen within the main transaction, there could be a violation
of a foreign key. It's not my case however.
I would just like to understand if the Firebird transaction
management has somehow changed with version 3.0.8, I can still
solve by doing everything in the main transaction, I just
can't understand why this change and if this could in some how
to affect the security and integrity of the database.
Thank you again, best regards,
Stephanie
--
You received this message because you are subscribed to a topic in the Google Groups "firebird-support" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebird-support/d5TMni7sRsU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/41d660dc-7f8d-4982-88de-5c0a25b9f542n%40googlegroups.com.
Dimitry Sibiryakov
> However, I cannot understand why the single entries that have been entered and
> committed separately between the Start and the Commit of the main transaction
> are not seen.
Concurrency?
--
WBR, SD.
Stéphanie Cortelli
Hello Dimitry,
thanks for your reply, yes the transaction is setted to Concurrency.
The UIB components su access to FB have Concurrency as default mode. Anyway I've now setted explicitly by code to Concurrency, but nothing change.
Thank you again and best regards,
Stephanie
Dimitry Sibiryakov
> thanks for your reply, yes the transaction is setted to Concurrency.
>
> The UIB components su access to FB have Concurrency as default mode. Anyway I've
> now setted explicitly by code to Concurrency, but nothing change.
in other transactions.
--
WBR, SD.
Stéphanie Cortelli
Thanks for your reply, it
has been very useful to me.
Now everything is clearer to
me and I'm thinking what to do.
Setting the transaction as
ReadCommited could be a solution, even if from some tests it
seems to be slower, especially when there is a lot of data to
enter.
Keeping the transaction as
Concurrency with the workaround I had done to put everything
inside the transaction would be another solution.
It is still not clear to me
why with FB 2.5.X and FB 3.0.5 it also worked with
transactions set to Concurrency, but the important thing is to
have understood the problem.
Thanks again for your help!
Best regards,
Stephanie
Mark Rotteveel
> It is still not clear to me why with FB 2.5.X and FB 3.0.5 it also
> worked with transactions set to Concurrency, but the important thing is
> to have understood the problem.
your app on Firebird 2.5 is using read committed.
Mark
--
Mark Rotteveel
Stéphanie Cortelli
Hello Mark,
and thanks for your reply.
Yes, it shouldn't work but it did. I had this procedure, that used separated und committed transaction for insert in secondary tables, for years, that worked until upgrade to FB 3.0.8.
Some days ago, a customer
has upgraded from FB 2.5.X to FB 3.0.8 on his server. I've
also installed with Virtual Box a XUbuntu 22.04 LTS with
Firebird 3.0.8, in order to have the same environnement. When
the customer has launched the procedure became error and have
called me. And testing the procedure on the virtual machine
with FB 3.0.8 I had the seme problem.
The same executable on my Debian Buster with FB 3.0.5 works, as it worked to my customer on FB 2.5.X. However this same executable copied to the machines with FB 3.0.8, with the same data to insert, raises exception (foreign key violation).
So I've made a workaround in
order to insert all data with the same transaction. So it
works.
May be my fault that I din't
think enough to the type of transaction and used the default
value. But why it worked until FB 3.0.5 I cannot understand.
Best regards,
Stephanie