8

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?

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
Mason Wheeler
  • 82,511
  • 50
  • 270
  • 477
  • 1
    As stated by others, Firebird always work inside transactions. There are different transaction isolations, but probably you are facing a tool feature that force the commit after each statement. I would try that in [ISQL](http://www.destructor.de/firebird/isql.htm) or [FlameRobin](http://www.flamerobin.org/). HTH (; – EMBarbosa Jul 02 '11 at 13:44

4 Answers4

8

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".

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
6

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;
Allan
  • 17,141
  • 4
  • 52
  • 69
  • 1
    This doesn't work properly. If I say "rollback" instead of "commit" at the end of the block, the values are still in the table. – Mason Wheeler Jun 06 '11 at 20:09
  • They should not be! What tool are you using to interface with the DB? – Adriano Carneiro Jun 06 '11 at 20:13
  • 2
    Maybe your connection/lib is set to auto-commit? Take a look at my answer. – Adriano Carneiro Jun 06 '11 at 20:15
  • @Adrian I'm testing this out in Firebird Maestro. So are you saying that this is a tool-specific issue? – Mason Wheeler Jun 06 '11 at 20:25
  • 1
    I do not know this tool, so I wouldn't know. Have you tried running this code using other tool? Say, the shipped-with-firebird-command-line ISQL? Also, there's a personal version of IBExpert, which is just great. I think you tool is isolating you from the concepts here described. – Adriano Carneiro Jun 06 '11 at 21:10
  • 1
    @Mason - try to do that in isql - if you using Firebird Maestro it can automaticlly commit after every sql statement, so you can see no effect. http://forums.devshed.com/firebird-sql-development-61/set-transaction-541679.html – JustMe Jun 07 '11 at 07:52
  • 2
    @Masion, ow and check you transaction isolation level! – JustMe Jun 07 '11 at 07:58
  • What happens if I need to SELECT some value and then update the table based on that? If transactions start only when I change something, then I could potentially update the table based on an obsolete value? – Escape Velocity Jun 14 '16 at 12:05
  • Isn't a new transaction also started if you explicitly issue a `rollback`? – Wolf Aug 06 '19 at 12:02
4

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

Fr0sT
  • 2,959
  • 2
  • 25
  • 18
  • The word *enter* seems a bit misleading here, it reads like you are invading another running transaction. Instead it's more like stacking a new (temporary) transaction upon the running one. – Wolf Sep 19 '18 at 07:53
  • 1
    @Wolf thanks for the note, I modified the sentence. Hope now it's more clear – Fr0sT Sep 19 '18 at 08:16
  • 1
    it's better now. But as I see now, it's also important to understand the *within a PSQL module* part. Normally transactions cannot be accessed from within a stored procedure, or trigger. – Wolf Sep 19 '18 at 14:11
0

If Firebird there is no need for extra syntax beyond

commit;

Below I provide screenshot from Firebird session showing how it works.

scrrenshot of isql

FabienAndre
  • 4,514
  • 25
  • 38
Amy
  • 35
  • 12
  • your screenshot is really bad, it is cut exactly where it gets interesting, take a look yourself :) – Wolf Sep 18 '18 at 10:51
  • 1
    @Wolf I just update my screenshot please take a look,whether it is helpful or not – Amy Sep 18 '18 at 11:31
  • the screenshot is much better now, maybe it's even better to embed it into the answer... – Wolf Sep 19 '18 at 07:42
  • ...before embedding, decrease the image width of it. The scrollbar on the right and the black void between it and the text isn't important. – Wolf Sep 19 '18 at 07:47