Transactions

Delphi Database, Delphi Components from ComponentAce

Transactions

Previous Top Next

Transaction

A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction.

Controlling Transactions

Applications control transactions mainly by specifying when a transaction starts and ends. This can be specified using either TABSDatabase methods or SQL statements. An example of code with transaction use:

begin
with MyABSDatabase do
begin
StartTransaction;
try
// Multiple table updates or some other actions
{ ... }
Commit;
except
Rollback;
end;
end;
end;

START TRANSACTION
You can start transactions in Absolute Database through either an TABSDatabase.StartTransaction call or the START TRANSACTION statement of Absolute Database SQL.

You can end transactions with either a COMMIT or ROLLBACK statement.

COMMIT
If a transaction is successful, commit it. A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. A COMMIT also frees resources, such as locks, used by the transaction.
You can commit transaction through either an TABSDatabase.Commit call or the COMMIT statement of Absolute Database SQL.
By default the Commit flushes data changes to disk. If you would like to improve the performance, it is recommended to set DoFlushBuffers parameter to false, allowing OS to perform lazy writes on disk.

ROLLBACK
If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. A ROLLBACK also frees resources held by the transaction.
You can roll back transaction through either an TABSDatabase.Rollback call or the ROLLBACK statement of Absolute Database SQL. It is also recommended to resresh open tables to avoid displaying data which is rolled back now, but make sure that there are no pending inserts or edits on these tables.

Transaction Locks

Absolute Database engine acquires table and record locks during the transaction. Unlike the usual locks, transaction locks remain unchanged after table modification until the end of transaction. These table locks don't prevent other users from reading locked table records, increasing concurrency level.

Deadlocking

A deadlock occurs when there is a cyclic dependency between two or more threads for some set of resources.
Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized.

Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

·	Rolled back, undoing all the work performed by the transaction.

·	Resubmitted by applications because they were rolled back when deadlocked.

To help minimize deadlocks:

·	Access objects in the same order.

·	Avoid user interaction in transactions.

·	Keep transactions short and in one batch.

SQL in Transactions

INSERT, UPDATE and DELETE SQL statements implicitly use an internal kind of transaction if one is not already active.
SELECT peforms share locks on all processed tables, but this lock is freed as soon as SELECT satement is executed.

Operations Not Allowed in Transactions

It is not permitted in transactions to perfrom:

Create table (CreateTable method, SQL CREATE TABLE)
Delete table (DeleteTable method, SQL DROP TABLE)
Empty table (EmptyTable method)
Copy table (CopyTable method)
Rename table (RenameTable method, SQL ALTER TABLE)
Restructure table (RestructureTable method, SQL ALTER TABLE)
RenameField in a table (RenameField method, SQL ALTER TABLE)
Add index to table (AddIndex method, SQL CREATE INDEX)
Delete index from table (DeleteIndex, DeleteAllIndexes methods, SQL DROP INDEX)

Isolation Level

Transaction isolation level determines how a transaction interacts with other simultaneous transactions when they work with the same tables, and how much a transaction sees of the work performed by other transactions.
Absolute Database supports ReadCommited level.
ReadCommitted permits reading of committed (permanent) changes made to the database by other simultaneous transactions.

Data Integrity

Absolute Database buffers in memory all changes made by transaction until commit or rollback.
If trransaction is commited, then all changes are written by the short operation. This allows to keep a good level of data integrity even in case of unexpected power loss or network problems.
If however the process of writing changes was interrupted, for example in such cases when very large trahsactions are performed and commiting process was legthy, the Absolute Database engine will detect if writing changes was interrupted and will suggest an auto-repair of a database file.

Jun 11, 2023