(mysql.info) ansi-diff-transactions
Info Catalog
(mysql.info) ansi-diff-select-into-table
(mysql.info) differences-from-ansi
(mysql.info) ansi-diff-triggers
1.9.5.3 Transactions and Atomic Operations
..........................................
MySQL Server (version 3.23-max and all versions 4.0 and above) supports
transactions with the `InnoDB' and `BDB' transactional storage engines.
`InnoDB' provides _full_ `ACID' compliance. See
storage-engines. For information about `InnoDB' differences from
standard SQL with regard to treatment of transaction errors, see
innodb-error-handling.
The other non-transactional storage engines in MySQL Server (such as
`MyISAM') follow a different paradigm for data integrity called `atomic
operations.' In transactional terms, `MyISAM' tables effectively always
operate in `AUTOCOMMIT=1' mode. Atomic operations often offer
comparable integrity with higher performance.
Because MySQL Server supports both paradigms, you can decide whether
your applications are best served by the speed of atomic operations or
the use of transactional features. This choice can be made on a
per-table basis.
As noted, the trade-off for transactional versus non-transactional
storage engines lies mostly in performance. Transactional tables have
significantly higher memory and disk space requirements, and more CPU
overhead. On the other hand, transactional storage engines such as
`InnoDB' also offer many significant features. MySQL Server's modular
design allows the concurrent use of different storage engines to suit
different requirements and deliver optimum performance in all
situations.
But how do you use the features of MySQL Server to maintain rigorous
integrity even with the non-transactional `MyISAM' tables, and how do
these features compare with the transactional storage engines?
* If your applications are written in a way that is dependent on
being able to call `ROLLBACK' rather than `COMMIT' in critical
situations, transactions are more convenient. Transactions also
ensure that unfinished updates or corrupting activities are not
committed to the database; the server is given the opportunity to
do an automatic rollback and your database is saved.
If you use non-transactional tables, MySQL Server in almost all
cases allows you to resolve potential problems by including simple
checks before updates and by running simple scripts that check the
databases for inconsistencies and automatically repair or warn if
such an inconsistency occurs. Note that just by using the MySQL
log or even adding one extra log, you can normally fix tables
perfectly with no data integrity loss.
* More often than not, critical transactional updates can be
rewritten to be atomic. Generally speaking, all integrity problems
that transactions solve can be done with `LOCK TABLES' or atomic
updates, ensuring that there are no automatic aborts from the
server, which is a common problem with transactional database
systems.
* To be safe with MySQL Server, regardless of whether you use
transactional tables, you only need to have backups and have
binary logging turned on. When that is true, you can recover from
any situation that you could with any other transactional database
system. It is always good to have backups, regardless of which
database system you use.
The transactional paradigm has its benefits and its drawbacks. Many
users and application developers depend on the ease with which they can
code around problems where an abort appears to be necessary, or is
necessary. However, even if you are new to the atomic operations
paradigm, or more familiar with transactions, do consider the speed
benefit that non-transactional tables can offer on the order of three to
five times the speed of the fastest and most optimally tuned
transactional tables.
In situations where integrity is of highest importance, MySQL Server
offers transaction-level reliability and integrity even for
non-transactional tables. If you lock tables with `LOCK TABLES', all
updates stall until integrity checks are made. If you obtain a `READ
LOCAL' lock (as opposed to a write lock) for a table that allows
concurrent inserts at the end of the table, reads are allowed, as are
inserts by other clients. The newly inserted records are not be seen by
the client that has the read lock until it releases the lock. With
`INSERT DELAYED', you can write inserts that go into a local queue
until the locks are released, without having the client wait for the
insert to complete. See concurrent-inserts, and
insert-delayed.
`Atomic,' in the sense that we mean it, is nothing magical. It only
means that you can be sure that while each specific update is running,
no other user can interfere with it, and there can never be an
automatic rollback (which can happen with transactional tables if you
are not very careful). MySQL Server also guarantees that there are no
dirty reads.
Following are some techniques for working with non-transactional tables:
* Loops that need transactions normally can be coded with the help
of `LOCK TABLES', and you don't need cursors to update records on
the fly.
* To avoid using `ROLLBACK', you can employ the following strategy:
1. Use `LOCK TABLES' to lock all the tables you want to access.
2. Test the conditions that must be true before performing the
update.
3. Update if the conditions are satisfied.
4. Use `UNLOCK TABLES' to release your locks.
This is usually a much faster method than using transactions with
possible rollbacks, although not always. The only situation this
solution doesn't handle is when someone kills the threads in the
middle of an update. In that case, all locks are 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 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
information-functions, and 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;
Info Catalog
(mysql.info) ansi-diff-select-into-table
(mysql.info) differences-from-ansi
(mysql.info) ansi-diff-triggers
automatically generated byinfo2html