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).
|