MySQL 4.1.0 Chinese reference manual---6.7 mysql transaction and Lockdown command

mysql| Reference | Reference manual | Chinese MySQL 4.1.0 chinese reference manual---dog (heart sail) Translate 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 perform an update, MySQL will immediately store the update on disk.

If you use a Transaction security table (such as InnoDB, BDB), you can set MySQL as a non-autocommit mode by following the command:

SET autocommit=0

After that, you must use a COMMIT to store your changes to disk, or use ROLLBACK, if you want to ignore the changes you made from the start of your transaction.

If you want to convert a series of statements from Autocommit mode, you can use the 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;

The start TRANSACTION is added to the MySQL 4.0.11, which is the recommended way to start a special (Ad-hoc) transaction, as this is the 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 autocommit mode state.

When you update a non-transaction table, if you execute a ROLLBACK, you will get an error (Er_warning_not_complete_rollback) as a warning. All transaction security tables will be restored, but non-transactional security tables will not change.

If you use the START TRANSACTION or SET autocommit=0, you should use the MySQL binary log as a backup to replace the old update log. Transactions are stored in a binary log in a large form, on a COMMIT, to protect the rollback transaction, rather than being stored. View the chapter 4.9.42 in-process logs. If you use a start transaction or set autocommit=0, you should use the MySQL binary log to replace older update logs for backups. Transactions are stored in binary logins a chunk, do, guarantee, roll transaction processing is not stored. See also section 4. 9.4 Binary log.

The following command automatically ends a transaction (as if you made a COMMIT before executing the command):
Command Command command ALTER TABLE BEGIN CREATE indexdrop DATABASE DROP TABLE RENAME tabletruncate
You can use SET TRANSACTION isolation level ... Change the isolation level of a transaction. View Chapter 6.7.3 SET TRANSACTION syntax.
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 frees all locks owned by the current thread. When a thread issues another lock table, or when a connection to the server is closed, all tables locked by the current thread are automatically unlocked.

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 SELECT, insert, DELETE, and UPDATE permissions on the table.

The main reason for using LOCK tables is to emulate transaction processing or to get faster speeds when updating tables. There will be a more detailed description thereafter.

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

The difference between read local and read is that when the lock is loaded, read local allows non-conflict (non-conflicting) INSERT statements to execute. If you manipulate the database files from MySQL when you load the lock, this will still not be used.

When you use lock tables, you must lock all the tables that you will use, and you must use the same alias as you would use in your query! If you use a table multiple times in a query (with aliases), you must obtain a lock for each alias.

The WRITE lock has higher permissions than the READ lock to ensure that the update is processed as quickly as possible. This means that if a thread obtains a READ lock and another thread requests a WRITE lock, the concurrent READ lock request waits until the write thread gets the lock and releases it. You can use the low_priority write lock, which allows other threads to get the READ lock when the thread is waiting for the write lock. You should only use the Low_priority WRITE lock if you are sure it will be the last time when no thread will own the READ lock.

Lock tables work as follows: Sort all locked tables in an internally defined order (the order is ambiguous from the user's standpoint). 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, only to the thread to get all the locks.
This scenario is designed to ensure that the table locks the deadlock release. There are still other things you need to know about this pattern:

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 gets a write lock and waits for a lock on the next table in the Locked table list, all other threads will wait for the write lock to be freed. If this raises a serious problem in your application, you should consider converting some of your tables into transaction security tables.

You can use Kill to safely kill a thread that is locking the table. View Chapter 4.5.5 KILL syntax.

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

Typically, you do not need to lock any tables, because all single UPDATE statements are atomic, and other threads cannot interfere with the currently executing SQL statement. When you want to lock the table anyway, here are some things:
If you're running a lot of operations on a bunch of tables, it's quicker to lock down the table you're going to use. Of course there are disadvantages, other threads will not be able to update a read-locked table, and there is no other thread to read a WRITE-lock table. Some things run faster in lock tables because MySQL will not dump the purge lock table key cache until UNLOCK tables are called (usually the key cache is dumped after each SQL statement). This accelerates inserts, updates, and deletes 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 an UPDATE. The following example shows that for safe execution, lock TABLES are required:
mysql> LOCK TABLES Trans READ, customer write;mysql> SELECT SUM (value) from Trans WHERE Customer_id=some_id;mysql&gt ; UPDATE customer SET total_value=sum_from_previous_statement-> WHERE customer_id=some_id;mysql> UNLOCK TABLES;
Without LOCK tables, it is possible that another thread might insert a new row of records into the trans table during the execution of the SELECT and UPDATE statements.
By using the incremental update (update customer SET value=value+new_value) or the last_insert_id () function, you can avoid using LOCK TABLES in many cases.

You can also use user-level locking functions Get_lock () and Release_lock () to resolve situations where these locks are stored in a hash table on the server and Pthread_mutex_lock () and Pthread_mutex_unlock () Implemented to achieve high speed. View Chapter 6.3.6.2 accessibility functions.

See chapter 5.3.1 How MySQL locks a table to get more information about locking scenarios.

You can use the FLUSH tables with the read lock command to lock all tables in all databases with a read lock. View the Chapters 4.5.3 FLUSH syntax. If you have a file system that can create snapshots of files in time, such as Veritas, this will be a very convenient way to get backup.

Note: Lock tables are not transaction-safe and will automatically commit all active transactions 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 (not started) transaction. If you use the Global keyword, the statement sets the default global transaction isolation level for all new connections established at that point. In order to do this, you need to have SUPER privileges. Use the session keyword to set the default transaction isolation level for all future transactions that are currently connected.

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