Persistent Transactions
2013 Feb 05 Filed in: Programming
This article describes a persistent transaction mechanism for SQL databases. The discussion and examples here are specific to SQLite, but the ideas and concepts apply to most of today’s SQL based databases.
Persistent transactions can be used in place of long running transient transactions or when there is likelihood that an operation will be undone in the future. Consider, for example, a system where the user enters some data that will eventually constitute a number of records in a database. It might be convenient for the application to update the database in a transient transaction, so that it can offer a live view of any data updates to the user. However the user may still decide to abort the data entry, in which case any transient transactions will need to be undone.
Persistent transactions have names and a well-defined lifetime. A persistent transaction can be started using the beginPersistentTransaction() operation and it can be ended using the endPersistentTransaction() operation. When a persistent transaction is ended, the transaction changes may be permanently posted to the database (committed) or they may be undone (rolled back). To update the database while in a persistent transaction, one starts a transient transaction and “enters” the persistent transaction using the enterPersistentTransaction() operation. Likewise to commit (or rollback) the transient transaction changes, one “leaves” the persistent transaction and commits (or rolls back) the transient transaction using the leavePersistentTransaction() operation.
The persistent transaction mechanism maintains two special tables (per attached SQLite database). The tables are named __xactlst__ and __xactlog__. The __xactlst__ table is used to maintain information about all persistent transactions currently started in the database. The __xactlog__ table is used to keep track of the changes made while in a persistent transaction. Specifically the table contains SQL instructions that when replayed will “undo” all changes by a specific persistent transaction and restore the database to its state prior to the persistent transaction. This is done in such a way so that changes done outside of a persistent transaction are not affected.
When a persistent transaction is started with beginPersistentTransaction(xname), a record is made in the __xactlst__ table that lists the persistent transaction id, name and CREATE/DROP TRIGGER scripts. The schema of the __xactlst__ table is (as mentioned we will be using SQLite syntax):
xactid INTEGER PRIMARY KEY NOT NULL,xname TEXT UNIQUE COLLATE NOCASE,ct0sql TEXT,ct1sql TEXT,dtsql TEXT
The ct0sql, ct1sql columns contain the CREATE TRIGGER scripts and the dtsql column contains the DROP TRIGGER script. The ct1sql script is used to create triggers that should be active when the persistent transaction is in effect (i.e. entered). The ct0sql script is used to create triggers that should be active when the persistent transaction is not in effect (i.e. after leave). The dtsql script is used to drop any persistent transaction related triggers.
The __xactlog__ table has the following schema:
tabname TEXT,tabrowid INTEGER,xactid INTEGER,sql TEXT,PRIMARY KEY(tabname, tabrowid, xactid)
Suppose that a persistent transaction is started with the name “Xa” that will operate on a table named tab0 in the database db1. This is what the ct0sql script will look like:
CREATE TRIGGER [db1].[__xactitr__1_tab0] AFTER INSERT ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND tabrowid=new._rowid_ AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND tabrowid=old._rowid_ AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;CREATE TRIGGER [db1].[__xactdtr__1_tab0] AFTER DELETE ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND tabrowid=old._rowid_ AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;
This is what the ct1sql script will look like:
CREATE TRIGGER [db1].[__xactitr__1_tab0] AFTER INSERT ON [tab0] BEGIN INSERT OR IGNORE INTO __xactlog__ VALUES('tab0', new._rowid_, 1, 'DELETE FROM [db1].[tab0] WHERE _rowid_='||new._rowid_); END;CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] BEGIN INSERT OR IGNORE INTO __xactlog__ VALUES('tab0', old._rowid_, 1, 'UPDATE [db1].[tab0] SET i0='||quote(old.i0)||',t0='||quote(old.t0)||' WHERE _rowid_='||old._rowid_); END;CREATE TRIGGER [db1].[__xactdtr__1_tab0] AFTER DELETE ON [tab0] BEGIN INSERT INTO __xactlog__ VALUES('tab0', old._rowid_, 1, 'INSERT INTO [db1].[tab0] (_rowid_,i0,t0) VALUES('||old._rowid_||','||quote(old.i0)||','||quote(old.t0)||')'); END;
Finally this is what the dtsql script will look like:
DROP TRIGGER [db1].[__xactitr__1_tab0];DROP TRIGGER [db1].[__xactutr__1_tab0];DROP TRIGGER [db1].[__xactdtr__1_tab0];
Let’s examine closely the UPDATE trigger in ct1sql. This trigger is in effect when a persistent transaction is in effect:
CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] BEGIN INSERT OR IGNORE INTO __xactlog__ VALUES('tab0', old._rowid_, 1, 'UPDATE [db1].[tab0] SET i0='||quote(old.i0)||',t0='||quote(old.t0)||' WHERE _rowid_='||old._rowid_); END;
This inserts into the table __xactlog__ a SQL UPDATE statement that if executed will undo all changes made by the UPDATE statement that triggered this trigger. Notice that INSERT OR IGNORE only enters one record in the __xactlog__ for every record changed (i.e. if one record is changed more than once, then only the first undo change is recorded in __xactlog__).
Let us now examine the UPDATE trigger in ct0sql. This trigger is in effect when the persistent transaction is NOT in effect. This trigger protects against changes to a row that has been already changed within a persistent transaction. We protect against such conflicts to avoid problems with database consistency, in case a persistent transaction is ever undone.
CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND tabrowid=old._rowid_ AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;
This checks to see if a change for this table and row id already exists in the transaction log. If it does the UPDATE statement is aborted.
In some cases rows have application-level relationships, which are not visible to the persistent transaction mechanism. For this purpose, the persistent transaction mechanism can disallow any changes to the whole table that has a pending persistent transaction change. This is implemented by modifying the ct0sql triggers. For example, for UPDATE:
CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;
Notice that the “AND tabrowid=old._rowid_” test is missing from the trigger’s WHEN clause. This effectively will disallow any changes to a table that has any change logged for a particular persistent transaction.
Clearly table granularity is very coarse and limits the usefulness of this mechanism. Unfortunately it is necessary in some cases.
http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
Concept
The concept of persistent transactions is simple to understand: a persistent transaction is one that persists across traditional (hereinafter called transient) transactions and even across database connections. A persistent transaction is comprised by multiple transient transactions, but crucially it can be “undone” (rolled back) at a later time.Persistent transactions can be used in place of long running transient transactions or when there is likelihood that an operation will be undone in the future. Consider, for example, a system where the user enters some data that will eventually constitute a number of records in a database. It might be convenient for the application to update the database in a transient transaction, so that it can offer a live view of any data updates to the user. However the user may still decide to abort the data entry, in which case any transient transactions will need to be undone.
Persistent transactions have names and a well-defined lifetime. A persistent transaction can be started using the beginPersistentTransaction() operation and it can be ended using the endPersistentTransaction() operation. When a persistent transaction is ended, the transaction changes may be permanently posted to the database (committed) or they may be undone (rolled back). To update the database while in a persistent transaction, one starts a transient transaction and “enters” the persistent transaction using the enterPersistentTransaction() operation. Likewise to commit (or rollback) the transient transaction changes, one “leaves” the persistent transaction and commits (or rolls back) the transient transaction using the leavePersistentTransaction() operation.
Design
Persistent transactions rely on triggers to perform their magic. When a persistent transaction is in effect, every SQL INSERT, UPDATE or DELETE statement is intercepted by a trigger and examined. Then a record is made that allows the persistent transaction mechanism to undo the statement change. While a persistent transaction is started, but not in effect (i.e. not “entered”), triggers are used again to examine the statements and possibly disallow the change if it would interfere with records already updated while the persistent transaction was in effect.The persistent transaction mechanism maintains two special tables (per attached SQLite database). The tables are named __xactlst__ and __xactlog__. The __xactlst__ table is used to maintain information about all persistent transactions currently started in the database. The __xactlog__ table is used to keep track of the changes made while in a persistent transaction. Specifically the table contains SQL instructions that when replayed will “undo” all changes by a specific persistent transaction and restore the database to its state prior to the persistent transaction. This is done in such a way so that changes done outside of a persistent transaction are not affected.
When a persistent transaction is started with beginPersistentTransaction(xname), a record is made in the __xactlst__ table that lists the persistent transaction id, name and CREATE/DROP TRIGGER scripts. The schema of the __xactlst__ table is (as mentioned we will be using SQLite syntax):
xactid INTEGER PRIMARY KEY NOT NULL,xname TEXT UNIQUE COLLATE NOCASE,ct0sql TEXT,ct1sql TEXT,dtsql TEXT
The ct0sql, ct1sql columns contain the CREATE TRIGGER scripts and the dtsql column contains the DROP TRIGGER script. The ct1sql script is used to create triggers that should be active when the persistent transaction is in effect (i.e. entered). The ct0sql script is used to create triggers that should be active when the persistent transaction is not in effect (i.e. after leave). The dtsql script is used to drop any persistent transaction related triggers.
The __xactlog__ table has the following schema:
tabname TEXT,tabrowid INTEGER,xactid INTEGER,sql TEXT,PRIMARY KEY(tabname, tabrowid, xactid)
Suppose that a persistent transaction is started with the name “Xa” that will operate on a table named tab0 in the database db1. This is what the ct0sql script will look like:
CREATE TRIGGER [db1].[__xactitr__1_tab0] AFTER INSERT ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND tabrowid=new._rowid_ AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND tabrowid=old._rowid_ AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;CREATE TRIGGER [db1].[__xactdtr__1_tab0] AFTER DELETE ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND tabrowid=old._rowid_ AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;
This is what the ct1sql script will look like:
CREATE TRIGGER [db1].[__xactitr__1_tab0] AFTER INSERT ON [tab0] BEGIN INSERT OR IGNORE INTO __xactlog__ VALUES('tab0', new._rowid_, 1, 'DELETE FROM [db1].[tab0] WHERE _rowid_='||new._rowid_); END;CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] BEGIN INSERT OR IGNORE INTO __xactlog__ VALUES('tab0', old._rowid_, 1, 'UPDATE [db1].[tab0] SET i0='||quote(old.i0)||',t0='||quote(old.t0)||' WHERE _rowid_='||old._rowid_); END;CREATE TRIGGER [db1].[__xactdtr__1_tab0] AFTER DELETE ON [tab0] BEGIN INSERT INTO __xactlog__ VALUES('tab0', old._rowid_, 1, 'INSERT INTO [db1].[tab0] (_rowid_,i0,t0) VALUES('||old._rowid_||','||quote(old.i0)||','||quote(old.t0)||')'); END;
Finally this is what the dtsql script will look like:
DROP TRIGGER [db1].[__xactitr__1_tab0];DROP TRIGGER [db1].[__xactutr__1_tab0];DROP TRIGGER [db1].[__xactdtr__1_tab0];
Let’s examine closely the UPDATE trigger in ct1sql. This trigger is in effect when a persistent transaction is in effect:
CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] BEGIN INSERT OR IGNORE INTO __xactlog__ VALUES('tab0', old._rowid_, 1, 'UPDATE [db1].[tab0] SET i0='||quote(old.i0)||',t0='||quote(old.t0)||' WHERE _rowid_='||old._rowid_); END;
This inserts into the table __xactlog__ a SQL UPDATE statement that if executed will undo all changes made by the UPDATE statement that triggered this trigger. Notice that INSERT OR IGNORE only enters one record in the __xactlog__ for every record changed (i.e. if one record is changed more than once, then only the first undo change is recorded in __xactlog__).
Let us now examine the UPDATE trigger in ct0sql. This trigger is in effect when the persistent transaction is NOT in effect. This trigger protects against changes to a row that has been already changed within a persistent transaction. We protect against such conflicts to avoid problems with database consistency, in case a persistent transaction is ever undone.
CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND tabrowid=old._rowid_ AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;
This checks to see if a change for this table and row id already exists in the transaction log. If it does the UPDATE statement is aborted.
Commit
Committing a persistent transaction is as simple as deleting all records related to the transaction from the transaction log (__xactlog__). Then all associated triggers must be dropped (dtsql) and the transaction record deleted from the __xactlst__ table. Finally if the __xactlst__ table is now empty (indicating no remaining persistent transactions) the __xactlst__ and __xactlog__ tables are dropped.Rollback
Rolling back a persistent transaction requires replaying the statements recorded in the transaction log for a particular transaction. This can be a lengthy operation. Because the persistent transaction mechanism protects against changes to records that have already been changed inside a persistent transaction, rolling back the transaction should not create any consistency issues for the database in most cases (but see below for when this may not be true). When all recorded statements have been replayed, they are deleted from the log. Then all associated triggers must be dropped (dtsql) and the transaction record deleted from the __xactlst__ table. Finally if the __xactlst__ table is now empty (indicating no started persistent transactions) the __xactlst__ and __xactlog__ tables are dropped.Consistency after undo
Whenever a persistent transaction is started, the persistent transaction mechanism tracks all SQL INSERT, UPDATE and DELETE statements to verify that the changes attempted will not be in conflict with changes made while the persistent transaction was also in effect (i.e. entered). The usual assumption is that rows in a table are not related, which means that if a row in a table changes, no other row in the table is affected. If this is true for all tables involved in a persistent transaction, then the transaction mechanism will provide sufficient protection and the database will remain consistent after a persistent transaction rollback.In some cases rows have application-level relationships, which are not visible to the persistent transaction mechanism. For this purpose, the persistent transaction mechanism can disallow any changes to the whole table that has a pending persistent transaction change. This is implemented by modifying the ct0sql triggers. For example, for UPDATE:
CREATE TRIGGER [db1].[__xactutr__1_tab0] AFTER UPDATE ON [tab0] WHEN (SELECT 1 FROM [db1].__xactlog__ WHERE tabname='tab0' AND xactid=1) BEGIN SELECT RAISE(ABORT, 'table [db1].[tab0] has persistent transaction changes'); END;
Notice that the “AND tabrowid=old._rowid_” test is missing from the trigger’s WHEN clause. This effectively will disallow any changes to a table that has any change logged for a particular persistent transaction.
Clearly table granularity is very coarse and limits the usefulness of this mechanism. Unfortunately it is necessary in some cases.
Related Reading
This page demonstrates how to use triggers to implement undo/redo logic for an application that uses SQLite as its primary data structure.http://www.sqlite.org/cvstrac/wiki?p=UndoRedo