Firebird supports the following transaction isolation levels:
1 (read committed, the default),
3 (serializable)
4 (versioning).
Firebird implements row-level locking in all cases.
Firebird performs optimistic locking. Your transaction does not attempt to lock a record until you issue an update operation that affects that record. This means that it is possible, though rare, for your update to fail because another client has locked the record, even if you started your transaction before that other client.
Firebird uses a unique versioning engine to achieve a granularity finer than that provided by traditional row-level locking. The versioning engine allows any number of clients to read a consistent copy of any given record, even if at the same time another client is updating that same row. Readers and writers never block one another, and the Firebird database engine maintains these record versions transparently as far as the client is concerned.
Support is also provided for two phase commit transactions across different Firebird databases. There is a restriction that only up to 10 databases can be used simultaneously in a two phase commit transaction. If you should need to use a two phase commit transaction then it is it is necessary to use the following call:
This call creates a common connection, to cancel the common connection:
Firebird ODBC by default uses one transaction per connection, however programatically you can use a more flexible transaction stucture. For example you can use multiple transactions within one connection, where one connection, can simultaneously be using a number of read/write transactions. It is also possible to use independent connections to different Firebird databases to carry out a two phase commit transaction across multiple Databases.
For more details of how to do this and for other advanced topics please look at the examples.