Accuracer: The First And Only BDE Alternative
Client-Server Single-File Embedded Database for Delphi / C++Builder / Kylix
Introduction
Transaction is the logical sequence of the database modification operations that can be treated as an atomic unit of work. Transactions have the following properties (ACID):
Atomicity
A transaction allows for the grouping of one or more changes to tables and rows in the database to form an atomic or indivisible operation. That is, either all of the changes occur or none of them do. If for any reason the transaction cannot be completed, everything this transaction changed can be restored to the state it was in prior to the start of the transaction via a rollback operation.
Consistency
Transactions always operate on a consistent view of the data and when they end always leave the data in a consistent state. Data may be said to be consistent as long as it conforms to a set of invariants, such as no two rows in the customer table have the same customer id and all orders have an associated customer row. While a transaction executes these invariants may be violated, but no other transaction will be allowed to see these inconsistencies, and all such inconsistencies will have been eliminated by the time the transaction ends.
Isolation
To a given transaction, it should appear as though it is running all by itself on the database. The effects of concurrently running transactions are invisible to this transaction, and the effects of this transaction are invisible to others until the transaction is committed.
Durability
Once a transaction is committed, its effects are guaranteed to persist even in the event of subsequent system failures. Until the transaction commits, not only are any changes made by that transaction not durable, but are guaranteed not to persist in the face of a system failure, as crash recovery will rollback their effects.
Transactions implementation in Accuracer
Accuracer supports transactions only for disk databases. In-Memory tables cannot be involved in the transaction.
All data modified by the transaction is stored in RAM, so if some failure will occurs duing the transaction processing all modifications will be lost and database will be in the same state as before starting the transaction. The database file can be corrupted only if failure occurs during the commit processing.
Multiple transactions on the same database file can be performed simultaneosly only if they are created in different sessions.
See Multi-User and Multi-Thread Support topic to learn more about sessions in Accuracer.
All modifications made by the transaction cannot be accessed by other sessions until commit will be finished.
Transaction can be finished by performing Commit or Rollback. The Commit tries to write all changes made by the transaction to the database file and after that unlocks all tables involved in the transaction.
The Commit by default flushes file buffers after writing changes, so all data will be saved to the file immediately.
Optionally Commit can skip this process that works much faster than with flushing.
The Rollback discards all changes, removes all pages added during the transaction and after that unlocks all tables involved in the transaction.
All tables in the database opened before starting the tranasction or during its processing are automatically becomes involved in the transaction. It means that they are locked in S mode (See LockingMechansim topic) and cannot be modified by other sessions.
If the transaction modifies some table it lock this table in IRW mode that means that other sessions cannot start Insert, Delete or Edit operations on this table and SQL statements INSERT, UPDATE, DELETE.
During the commit a transaction tries to set RW lock to all modified tables, and raises an exception if failed, so other sessions cannot read records from these tables.
Isolation level
The only isolation level in Accuracer is READ COMMITTED. It means that all changes made by the transaction cannot be viewed by other sessions until commit will be finished.
Executing a transaction
A transaction can be executed in two ways:
1) Using TACRDatabase component - methods StartTransaction, Commit, Rollback
2) Executing SQL statements START TRANSACTION, COMMIT, ROLLBACK
Transactions demo shows both of these methods.
Do not forget about handling an exceptions during the transaction processing: you should run Rollback manually if execption will be raised. Exceptions can be caused by impossibility to lock tables or by other reasons like constraints violation.
How the transactions increases the performance
The transaction locks all tables opened by current session and keeps these locks until Commit or Rollback will be called. Thus each table involved in the transaction cannot be modified by other sessions, so there is no need to re-read data from the database file and all changes are saved only during the Commit process, not after each single operation.
The maximum performance can be achieved by opening tables in Exclusive mode and running a transaction.
Even reading records works much faster inside the transaction.
Operations incompatible with transactions
All operations that requires Exclsuive access to the table or database cannot be performed when transaction is started.
However, all tables that are not invloved in the transaction cannot be accessed in Exclusive mode.
Here is a list of operations that are incompatible with transactions:
- Repairing database, Compacting database or Chaning database settings
- Emptying, Restructuring, Deleting and Renaming tables involved in the transaction
- Creating and dropping indexes on tables involved in the transaction
|