Transactions and Locking in MySQL

[Bruno007]

I have observed that many new learners of MySQL has tough times while understanding the transactions and locking. So thought to provide some hints for all those who are suffering for it.

One of the classic problems is that a DBMS must handle concurrent access to data by different users, both read and update. A typical situation of this kind is the case, for example, two users read the same data with the intention of updating it, evidently one of them will do it for the first, at which point the second user, when they attempt to turn a update, you will find a changed situation with respect to the time he read the data, with the risk of creating situations inconsistent. Another classic situation that is problematic is when an application needs to do more updates logically connected to each other, requiring that all updates are canceled when only one of them fails. The solutions to these problems are, in their simplest form, the lock on the tables, and in the more advanced transactions. The latter are not available on traditional storage engine MySQL, MyISAM, but InnoDB and BDB tables only.

[Bruno007]

Let's start with the analysis of the lock, we can consider the constraints of "exclusive use" that a user can get on certain tables for the time necessary to carry out operations that are needed. With the lock can be simulated (in part) transactions, or in some cases simply speed up write operations, where there are large amounts of data to be entered. The use of the lock is recommended only with the MyISAM table type, which does not support transactions. A lock may be required to read or write: In the first case the user has the guarantee that nobody will update the table will be locked until you release the lock, but other users will still be allowed the opportunity to read the same table . In this case, however, even the person who obtained the lock can make updates. The write lock prevents other users instead of all access to the table, and allows a user who has acquired reading and writing. Let's see the syntax of lock operations, noting that they require the privilege to SELECT and LOCK TABLES on tables involved:

LOCK TABLES
     Table [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
     [Table [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...

First, we note that it is possible to lock up more tables with LOCK TABLES a single statement, in reality, more of a chance that this is a must. In fact, every education LOCK TABLES causes the release of the lock obtained earlier: therefore, if you need to get lock on multiple tables, you are obliged to do so with a single statement.

[Bruno007]

At every table in the lock ask you can associate an alias, just as in the query: in this case we are bound to use this system where the queries that we are going to make use aliases. In practice, after getting a lock, our query can use only the tables on which we have the lock: it is not possible then, in the presence of active lock, access other tables in addition, we will have access to these tables using the same alias defined in phase lock. If a table is present multiple times in a query, we obviously need more than an alias: thus we get a lock for each alias, although the table is the same. The LOCAL clause associated with a READ lock allows other users to make entries that do not conflict with our reading. LOW_PRIORITY clause associated with a WRITE lock makes the request to give priority to requests for read lock (usually rather a write lock has higher priority). The locks obtained are released with the statement:

UNLOCK TABLES

In fact we have already seen that a new lock request causes the release of the previous ones also locks are automatically released at the end of the connection, if it was not done explicitly.

[Bruno007]

The use of transactions can "consolidate" the changes to the database only in a precise time: from the moment we start a transaction, the updates are suspended (and invisible to other users) until they confirm (commit) or alternatively at the confirmation you can cancel (rollback). First, it should be noted that MySQL runs in autocommit mode by default, meaning that all updates are automatically established when they are executed. If we are in autocommit, to begin a transaction must use the START TRANSACTION statement, from this point on, all updates will remain suspended. The transaction can be closed with the COMMIT statement, which consolidates the changes, with or ROLLBACK, which cancels all updates made during the transaction. We can also use COMMIT or ROLLBACK AND CHAIN AND CHAIN, causing the imminent opening of a new transaction, or COMMIT RELEASE or ROLLBACK RELEASE, which in addition to closing the transaction will close the connection to the server. With the SET AUTOCOMMIT = 0 we can disable autocommit: in this case is no longer necessary to initiate the transaction with START TRANSACTION, and all updates will remain suspended until the use of COMMIT or ROLLBACK. Within a transaction can also establish savepoint, that is of intermediate states to which we can come back with a ROLLBACK instead of canceling the entire transaction. Here's an example:

START TRANSACTION 
... Update statements (1) ... 
SAVEPOINT sp1; 
... Update statements (2) ... 
ROLLBACK TO SAVEPOINT sp1; 
... Update statements (3) ... 
COMMIT

In this case, after starting the transaction we performed a first set of updates, followed by the creation of the savepoint with the name 'sp1' and then we performed a second set of updates, the ROLLBACK TO SAVEPOINT sp1 causes "back "to the situation when we created the savepoint, in practice only the second block updates will be released, and the transaction remains open, just a ROLLBACK instead he was canceling all and closed the transaction. The COMMIT performed after the third block causes be consolidated updates made in the first and third block.

[Quattro]

Please note that correct use of transactions is only possible using the same type of tables within each transaction. The simultaneous use of InnoDB and BDB can in fact create some problems in the COMMIT time. It is highly recommended course using MyISAM tables in transactions, as on them can not be rolled back and the updates are effective immediately: if then ROLLBACK would generate precisely those inconsistencies that the use of transactions designed to avoid. You should also remember that some types of operations are not affected: in general all those who create, delete, or alter the structure of databases and tables. It is therefore advisable not to include these operations in a transaction, which among other things, in most cases, cause an implicit COMMIT. In some cases it is useful to use two particular clauses when you make a SELECT:

SELECT .......... FOR UPDATE; 
SELECT .......... LOCK IN SHARE MODE;

The FOR UPDATE clause establishes a lock on all rows read that will prevent other users from reading the same lines until the end of our transaction, obviously you use when reading with a specific intention to update it. LOCK IN SHARE MODE clause instead establishes a lock that prevents only the updates, ensuring that the contents of the row will remain unchanged for the duration of our transaction.

[StephanieRice]

An important aspect related to transactions and isolation level at which they occur. The are four possible levels, and lists them in ascending order:

• READ UNCOMMITTED: At this level you can see updates made by others but not established: it is not proper transactional behavior, which can cause serious problems obviously consistency of the data should be used only when we have concerns of this nature and we need speed readings
• READ COMMITTED: At this level the updates become visible only after the consolidation
• REPETEABLE READ: in this case because an update is visible must be not only consolidated but also the transaction that the law must be terminated in practice, repeated the same reading within a transaction will always give the same result, is the way default
• SERIALIZABLE: as above, but in addition, the simple reading of a data block causes the update until the transaction is in substance as if each were carried out with the clause SELECT LOCK IN SHARE MODE

The isolation level used may be given the option of starting the server - transaction-isolation to see what level you are using we can use the SELECT @ tx_isolation; also can edit it with the following statement:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL 
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

If we omit the clauses GLOBAL and SESSION, the change is valid only for the subsequent transaction with a session by setting the value for the entire connection, while GLOBAL modify the value for the server: this value will then be taken on all open connections, then ( not on those already open), in which case you need the SUPER privilege.