GNU Info

(mysql.info)Commit-rollback

---

Next: Bugs Prev: Standards Up: Compatibility

---

Enter node , (file) or (file)node

How to Cope Without `COMMIT'/`ROLLBACK'
---------------------------------------

The following mostly applies only for `ISAM', `MyISAM', and `HEAP'
tables. If you only use transaction-safe tables (`BDB', or `InnoDB'
tables) in an an update, you can do `COMMIT' and `ROLLBACK' also with
MySQL.  Note: COMMIT.

The problem with handling `COMMIT'-`ROLLBACK' efficiently with the
above table types would require a completely different table layout
than MySQL uses today.  The table type would also need extra threads
that do automatic cleanups on the tables, and the disk usage would be
much higher. This would make these table types about 2-4 times slower
than they are today.

For the moment, we prefer implementing the SQL server language
(something like stored procedures). With this you would very seldom
really need `COMMIT'-`ROLLBACK.' This would also give much better
performance.

Loops that need transactions normally can be coded with the help of
`LOCK TABLES', and you don't need cursors when you can update records
on the fly.

We at TcX had a greater need for a real fast database than a 100%
general database. Whenever we find a way to implement these features
without any speed loss, we will probably do it. For the moment, there
are many more important things to do. Check the TODO for how we
prioritize things at the moment. (Customers with higher levels of
support can alter this, so things may be reprioritized.)

The current problem is actually `ROLLBACK'. Without `ROLLBACK', you can
do any kind of `COMMIT' action with `LOCK TABLES'. To support
`ROLLBACK' with the above table types, MySQL would have to be changed
to store all old records that were updated and revert everything back
to the starting point if `ROLLBACK' was issued. For simple cases, this
isn't that hard to do (the current `isamlog' could be used for this
purpose), but it would be much more difficult to implement `ROLLBACK'
for `ALTER/DROP/CREATE TABLE'.

To avoid using `ROLLBACK', you can use the following strategy:

  1. Use `LOCK TABLES ...' to lock all the tables you want to access.

  2. Test conditions.

  3. Update if everything is okay.

  4. Use `UNLOCK TABLES' to release your locks.

This is usually a much faster method than using transactions with
possible `ROLLBACK's, although not always. The only situation this
solution doesn't handle is when someone kills the threads in the middle
of an update. In this case, all locks will be released but some of the
updates may not have been executed.

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 fields relative to their current value.

   * Update only those fields that actually have changed.

For example, when we are doing updates to some customer information, we
update only the customer data that has changed and test only that none
of the changed data, or data that depend 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 have been changed by another user". Then we show the old row
versus the new row in a window, so the user can decide which version of
the customer record he should 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+'relative change';
     
     UPDATE customer
       SET
         customer_date='current_date',
         address='new address',
         phone='new phone',
         money_he_owes_us=money_he_owes_us+'new_money'
       WHERE
         customer_id=id AND address='old address' AND phone='old phone';

As you can see, this is very efficient and works even if another client
has changed the values in the `pay_back' or `money_he_owes_us' columns.

In many cases, users have wanted `ROLLBACK' and/or `LOCK TABLES' for
the purpose of managing unique identifiers for some tables. This can be
handled much more efficiently by using an `AUTO_INCREMENT' column and
either the SQL function `LAST_INSERT_ID()' or the C API function
`mysql_insert_id()'. Note: `mysql_insert_id()'.

At MySQL AB, we have never had any need for row-level locking because
we have always been able to code around it. Some cases really need row
locking, but they are very few. If you want row-level locking, you can
use a flag column in the table and do something like this:

     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 already 1 in the original row.

You can think of it as MySQL changed the above query to:

     UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;