What is a transaction?
Transaction is an atomic unit of work on database. It's a sequence of actions (various SQL statements) that ends with either COMMIT, ROLLBACK or breaking of client's connection.
If you commit the transaction, all your changes become valid in the database and other users can see it under a few rules:
- those who have READ COMMITED transations, see it immediately
- those who have SNAPSHOT transactions, must start a new transaction
- those who had DIRTY READ transaction were able to see the changes as you made them (even before commiting)
If you rollback, all your changes are undone, except GENERATORS and EXTERNAL TABLES which are outside of transaction control.
If the connection breaks, transaction remains active until a new one comes and decides that the old one is invalid and rolls it back. Sometimes it can happen that some transaction's state is undetermined, and it's called a limbo transaction. This may happen with transactions that span multiple databases. You need to use the gfix tool either option -t for two phase recovery or -c and -r options to commit or rollback such transactions manually.
It is important that you keep your transactions as short as possible since bad transaction management is a cause for many performance problems: big database grow, slow queries, user lockups, etc.