PHP编程：PHP+MySQl的事务处置（兼回 luobutou 兄...

现在，也有了从事软件工程的想法，在经过了一个月的PHP培训学习之后，发现其实软件工程并没有想像中的那么难。mysql|事务处置   Simulating Transactions in MySQL
Wed, Sep 20, 2000; by John Lim.


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.


MySQL
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 invoice.id = $id;
   if no records returned
     DELETE FROM invitems WHERE cartid = $cartid;
   else
     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;
  UNLOCK TABLES;
  return $id;
}


Conclusion
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.


Feedback
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，设计数据库，学习PHP如何去连接MYSQL数据库。对于MYSQL，我并不陌生，因为学校开设了Linux系统的课程，对于数据库的操作。

个人呢觉得，配wamp 最容易漏的一步就是忘了把$PHP$目录下的libmysql.dll拷贝到windows系统目录的system32目录下，还有重启apache。

我要在声明一下：我是个菜鸟！！我对php这门优秀的语言也是知之甚少。但是我要在这里说一下php在网站开发中最常用的几个功能：

我还是推荐用firefox ，配上firebug 插件调试js能省下不受时间。谷歌的浏览器最好也不少用，因为谷歌的大侠们实在是太天才啦，把一些原来的js代码加了一些特效。

最后介绍一个代码出错，但是老找不到错误方法，就是 go to wc （囧），出去换换气没准回来就找到错误啦。

真正的方向了，如果将来要去开发团队，你一定要学好smarty ,phplib这样的模板引擎，

使用zendstdio 写代码的的时候，把tab 的缩进设置成4个空格是很有必要的

学习php的目的往往是为了开发动态网站，phper就业的要求也涵盖了很多。我大致总结为：精通php和mysql

php里的数组为空的时候是不能拿来遍历的；（这个有点低级啊，不过我刚被这个边界问题墨迹了好长一会）

真正的方向了，如果将来要去开发团队，你一定要学好smarty ,phplib这样的模板引擎，

至于模板嘛，各位高人一直以来就是争论不休，我一只小菜鸟就不加入战团啦，咱们新手还是多学点东西的好。

其实也不算什么什么心得，在各位大侠算是小巫见大巫了吧，望大家不要见笑，若其中有错误的地方请各位大虾斧正。

如果你已经到这种程度了，那么你已经可以做我的老师了。其实php也分很多的区域，

仓酷云

建数据库表的时候，int型要输入长度的，其实是个摆设的输入几位都没影响的，只要大于4就行，囧。

首先声明：我是一个菜鸟，是一个初学者。学习了一段php后总是感觉自己没有提高，无奈。经过反思我认为我学习过程中存在很多问题，我改变了学习方法后自我感觉有了明显的进步。

小鸟是第一次发帖（我习惯潜水的(*^__^*) 嘻嘻……），有错误之处还请大家批评指正，另外，前些日子听人说有高手能用php写驱动程序，真是学无止境，人外有人，天外有天。

装在C盘下面可以利用windows的ghost功能可以还原回来（顺便当做是重转啦），当然啦我的编译目录要放在别的盘下，不然自己的劳动成果就悲剧啦。

使用 jquery 等js框架的时候，要随时注意浏览器的更新情况，不然很容易发生框架不能使用。

至于模板嘛，各位高人一直以来就是争论不休，我一只小菜鸟就不加入战团啦，咱们新手还是多学点东西的好。

兴趣是最好的老师，百度是最好的词典。