|Subject||Re: Which isolation level to use?|
> I'm a newbie on FB, I'm confused with isolation level to use inWhen I am driving, I can go east or west. What is the best practice?
> transaction. What is the best practice?
It depends on where I am, and where I want to be. The best practice is
to use the one that best suits the problem you are solving. You really
must understand transactions if you want to understand how Firebird
works. They are an integral part of the database, not a last minute
tack-on. That means you need to understand that isolation is about
allowing concurrent transactions to have their own view of the
database without stepping on each others toes.
For example, a snapshot transaction (as its name suggest) sees the
database as it existed at the time the transaction started combined
with whatever changes it made itself. If another transaction adds a
record and commits, your snapshot transaction will not see it even
after the commit. If another transaction deletes a record and commits,
your transaction will still see it. If another transaction modifies a
record, your transaction will still see the old version of that record
A read-committed transaction sees the database as it existed at the
time the transaction started, combined with whatever changes it made
itself, combined with any committed changes made by anyone else.
In practice, a snapshot transaction allows you to generate a report
and not worry about what happens if some of the data is changed during
that generation. Many databases have to use locks to prevent
conflicting changes, or they force you to use a 'dirty read' where
your report is potentially nonsense.
A read-committed transaction is better when you need to see the latest
version of records all the time (quite often used in a read-only
'The Firebird Book' by Helen Borrie is a valuable resource for a
developer using Firebird (buy it through ibphoenix.com to support the
project), which has over 50 pages on transactions alone. If you are
doing more than a casual glance of Firebird, it will pay for itself
with time saved.