Drizzled Public API Documentation

Transactions

Transaction handling in the server. More...

Transaction handling in the server.

In each client connection, Drizzle maintains two transaction contexts representing the state of the:

1) Statement Transaction 2) Normal Transaction

These two transaction contexts represent the transactional state of a Session's SQL and XA transactions for a single SQL statement or a series of SQL statements.

When the Session's connection is in AUTOCOMMIT mode, there is no practical difference between the statement and the normal transaction, as each SQL statement is committed or rolled back depending on the success or failure of the indvidual SQL statement.

When the Session's connection is NOT in AUTOCOMMIT mode, OR the Session has explicitly begun a normal SQL transaction using a BEGIN WORK/START TRANSACTION statement, then the normal transaction context tracks the aggregate transaction state of the SQL transaction's individual statements, and the SQL transaction's commit or rollback is done atomically for all of the SQL transaction's statement's data changes.

Technically, a statement transaction can be viewed as a savepoint which is maintained automatically in order to make effects of one statement atomic.

The normal transaction is started by the user and is typically ended (COMMIT or ROLLBACK) upon an explicity user request as well. The exception to this is that DDL statements implicitly COMMIT any previously active normal transaction before they begin executing.

In Drizzle, unlike MySQL, plugins other than a storage engine may participate in a transaction. All plugin::TransactionalStorageEngine plugins will automatically be monitored by Drizzle's transaction manager (implemented in this source file), as will all plugins which implement plugin::XaResourceManager and register with the transaction manager.

If Drizzle's transaction manager sees that more than one resource manager (transactional storage engine or XA resource manager) has modified data state during a statement or normal transaction, the transaction manager will automatically use a two-phase commit protocol for all resources which support XA's distributed transaction protocol. Unlike MySQL, storage engines need not manually register with the transaction manager during a statement's execution. Previously, in MySQL, all handlertons would have to call trans_register_ha() at some point after modifying data state in order to have MySQL include that handler in an XA transaction. Drizzle does all of this grunt work behind the scenes for the storage engine implementers.

When a connection is closed, the current normal transaction, if any is currently active, is rolled back.

Transaction life cycle

When a new connection is established, session->transaction members are initialized to an empty state. If a statement uses any tables, all affected engines are registered in the statement engine list automatically in plugin::StorageEngine::startStatement() and plugin::TransactionalStorageEngine::startTransaction().

You can view the lifetime of a normal transaction in the following call-sequence:

drizzled::statement::Statement::execute() drizzled::plugin::TransactionalStorageEngine::startTransaction() drizzled::TransactionServices::registerResourceForTransaction() drizzled::TransactionServices::registerResourceForStatement() drizzled::plugin::StorageEngine::startStatement() drizzled::Cursor::write_row() <– example...could be update_row(), etc drizzled::plugin::StorageEngine::endStatement() drizzled::TransactionServices::autocommitOrRollback() drizzled::TransactionalStorageEngine::commit() <– or ::rollback() drizzled::XaResourceManager::xaCommit() <– or rollback()

Roles and responsibilities

Beginning of SQL Statement (and Statement Transaction)

At the start of each SQL statement, for each storage engine that is involved in the SQL statement, the kernel calls the engine's plugin::StoragEngine::startStatement() method. If the engine needs to track some data for the statement, it should use this method invocation to initialize this data. This is the beginning of what is called the "statement transaction".

For transaction storage engines (those storage engines that inherit from plugin::TransactionalStorageEngine), the kernel automatically determines if the start of the SQL statement transaction should also begin the normal SQL transaction. This occurs when the connection is in NOT in autocommit mode. If the kernel detects this, then the kernel automatically starts the normal transaction w/ plugin::TransactionalStorageEngine::startTransaction() method and then calls plugin::StorageEngine::startStatement() afterwards.

Beginning of an SQL "Normal" Transaction

As noted above, a "normal SQL transaction" may be started when an SQL statement is started in a connection and the connection is NOT in AUTOCOMMIT mode. This is automatically done by the kernel.

In addition, when a user executes a START TRANSACTION or BEGIN WORK statement in a connection, the kernel explicitly calls each transactional storage engine's startTransaction() method.

Ending of an SQL Statement (and Statement Transaction)

At the end of each SQL statement, for each of the aforementioned involved storage engines, the kernel calls the engine's plugin::StorageEngine::endStatement() method. If the engine has initialized or modified some internal data about the statement transaction, it should use this method to reset or destroy this data appropriately.

Ending of an SQL "Normal" Transaction

The end of a normal transaction is either a ROLLBACK or a COMMIT, depending on the success or failure of the statement transaction(s) it encloses.

The end of a "normal transaction" occurs when any of the following occurs:

1) If a statement transaction has completed and AUTOCOMMIT is ON, then the normal transaction which encloses the statement transaction ends 2) If a COMMIT or ROLLBACK statement occurs on the connection 3) Just before a DDL operation occurs, the kernel will implicitly commit the active normal transaction

Transactions and Non-transactional Storage Engines

For non-transactional engines, this call can be safely ignored, an the kernel tracks whether a non-transactional engine has changed any data state, and warns the user appropriately if a transaction (statement or normal) is rolled back after such non-transactional data changes have been made.

XA Two-phase Commit Protocol

During statement execution, whenever any of data-modifying PSEA API methods is used, e.g. Cursor::write_row() or Cursor::update_row(), the read-write flag is raised in the statement transaction for the involved engine. Currently All PSEA calls are "traced", and the data can not be changed in a way other than issuing a PSEA call. Important: unless this invariant is preserved the server will not know that a transaction in a given engine is read-write and will not involve the two-phase commit protocol!

At the end of a statement, TransactionServices::autocommitOrRollback() is invoked. This call in turn invokes plugin::XaResourceManager::xapPepare() for every involved XA resource manager.

Prepare is followed by a call to plugin::TransactionalStorageEngine::commit() or plugin::XaResourceManager::xaCommit() (depending on what the resource is...)

If a one-phase commit will suffice, plugin::StorageEngine::prepare() is not invoked and the server only calls plugin::StorageEngine::commit_one_phase(). At statement commit, the statement-related read-write engine flag is propagated to the corresponding flag in the normal transaction. When the commit is complete, the list of registered engines is cleared.

Rollback is handled in a similar fashion.

Additional notes on DDL and the normal transaction.

CREATE TABLE .. SELECT can start a new normal transaction because of the fact that SELECTs on a transactional storage engine participate in the normal SQL transaction (due to isolation level issues and consistent read views).

Behaviour of the server in this case is currently badly defined.

DDL statements use a form of "semantic" logging to maintain atomicity: if CREATE TABLE .. SELECT failed, the newly created table is deleted.

In addition, some DDL statements issue interim transaction commits: e.g. ALTER TABLE issues a COMMIT after data is copied from the original table to the internal temporary table. Other statements, e.g. CREATE TABLE ... SELECT do not always commit after itself.

And finally there is a group of DDL statements such as RENAME/DROP TABLE that doesn't start a new transaction and doesn't commit.

A consistent behaviour is perhaps to always commit the normal transaction after all DDLs, just like the statement transaction is always committed at the end of all statements.