--- In firebird-support@yahoogroups.com, Diego Barros <dbarros@m...>
wrote:

> Hi all,
>
> I have a couple of questions regarding transactions and FB. When I

do anything in a Delphi/FB application, I always have to have a
StartTransaction and Commit/Rollback? Even for a SELECT statement?

Firebird has multi-version records. So if I run the following query

insert into employee (id, name) values (gen_id(gen_employeeid,1),
'Diego');

And then I run:

select name from employee

I will see 'Diego' in my list. Another transaction will not see that
value because I have not yet run a commit.

While a select statement can't change the value of a table (unless it
is selecting from a stored procedure), Firebird will return you the
information as it was at the time you begin the transaction. This
allows firebird to maintain consistency and isolation (aCId).

>I noticed that when I was inserting a record in the database, it was

being done correctly (I was using transactions), but when I did a
select it was not being returned.

>

My guess is that a different transaction inserted the data, and you
will not see it until you commit. The default destroy action on most
if not all of the delphi actions is to commit, which is why when you
close the form, the data suddenly appears.

You should read up on Isolation. It is really powerful but can be
confusing if you are not used to multiversion records.
http://cegt201.bradley.edu/projects/proj2003/equiprd/acid.html

> I've seen some code samples on the newsgroups which didn't have a

StartTransaction call, but just a Commit after executing some SQL or
stored proc. Is this OK to do? Or must I always use the start,
commit/rollback combination?

I believe that StartTransaction will be internally called when you run
the first query. Sometimes you might want to explicitly call it, it
really depends on what you are trying to do, but you shouldn't need to.

>Do I need transactions when using a stored procedure component and

calling ExecProc() too?

Yes, for the same reason as it is used for select.

Hope that helps

Adam