Supporting Transactions MySQL

This section documents the methods that must be implemented to add support for transactions to a storage engine.

Please note that transaction management can be complicated and involve methods such as row version-ing and redo logs,which is beyond the scope of this document. Instead coverage is limited to a description of required methods and not their implementation. For examples of implementation, please see ha_innodb.cc.

Transaction Overview

Transactions are not explicitly started on the storage engine level, but are instead implicitly started through calls to either start_stmt()or external_lock(). If the preceding methods are called and a transaction already exists the transaction is not replaced.

The storage engine stores transaction information in per-connection memory and also registers the transaction in the MySQL server to allow the server to later issue COMMIT and ROLLBACK operations. As operations are performed the storage engine will have to implement some form of versioning or logging to permit a rollback of all operations executed within the transaction.

After work is completed,the MySQL server will call either the commit() method or the rollback() method defined in the storage engine's handlerton.

Starting a Transaction

A transaction is started by the storage engine in response to a call to either the start_stmt() or external_ lock() methods.

If there is no active transaction, the storage engine must start a new transaction and register the transaction with the MySQL server so that ROLLBACK or COMMIT can later be called.

Starting a Transaction from a start_stmt() Call

The first method call that can start a transaction is the start_stmt() method.

The following example shows how a storage engine could register a transaction:

THD is the current client connection. It holds state relevant data for the current client, such as identity, network connection and other per-connection data.

thd->ha_data[my_handler_hton.slot] is a pointer in thd to the connection-specific data of this storage engine. In this example we use it to store the transaction context.

An additional example of implementing start_stmt() can be found in ha_innodb.cc.

Starting a Transaction from a external_lock() Method

MySQL calls handler::external_lock() for every table it is going to use at the beginning of every statement. Thus, if a table is touched for the first time, it implicitly starts a transaction.

Note that because of pre-locking,all tables that can be potentially used between the beginning and the end of a statement are locked before the statement execution begins and handler:: external_lock()is called for all these tables.

That is,if an INSERT fires a trigger, which calls a stored procedure,that invokes a stored method, and so forth, all tables used in the trigger,stored procedure,method, etc.,are locked in the beginning of the INSERT. Additionally,if there's a construct like

Also, if a user calls LOCK TABLES,MySQL will call handler::external_lock only once. In this case, MySQL will call handler :: start_stmt() at the beginning of the statement.

The following example shows how a storage engine can start a transaction and take locking requests into account:

Every storage engine must call trans_register_ha()every time it starts a transaction. The trans_register_ha()method registers a transaction with the MySQL server to allow for future COMMIT and ROLLBACK calls.

An additional example of implementing external_lock() can be found in ha_innodb.cc.

Implementing ROLLBACK

Of the two major transactional operations,ROLLBACK is the more complicated to implement. All operations that occurred during the transaction must be reversed so that all rows are unchanged from before the transaction began.

To support ROLLBACK, create a method that matches this definition:

The method name is then listed in the rollback(thirteenth)entry of the handlerton.

The THD parameter is used to identify the transaction that needs to be rolled back, while the bool all parameter indicates whether the entire transaction should be rolled back or just the last statement.

Details of implementing a ROLLBACK operation will vary by storage engine. Examples can be found in ha_innodb.cc.

Implementing COMMIT

During a commit operation,all changes made during a transaction are made permanent and a rollback operation is not possible after that. Depending on the transaction isolation used,this may be the first time such changes are visible to other threads.

To support COMMIT, create a method that matches this definition:

The method name is then listed in the commit (twelfth) entry of the handlerton.

The THD parameter is used to identify the transaction that needs to be committed, while the bool all parameter indicates if this is a full transaction commit or just the end of a statement that is part of the transaction.

Details of implementing a COMMIT operation will vary by storage engine. Examples can be found in ha_innodb.cc.

If the server is in auto-commit mode, the storage engine should automatically commit all read-only statements such as SELECT.

In a storage engine, "auto-committing" works by counting locks. Increment the count for every call to external_lock(), decrement when external_lock() is called with an argument of F_UNLCK. When the count drops to zero, trigger a commit.

Adding Support for Savepoints

First, the implementor should know how many bytes are required to store savepoint information. This should be a fixed size, preferably not large as the MySQL server will allocate space to store the savepoint for all storage engines with each named savepoint.

The implementor should store the data in the space preallocated by mysqld - and use the contents from the preallocated space for rollback or release savepoint operations.When a COMMIT or ROLLBACK operation occurs (with bool all set to true), all savepoints are as- sumed to be released. If the storage engine allocates resources for savepoints, it should free them.

The following handlerton elements need to be implemented to support savepoints (elements 7,9,10,11):

Specifying the Savepoint Offset

The seventh element of the handlerton is the savepoint_offset: uint savepoint_offset;

The savepoint_offset must be initialized statically to the size of the needed memory to store persavepoint information.

Implementing the savepoint_set Method

The savepoint_set() method is called whenever a user issues the SAVEPOINT statement:

The *sv parameter points to an uninitialized storage area of the size defined by savepoint_offset.

When savepoint_set() is called, the storage engine needs to store savepoint information into sv so that the server can later roll back the transaction to the savepoint or release the savepoint resources.

Implementing the savepoint_rollback() Method

The savepoint_rollback() method is called whenever a user issues the ROLLBACK TO SAVEPOINT statement:

The *sv parameter points to the storage area that was previously passed to the savepoint_set() method.

Implementing the savepoint_release() Method

The savepoint_release() method is called whenever a user issues the RELEASE SAVEPOINT statement:

The *sv parameter points to the storage area that was previously passed to the savepoint_set() method.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MySQL Topics