Transactions in MySQL

2011-02-15 10:49 zhenjing 阅读(661) 评论(0) 编辑收藏举报

July 20, 2004

Transactions in MySQL

By Ian Gilfillan

What is a transaction?

If you are asking this question, you are probably used to website databases, where most often it does not matter in which order you run transactions, and if one query fails, it has no impact on others. If you are updating some sort of content, often you will not care when the update is performed, as long as the reads are being taken care of quickly. Similarly, if an update fails, the reads can still carry on reading the old data in the meantime. However, there are times when it is vitally important in which order queries run, and that all queries in a group run, or none at all. The classic example is from the banking environment. An amount of money is taken from one person's account, and put into another, for example as follows, a 500-unit transaction:

UPDATE account1 SET balance=balance-500;
UPDATE account1 SET balance=balance+500;

Both queries must run, or neither must run. You cannot have the money being transferred out of one person's account, and then 'disappearing' if for some reason the second query fails. Both these queries form one transaction. A transaction is simply a number of individual queries that are grouped together.

A small dose of ACID

For a long time, when MySQL did not support transaction, its critics complained that it was not ACID compliant. What they meant, is that MySQL did not comply with the four conditions to which transactions need to adhere in order to ensure data integrity. These four conditions are:

Atomicity: An atom is meant to be the smallest particle, or something that cannot be divided. Atomicity applies this principle to database transactions. The queries that make up the transaction must either all be carried out, or none at all (as with our banking example, above).

Consistency: This refers to the rules of the data. For example, an article body may have to have an associated article heading. During the transaction, this rule may be broken, but this state of affairs should never be visible from outside of the transaction.

Isolation: Simply put, data being used for one transaction cannot be used by another transaction until the first transaction is complete. Take this example below, where an account balance starts at 900. There is a single deposit of 100, and a withdrawal of 100, so the balance at the end should remain the same.

Connection 1: SELECT balance FROM account1;
Connection 2: SELECT balance FROM account1;
Connection 1: UPDATE account1 SET balance = 900+100;
Connection 2: UPDATE account1 SET balance = 900-100;

The balance is now 800, so we have lost 100. These two transactions should have been isolated, and the result supplied to Connection 2 only when the transaction from Connection 1 was complete.

Durability: Once a transaction has completed, its effects should remain, and not be reversible.

Down to work: InnoDB Transactions

Transactions are wrapped in BEGIN and COMMIT statements. Let's create a sample InnoDB table, and see how transactions work:

mysql> CREATE TABLE t (f INT) TYPE=InnoDB;

Now let's begin a transaction, and insert a record:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t(f) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t;
Empty set (0.00 sec)

Without a COMMIT statement, the insert was not permanent, and was reversed with the ROLLBACK. Note that the added record was visible during the transaction from the same connection that added it.

Consistent reads

Let's try looking from a different connection. For this exercise, open two connections to the database.

Connection 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (f) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
Empty set (0.02 sec)

The important point is that running the same query from different connections (one within the middle of a transaction, the other from without) produces different results. Now, commit the transaction from the first connection, and run the query again from connection 2.

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

This behavior is called consistent reading. Any select returns a result up until the most recently completed transaction, with the exception of the connection doing the updating, as we saw above. By default, MySQL InnoDB tables perform consistent reads.

Automatic Commits

MySQL also automatically commits statements that are not part of a transaction. The results of any UPDATE or INSERT statement not preceded with a BEGIN will immediately be visible to all connections. You can change this behavior, as follows:

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

Now, note what happens, even if we do not specifically start a transaction with BEGIN.

Connection 1:

