Shulou(Shulou.com)06/01 Report--

MySQL 4.1.0 Chinese reference manual-- 6.7 MySQL transaction and locking commands [@ more@] code {color:purple} tt {color:green} samp {color:navy} pre {color:maroon} MYSQL 4.1.0 Chinese reference manual > words "content=" MySQL,4.1.0,Shuixin13,MySQL 4.1.0, Chinese, Chinese reference manual Dog (Xinfan) > CSS rel=STYLESHEET > MySQL Reference Manual for version 4.1.0-alpha.6.7 MySQL transaction and locking command 6.7.1 BEGIN/COMMIT/ROLLBACK syntax

By default, MySQL runs in autocommit mode. This means that when you finish performing an update, MySQL will immediately store the update on disk.

If you use transaction safety tables (such as Innodb, BDB), you can set MySQL to non-autocommit mode with the following command:

SET AUTOCOMMIT=0

After that, you must use COMMIT to store your changes to disk, or use ROLLBACK if you want to ignore changes made since your transaction.

If you want to convert from AUTOCOMMIT mode for a series of statements, you can use START TRANSACTION or BEGIN or BEGIN WORK statements:

START TRANSACTION; select @ A:=SUM (salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; COMMIT

START TRANSACTION was added in MySQL 4.0.11; this is the recommended way to start a special (ad-hoc) transaction because it is ANSI SQL syntax.

Note that if you are using a non-transactional security table, the changes are stored immediately and are not constrained by the state of the autocommit mode.

When you update a non-transactional table, if you execute a ROLLBACK, you will get an ER_WARNING_NOT_COMPLETE_ROLLBACK as a warning. All transaction security tables will be restored, but non-transaction security tables will not be changed.

If you use START TRANSACTION or SET AUTOCOMMIT=0, you should use the MySQL binary log for backup instead of the old update log. The transaction is stored in a large chunk in the binary log, above the COMMIT, to protect the rolled-back transaction, not the stored one. Check out the section 4.9.4 binary log. If you use startup transactions or set AUTOCOMMIT=0, you should use MySQL binary logs for backups instead of older update logs. Transactions are stored in a large block of binary logins, do, guarantee, and roll transactions are not stored. See section 4. 9.4 binary log.

The following command automatically ends a transaction (as if you made a COMMIT before executing the command):

Command ALTER TABLE BEGIN CREATE INDEX DROP DATABASE DROP TABLE RENAME TABLE TRUNCATE

You can use SET TRANSACTION ISOLATION LEVEL... Change the isolation level of the transaction. Check the syntax of section 6.7.3 SET TRANSACTION.

6.7.2 LOCK TABLES/UNLOCK TABLES syntax

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}...]. UNLOCK TABLES

LOCK TABLES locks the table for the current thread. UNLOCK TABLES releases all locks owned by the current thread. When a thread issues another LOCK TABLES, or when the connection to the server is closed, all tables locked by the current thread are automatically unlocked.

In order to use LOCK TABLES in MySQL 4.0.2, you must have a global LOCK TABLES permission and a SELECT permission on the related table. In MySQL 3.23, you need to have SELECT, insert, DELETE, and UPDATE permissions on the table.

The main reason for using LOCK TABLES is to emulate transactions or get faster updates to tables. There will be a more detailed description later.

If a thread gets an READ lock on a table, that thread (and all other threads) can only read from the table. If a thread acquires an WRITE lock on a table, only the thread that owns the lock can read and write the table from the table. Other threads are blocked.

The difference between READ LOCAL and READ is that READ LOCAL allows non-conflicting INSERT statements to execute when locks are loaded. If you manipulate database files from outside MySQL while you are loading locks, this will still not be available.

When you use LOCK TABLES, you must lock all the tables you will use and must use the same alias as you will use in your query! If you use a table (with an alias) multiple times in a query, you must get a lock for each alias.

