(mysql.info.gz) ANSI diff Transactions

Info Catalog

(mysql.info.gz) ANSI diff SELECT INTO TABLE

(mysql.info.gz) Differences from ANSI

(mysql.info.gz) ANSI diff Triggers

 
 1.5.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.   Storage engines.
 
 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.
 
 With MySQL Server supporting 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 table
 types lies mostly in performance. Transactional tables have
 significantly higher memory and diskspace requirements, and more CPU
 overhead.  On the other hand, transactional table types 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 table types?
 
   1. 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.
 
   2. 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 you never will get an automatic abort from
      the server, which is a common problem with transactional database
      systems.
 
   3. Even a transactional system can lose data if the server goes down.
      The difference between different systems lies in just how small the
      time-lag is where they could lose data. No system is 100% secure,
      only "secure enough." Even Oracle, reputed to be the safest of
      transactional database systems, is reported to sometimes lose data
      in such situations.
 
      To be safe with MySQL Server, whether or not using transactional
      tables, you only need to have backups and have binary logging
      turned on. With this 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, 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 will stall until any 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 new inserted records will not be seen by
 the client that has the read lock until it releases the lock.  With
 `INSERT DELAYED', you can queue inserts into a local queue, until the
 locks are released, without having the client wait for the insert to
 complete.  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 will never be an
 automatic rollback (which can happen with transactional tables if you
 are not very careful).  MySQL Server also guarantees that there will
 not be any 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 use 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 everything is okay.
 
        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 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 columns relative to their current value.
 
         * Update only those columns 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 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' and/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()'
DONTPRINTYET       SQL function or the `mysql_insert_id()' C API function.  
      Information functions.  *Note `mysql_insert_id()':
DONTPRINTYET       SQL function or the `mysql_insert_id()' C API function.  
      Information functions.   `mysql_insert_id()'

      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. 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 it as though MySQL Server changed the preceding
      query to:
 
           UPDATE TBL_NAME SET row_flag=1 WHERE id=ID AND row_flag <> 1;
 

Info Catalog

(mysql.info.gz) ANSI diff SELECT INTO TABLE

(mysql.info.gz) Differences from ANSI

(mysql.info.gz) ANSI diff Triggers