How to cope without COMMIT-ROLLBACK

MySQL doesn't support COMMIT-ROLLBACK. The problem with COMMIT-ROLLBACK is that to handle this efficiently it would require a completely different table layout than MySQL uses today. MySQL would also need extra threads that do automatic cleanups on the tables and the disk usage would be much higher. This would make MySQL about 2-4 times slower than it is today. One of the reasons that MySQL is so much faster than almost all other SQL databases (typical times are at least 2-3 times faster) is the lack of COMMIT-ROLLBACK.

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

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

We have transactions and cursors on the TODO but not quite prioritised. If it is implemented it will be as a option to CREATE TABLE. That means that COMMIT-ROLLBACK will only work on those tables and only those tables will be slower.

We at TcX have a greater need for a real fast database than a 100% general database. Whenever we find a way to implement these 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 prioritise things at the moment. Customers with higher levels of support can alter this, so things may be reprioritised.

The current problem is actually ROLLBACK. Without ROLLBACK you can do anything with LOCK TABLES. To support ROLLBACK 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), but if one wants to have ROLLBACK with ALTER/DROP/CREATE TABLE it would make everything much harder to implement.

To avoid using ROLLBACK one can do:

1. LOCK TABLES ...
2. Test conditions.
3. Update if everything is ok.
4. UNLOCK TABLES

This is usually much faster, but not always. The only thing this doesn't handle if someone does a kill on the process.

One can also use functions to update things in one operation. By doing all updates relatively and/or only updating those fields that actually have changed one can get a very efficient application.

For example, when we are doing updates on some customer information, we only update the customer data that has changed and only test that none of the changed data, or data that depends on the changed data, has changed in the original row. The test for change 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", and then we show the old row versus the new row in a window. The user can then decide which version of the customer record he should use.

This gives us something like 'column locking' but actually even better, because we only update some of the columns with relative information. This means that a typical update statement looks something like:

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+'new_money' where
customer_id=id and address='old address' and phone='old phone';

As you can see, this is very efficient and even if another client has changed the 'money_he_owes_us' or 'pay_back' amount this will still work.

In many cases, users have wanted ROLLBACK and/or LOCK TABLES to manage unique identifiers for some tables. This can be handled much more efficiently by using an AUTO_INCREMENT column and the MySQL API function mysql_insert_id. See section How can I get the unique ID for the last inserted row?

At TcX we have never had any need for row level locking as we have always been able to code around it. I know some cases that really need row locking, but they are very few. If you want to have row level locking you can do something like:

UPDATE table_name SET row_flag=1 WHERE id=ID;

MySQL returns affected rows = 1 if the row was found and row_flag wasn't 1 in the original document. On the TODO there is GET_LOCK and RELEASE_LOCK for those that want to implement application level locking.

Go to the first, previous, next, last section, table of contents.