mysql5.1新特性--關(guān)于transaction 一段難懂的話

cocolala

You can also use functions to update records in a single operation. You can get a very efficient application by using the following techniques:

Modify columns relative to their current value.

Update only those columns that actually have changed.

For example, when we are updating customer information, we update only the customer data that has changed and test only that none of the changed data, or data that depends on the changed data, has changed compared to the original row. The test for changed data is done with the WHERE clause in the UPDATE statement. If the record wasn't updated, we give the client a message: “Some of the data you have changed has been changed by another user.” Then we show the old row versus the new row in a window so that the user can decide which version of the customer record to use.
This gives us something that is similar to column locking but is actually even better because we only update some of the columns, using values that are relative to their current values. This means that typical UPDATE statements look something like these:
UPDATE tablename SET pay_back=pay_back+125;
UPDATE customer
  SET
    customer_date='current_date',
    address='new address',
    phone='new phone',
    money_owed_to_us=money_owed_to_us-125
  WHERE
    customer_id=id AND address='old address' AND phone='old phone';
This is very efficient and works even if another client has changed the values in the pay_back or money_owed_to_us columns.

In many cases, users have wanted LOCK TABLES or ROLLBACK for the purpose of managing unique identifiers. This can be handled much more efficiently without locking or rolling back by using an AUTO_INCREMENT column and either the
LAST_INSERT_ID()
SQL function or the
mysql_insert_id()
C API function. See
Section 11.11.3, “Information Functions”
, and
Section 27.2.3.37, “mysql_insert_id()”
.
You can generally code around the need for row-level locking. Some situations really do need it, and InnoDB tables support row-level locking. Otherwise, with MyISAM tables, you can use a flag column in the table and do something like the following:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
MySQL returns 1 for the number of affected rows if the row was found and row_flag wasn't 1 in the original row. You can think of this as though MySQL Server changed the preceding statement to:
UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag  1;


本文來(lái)自ChinaUnix博客，如果查看原文請(qǐng)點(diǎn)：http://blog.chinaunix.net/u2/67276/showart_718407.html