原创 2001年06月12日 08:42:00

Life doesn't have to be logical. We fall in love, get attached to someone, promise ever-lasting love, and then we break up. And the worst part is of course the breaking up.

It's the same with databases. We decide we like this database, so we store records into it. We promise ourselves that the database will hold our records for ever and ever, and then the database crashes; now we want to strangle the database server...

This is where transactions are better than real-life love. Transactions is a technology that ensure that if you have to update multiple tables and you crash midway, you can rollback the data to a consistent state just before the crash. Imagine doing that with your loved one!

MySQL is the most popular open source database on Earth. The current stable release, 3.22 does not support transactions, but with a little bit of intelligence and discipline, we can simulate transactions.

How Transactions Work
An example of how we use transactions is a shopping cart system after checkout. Here we are generating an invoice and the invoice items based on the contents of a shopping_cart_items table.

Suppose we crash after creating the invoice record, but before all the invoice items are created. Or suppose we crash before we can delete all shopping cart items. Then we will be double-counting the items: once in the shopping cart, the other in the invoice.

Transactions help solve the problem, as can be seen below in pseudo-code:

begin tran;
INSERT INTO invoice (...) values (...);
$parent_id = mysql_inserted_id();
for each shopping_cart_items
   INSERT INTO invitems (...,invoice,..) VALUES (...,$parent_id,... );

DELETE FROM shopping_cart_items WHERE cart_id = ?
commit tran;

Now if we crash before we insert all the invoice items, the database will notice that a transaction was taking place, and will rollback the data to the state it was before the begin tran. So the invoice is not generated because the commit tran was never executed, and the shopping cart remains intact.

The current stable version of MySQL (3.22) does not support transactions. This is a feature currently in testing for 3.23, but I forsee that many web hosts will not be upgrading for some time to come. Not to worry, we'll simulate them using some techniques developed long ago for older databases.

There are 2 types of transactions we can simulate, record creation transactions, and update/delete transactions.

Record Creation Transactions
In the above example, we created the invoice record first. Then we used mysql_inserted_id() generated from the invoice record to provide the link from the child invitems to the parent invoice record.
In simulated transactions, we do things the other way round. The child records must be created first, then the parent.

Why? Because normally we view information from top-down, parent to child. In a simulated transaction, child records are treated as invalid if the parent record has not been created yet. To do this we also need to generate synthetic keys in advance as primary keys for the invoice table.

For example, assuming we have a function called generate_key() to generate the synthetic keys:

$parent_id = generate_key();
for each shopping_cart_items
   INSERT INTO invitems (...,invoice,..) VALUES(...,$parent_id,... );
INSERT INTO invoice (id,...) VALUES ($parent_id,...);

In this case, if we crash while updating the invitems, the invoice record is not created. Provided we never access the invitems records without joining to the parent table, we are ok. The simulated transaction will work.
To be safe, we can run a batch job in background to delete orphaned child records.

Now you are probably saying, we didn't cover the delete. What happens if we crash before the delete? Then we would have the purchased items both in the shopping cart and being shipped to the end-user.

Update/Delete Transactions
This sort of transaction is harder to handle. We need to implement a status field that tells us whether we are outside or inside a transaction.
Then after a crash or whenever the database is restarted, we perform a scan to detect whether any transaction occured when we crashed, and perform a repair if it is so.

For our example:

UPDATE shopping_cart_items SET status='IN_TRANS' WHERE id = ?;
$parent_id = generate_key();

for each shopping_cart_items
       INSERT INTO invitems (...,invoice,cartid,..)
                  VALUES (...,$parent_id,$cartid,... );

INSERT INTO invoice (id,...) VALUES ($parent_id,...);
DELETE FROM shopping_cart_items WHERE id = ?;

Then in your repair pseudo-code:

select cartid,id from shopping_cart_items,invitems
       where status = 'IN_TRANS'
       and invitems.cartid=shopping_cart_items.cartid
       INTO $cartid,$id;

for each ($cartid,$id)
   SELECT id FROM invoice WHERE = $id;
   if no records returned
     DELETE FROM invitems WHERE cartid = $cartid;
     DELETE FROM shopping_cart_items WHERE cartid = $cartid;

The same method of repair used for deletes is also used for updates.

