How do I use transactions in Firebird?

Question

In MS SQL Server, I can easily put multiple insert statements into a transaction, like so:

begin tran
insert into mytable values (1, 2, 3)
insert into mytable values (4, 5, 6)
commit tran

I'm trying to do the same thing in Firebird, but I can't figure out the syntax. Googling for "Firebird transaction syntax" returns nothing useful. I've found enough to know that transaction support exists, but no examples for how to use it right.

So I figure I may as well ask on here. Does anyone know how to write a transaction using multiple inserts for a Firebird database?

Answer 1

Complementing @Allan's answer (which I upvoted, BTW), here's some more information.

When you do begin tran in SQL Server, it does not mean that you're starting the transaction now. You are already in transaction, since you are connected to the database! What begin tran really does is disable the "auto-commit at each statement", which is the default state in SQL Server (unless otherwise specified).

Respectively, commit tran commits and reverts the connection to "auto-commit at each statement" state.

In any database, when you are connected, you are already in transaction. This is how databases are. For instance, in Firebird, you can perform a commit or rollback even if only ran a query.

Some databases and connection libs, in the other hand, let you use the "auto-commit at each statement" state of connection, which is what SQL Server is doing. As useful as that feature might be, it's not very didactic and lead beginners to think they are "not in a transaction".

Answer 2

Firebird always uses transactions. The transaction is started as soon as you make a change in the database and remains open for that session until you commit. Using your code, it's simply:

insert into mytable values (1, 2, 3);
insert into mytable values (4, 5, 6);
commit;

Answer 3

Since FB 2.5 it's possible to start a new transaction from inside the current one.

IN AUTONOMOUS TRANSACTION
DO
  < simple statement | compound statement >

http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-psql-auton