WRITE locks have higher permissions than READ locks to ensure that updates are processed as soon as possible. This means that if one thread acquires a READ lock while another thread requests a WRITE lock, the concurrent READ lock request will wait until the WRITE thread acquires the lock and releases it. You can use LOW_PRIORITY WRITE locks, which will allow other threads to acquire READ locks when the thread is waiting for a WRITE lock. You should only use LOW_PRIORITY WRITE locks if you are sure this will be the last time, when no thread will own READ locks.

LOCK TABLES works as follows:

Sorts all locked tables in an internally defined order (which is ambiguous from the user's point of view). If a table is locked with a read lock and a write lock, the write lock is placed before the read lock. Lock only one table at a time until the thread gets all the locks.

This scheme is designed to ensure that the table lock deadlock is released. There are still some other things you need to know about this model:

If you use a LOW_PRIORITY WRITE lock on a table, this means that MySQL will wait for the lock until no thread requests a READ lock. When a thread acquires a WRITE lock and waits for a lock on the next table in the lock table list, all other threads wait for the WRITE lock to be released. If this causes a serious problem in your application, you should consider converting some of your tables to transaction safety tables.

You can use KILL to safely kill a thread that is locking a table. See section 4.5.5 KILL syntax.

Note that you should not lock the table on which you are using INSERT DELAYED. This is because, in this case, INSERT is done through a separate thread.

In general, you don't need to lock any tables because all single UPDATE statements are atomic; other threads cannot interfere with the currently executed SQL statement. When you want to lock the table anyway, here are some things:

If you run a lot of operations on a bunch of tables and lock the tables you are going to use, it will be faster. There is a downside, of course, that other threads will not be able to update a READ-locked table, and no other thread will want to read a WRITE-locked table. The reason something runs faster under LOCK TABLES is that MySQL will not dump and clear the locked table key cache until UNLOCK TABLES is called (usually the key cache is cleared after each SQL statement). This will speed up inserts, updates, and deletions on the MyISAM table. If you are using a storage engine that does not support transactions in MySQL, you must use LOCK TABLES if you want to ensure that no other threads appear between a SELECT and a UPDATE. The following example shows that in order to execute safely, LOCK TABLES is required:

Mysql > LOCK TABLES trans READ, customer WRITE; mysql > SELECT SUM (value) FROM trans WHERE customer_id=some_id; mysql > UPDATE customer SET total_value=sum_from_previous_statement-> WHERE customer_id=some_id; mysql > UNLOCK TABLES

Without LOCK TABLES, it may happen that another thread may insert a row of new records in the trans table during the execution of SELECT and UPDATE statements.

You can avoid using LOCK TABLES in many cases by using the incremental update (UPDATE customer SET value=value+new_value) or LAST_INSERT_ID () function.

You can also use the user-level locking functions GET_LOCK () and RELEASE_LOCK () to solve situations where these locks are stored in a hash table on the server and implemented in pthread_mutex_lock () and pthread_mutex_unlock () for high speed. See Section 6.3.6.2 accessibility functions.

See section 5.3.1 how MySQL locks the table for more information about the locking scheme.

You can use the FLUSH TABLES WITH READ LOCK command to lock all tables in all databases with a read lock. Check the syntax of section 4.5.3 FLUSH. If you have a file system that can take snapshots of files in time, such as Veritas, this will be a very convenient way to get backups.

Note: LOCK TABLES is not transaction safe and all active transactions are automatically committed before attempting to lock a table.

6.7.3 SET TRANSACTION syntax

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Sets the transaction isolation level for the global, entire session, or next transaction.

The default behavior is to set the isolation level for the next (unstarted) transaction. If you use the GLOBAL keyword, the statement sets the default global transaction isolation level for all new connections made at that point. In order to do this, you need to have SUPER permissions. Use the SESSION keyword to set the default transaction isolation level for all future transactions of the current connection.

You can use-- transaction-isolation=... Sets the default global isolation level for mysqld. See section 4.1.1 mysqld command line options.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.