mysql> INSERT INTO t (f) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Commit the transaction from the first connection, then reset AUTOCOMMIT to 1, and repeat the exercise:

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (f) VALUES (3);
Query OK, 1 row affected (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

This time the transaction is committed immediately, and is visible from another connection, even without a specific COMMIT statement.

Read locks for Updating

Sometimes, the default consistent read is not what you want. There are cases where we would want to read a record in order to update it, knowing that we are not conflicting with any other connection that is doing the same thing. For example, two connections read a record, in order to insert a new value incremented by one from the current maximum.

Connection 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(f) FROM t;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO t(f) VALUES (4);
Query OK, 1 row affected (0.00 sec)

Connection 2:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(f) FROM t;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO t(f) VALUES(4);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t; 
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    4 |
+------+
5 rows in set (0.00 sec)

The result is that we have two records with the value 4, where we wanted to have one with 4, one with 5. We can overcome this by creating an update lock. This specifies that no other connections can read that data until the transaction is complete. Here is an update lock in action. First, we delete the erroneous record:

mysql> DELETE FROM t WHERE f=4;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(f) FROM t FOR UPDATE;
+--------+
| MAX(f) |
+--------+
|      3 |
+--------+
1 row in set (0.01 sec)

mysql> INSERT INTO t(f) VALUES (4);
Query OK, 1 row affected (0.00 sec)

Connection 2:

mysql> SELECT MAX(f) FROM t FOR UPDATE;

No results are returned - MySQL is waiting until the active transaction is complete, and only then will it return the results Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

The results are now returned to connection 2. Note that the read may have timed out if you have taken too long:

Connection 2:

mysql> SELECT MAX(f) FROM t FOR UPDATE;
+--------+
| MAX(f) |
+--------+
|      4 |
+--------+
1 row in set (4.23 sec)

mysql> INSERT INTO t(f) VALUES(5);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

Read locks for sharing

Another kind of lock ensures you always read the latest data, but is not part of the transaction wanting to change the data itself. This is the LOCK IN SHARE MODE. It will stop any updates or deletes of the row being read, and if the latest data is still uncommitted, will wait until that transaction is committed before returning any results. Here's an example:

Connection 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(f) FROM t LOCK IN SHARE MODE;
+--------+
| MAX(f) |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

Meanwhile, a second connection attempts to perform an update:

Connection 2:

mysql> UPDATE t SET f = 55 WHERE f=5;

The update waits until the lock from the other connection is released.

Connection 1:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Connection 2:

mysql> UPDATE t SET f = 55 WHERE f=5;
Query OK, 0 rows affected (6.95 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> UPDATE t SET f = 55 WHERE f=5;
Query OK, 1 row affected (43.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|   55 |
+------+
5 rows in set (0.00 sec)

BDB tables handle transactions slightly differently, and the default MyISAM tables only in a very limited way (table locks only, as opposed to read locks), but if you are using transactions, you are most likely to be using InnoDB tables. If there's enough demand, I will write a follow up article on transactions in these table types, but hopefully you have enough to keep you busy in the meantime. Good luck!

转自：http://www.databasejournal.com/features/mysql/article.php/10897_3382171_2/Transactions-in-MySQL.htm

Note

1) Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0.) If autocommit is enabled, the rows matching the specification are not locked.

2) Only InnoDB supports SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads. MyISAM and other Engine is not.
==================================

区别：innodb和MyISAM（请自行进一步搜索）

MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：· MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置 MySQL默认使用另外一个引擎。 ·MEMORY存储引擎提供“内存中”表。MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。释：MEMORY存储引擎正式地被确定为HEAP引擎。· InnoDB和BDB存储引擎提供事务安全表。BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所有MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。·EXAMPLE存储引擎是一个“存根”引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。

在MySQL 5.0里面，MyISAM和InnoDB存储引擎性能差别并不是很大，针对InnoDB来说，影响性能的主要是 innodb_flush_log_at_trx_commit 这个选项，如果设置为1的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0效率能够看到明显提升，当然，同样你可以SQL中提交“SET AUTOCOMMIT = 0”来设置达到好的性能。另外，还听说通过设置innodb_buffer_pool_size能够提升InnoDB的性能，但是我测试发现没有特别明显的提升。

基本上我们可以考虑使用InnoDB来替代我们的MyISAM引擎了，因为InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多，当然，相应的在my.cnf中的配置也是比较关键的，良好的配置，能够有效的加速你的应用。

见：MySQL中MyISAM引擎与InnoDB引擎性能比较

更多信息见官方文档：第15章：存储引擎和表类型

刷新页面返回顶部

zhenjing的博客以文会友