Transactions

Polyhedra supports transactions, whereby a client can send down a group of changes to be performed together. Polyhedra enforces the 'ACID' properties:

  • Atomic.
    The changes of a transaction (together with any knock-on changes performed by trigger code attached to the database) are either all applied, or - if there is any reason for any of them to fail - are all cancelled, with the database rolled back to its pre-transactional state.

  • Consistent.
    Any breach of the database integrity rules* causes the transaction to fail, so each successful transaction takes the database from one correct state to another.

      (*Polyhedra enforces entity integrity - where rows have to have the right number of columns, values have to be the correct type for the column, 'not null' attributes have to have a value and uniqueness constraints are enforced - primary key integrity - where each table has to have a designated column or set of columns, with each record uniquely identified by the value(s) in its primary key - and referential integrity: if a column or set of columns in a table are defined as a foreign key reference to another table (or even to the same table), then for each record the values in these columns are either all null or they match the primary key of a record in the designated table. In addition, Polyhedra allows 'triggers' written in CL to be attached to tables, and these can impose further constraints.)

  • Isolated.
    The intermediate state of a transaction is invisible to other transactions.

  • Durable.**
    Once a transaction is reported as successful, the initiator is entitled to assume the changes have been safely records, and so will be preserved even over a system failure.

      (**Polyhedra FlashLite enforces durability by default; Polyhedra IMDB leaves it up to the client to decide whether it is told of the successful transaction immediately upon completion, or only when it is known it has been successfully journalled to disk and - where applicable - applied to the standby server.)

The Polyhedra transactional model is very simple, as is appropriate for a system designed for real-time work. Basically, there are four main types of request from a client to a server:

  • an SQL query (which can be 'static' or 'active', as described below);
  • some 'SQL DDL' to change the structure of the database - for example, the addition of a number of tables, or the addition of some columns to some existing tables;
  • a group of SQL DML statements (insert, update, delete, etc), optionally followed by an SQL query; or,
  • a set of updates to the result set of one or more active queries.

To understand how these are handled by the server, it is best to envisage these requests being put onto a queue, and processed in turn - so that everything is serialised. (This is an over-simplification, as explained below).

  • For a query, the database server just calculates the result set and sends it to the client. For normal (static) queries, that is all that is needed; for active queries, though, the server remembers enough about the query so that it can inform the client when relevant changes occur. (This is described in more detail in a separate FAQ.)
  • For DDL or DML, the neccessary changes to the database are made and the client is told whether it was successful; in the case of DML which an appended query, if the tranasction was successful then the query is immediately evaluated (before any other transaction has a chance to update the database).
  • For updates through active queries, where meaningful and possible the database server tries to alter the database contents to match the requested changes. However, a limited form of 'optimistic concurrency' is enforced, so that if the database has been altered recently a way that 'clashes' with any of the new changes, the new transaction will be abandoned. This is described further in the article on active queries.
From Polyhedra 8.7 onwards (March 2013), Polyhedra also supports an explicit locking mechanism, with both optimistic and pessimistic locks. This is described in more detail on a separate page.

CL in transactions

If CL is attached to the database, any CL triggered as the result of a transaction is performed within that transaction; the CL code has the ability to abort the transaction if it finds an application error, and any changes done by the CL code are rolled back if the transaction has to be aborted for any reason.

 A degree of parallelism

Although the Polyhedra transactional model is described as though everything is serialised, in fact the database server uses separate threads and internal, table-level locks to allow a certain amount of parallel execution (for 'fairness', and to get better performance on multi-core or multiprocessor machines) while preserving the semantics of full serialisation. Thus, queries from separate clients can operate simultaneously, and each will put non-exclusive (read-only) locks on the tables they are reading; meanwhile a separate thread performing a batch of SQL DML (or a batch of alterations via active queries) will accumulate exclusive locks on on the tables it is altering. Normally, there is at most one update transaction running at a time, but under certain circumstances it is possible to allow many to run at once - see the server reference manual for details of how to enable this mode (and the restrictions that apply).