If your MySQL database is out-sourced, and you are not informed when the MySQL database is restarted, then you need to do periodic scans and repairs. Sounds a bit like fsck or scandisk doesn't it?

In MySQL, we can generate the synthetic key using a special table to hold the last valid key number. Then whenever we want a new key, we lock the table, increment the key number by one, read the latest value, then unlock the table.

For example, assuming we create a table called invoiceid with one record containing one field called id. In pseudo-code:

function generate_key()
  LOCK TABLES invoiceid WRITE;
  UPDATE invoiceid SET id=id+1;
  SELECT id FROM invoiceid INTO $id;
  return $id;

So all you broken-hearted lovers out there -- get jealous. Transactional databases do it better. They can rollback to the starry-eyed time when lovers are still in love. And MySQL can do it too with a little bit of love and care.

Since this article was released, I have received some feedback: mostly concerns about the risks involved in using MySQL. Well, I have actually used these techniques and they work, but I also agree that they are not a complete substitute for a database system that fully supports transactions.

You need to think about the risks involved in using these techniques with MySQL or similar databases. If you don't feel comfortable, I suggest you invest some money in getting a Relation Database Management System that supports transactions such as Interbase, Oracle or MSSQL 7.

You will still need to code with discipline even if you are using Oracle. It is possible to write buggy transaction code that will corrupt the data integrity on rollback. There's no substitute for good code.

Best wishes, and let's hope we never have to rollback our love-life.

Read/Post Responses (Join/Login first) 


首页要mysql表类型支持事务处理,我是用innoDB,当然你也可以用BDB,如果你的表属性不支持InnoDB, 请参考:mysql不支持innoDB怎么办? 然后新建一个测试表格    cr...
  • chenjiebin
  • chenjiebin
  • 2009年06月04日 14:32
  • 902


一、数据引擎innodb用begin,rollback,commit来实现提交事务处理,begin开始事务后出现错误就rollback事务回滚或者没有错误就commit提事务提交确认完成。   st...
  • wangyunfeis
  • wangyunfeis
  • 2017年08月16日 19:43
  • 70


PHP与MYSQL事务处理 /* MYSQL的事务处理主要有两种方法。 1、用begin,rollback,commit来实现 begin 开始一个事务 rollback 事务回...
  • u010817136
  • u010817136
  • 2015年04月25日 15:29
  • 1525


使用SQLiteDatabase的beginTransaction()方法可以开启一个事务,程序执行到endTransaction() 方法时会检查事务的标志是否为成功,如果程序执行到endTrans...
  • u013588712
  • u013588712
  • 2016年06月08日 16:24
  • 1910


采用PHP+MySQL,语言优势及特点 时间:2013-05-31 11:52 浏览: 2136 次 PHP+MySQL,LAMP模式   采用PHP+MySQL进行...
  • legnyu880412
  • legnyu880412
  • 2016年05月03日 11:42
  • 2195


需求说明:  案例背景:银行的转账过程中,发生意外是在所难免。为了避免意外而造成不必要的损失,使用事务处理的方式进行处理: A账户现有余额1000元,向余额为200的B账户进行转账500元。可能由于某...
  • hello_zhou
  • hello_zhou
  • 2016年07月09日 12:39
  • 7741


  • u013132035
  • u013132035
  • 2016年11月22日 23:40
  • 260


1:Java包含两种异常:checked异常和unchecked异常。checked和unchecked异常之间的区别是: Checked异常必须被显式地捕获try-catch-finally,而u...
  • u013628152
  • u013628152
  • 2015年08月25日 18:02
  • 4116


适合新手动手实践的PHP搭配mysql的小项目,本项目主要实现以下几点: 简单的用户注册简单的用户登录修改用户信息查看学生基本信息(查看学生以及对应的班级信息,实现分页)    2.实现注册页面...
  • jane9568
  • jane9568
  • 2016年02月04日 14:43
  • 494

PHP+mysql 入门级通讯录(一)

PHP+mysql 入门级通讯录PHPmysql 入门级通讯录 PHP入门第一步安装程序 准备工作一首先需要建立自己的数据库代码如下 二数据库建成后在sublime写一个连接数据库的程序进行测试 ...
  • qq_33541033
  • qq_33541033
  • 2017年08月07日 10:05
  • 254