Oracle 5.0, Справочное руководство

Страница: 1139 / 3232

SET TRANSACTION
Syntax
1119
13.3.5.3. Table-Locking Restrictions and Conditions
You can safely use
KILL
to terminate a session that is waiting for a table lock. See Section 13.7.6.3,
“
KILL
Syntax” .
You should not lock any tables that you are using with
INSERT DELAYED
. An
INSERT DELAYED
in this case results in an error because the insert must be handled by a separate thread, not by the
session which holds the lock.
LOCK TABLES
and
UNLOCK TABLES
cannot be used within stored programs.
Normally, you do not need to lock tables, because all single
UPDATE
statements are atomic; no other
session can interfere with any other currently executing SQL statement. However, there are a few
cases when locking tables may provide an advantage:
• If you are going to run many operations on a set of
MyISAM
tables, it is much faster to lock the tables
you are going to use. Locking
MyISAM
tables speeds up inserting, updating, or deleting on them
because MySQL does not flush the key cache for the locked tables until
UNLOCK TABLES
is called.
Normally, the key cache is flushed after each SQL statement.
The downside to locking the tables is that no session can update a
READ
-locked table (including the
one holding the lock) and no session can access a
WRITE
-locked table other than the one holding
the lock.
• If you are using tables for a nontransactional storage engine, you must use
LOCK TABLES
if you
want to ensure that no other session modifies the tables between a
SELECT
and an
UPDATE
. The
example shown here requires
LOCK TABLES
to execute safely:
LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id= some_id ;
UPDATE customer
SET total_value= sum_from_previous_statement
WHERE customer_id= some_id ;
UNLOCK TABLES;
Without
LOCK TABLES
, it is possible that another session might insert a new row in the
trans
table
between execution of the
SELECT
and
UPDATE
statements.
You can avoid using
LOCK TABLES
in many cases by using relative updates (
UPDATE customer
SET value=value+new_value
) or the
LAST_INSERT_ID()
[961] function. See Section 1.8.5.3,
“Transactions and Atomic Operations” .
You can also avoid locking tables in some cases by using the user-level advisory lock functions
GET_LOCK()
[965] and
RELEASE_LOCK()
[967] . These locks are saved in a hash table in the server
and implemented with
pthread_mutex_lock()
and
pthread_mutex_unlock()
for high speed.
See Section 12.14, “Miscellaneous Functions” .
See Section 8.7.1, “Internal Locking Methods” , for more information on locking policy.
13.3.6.
SET TRANSACTION
Syntax
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
This statement sets the transaction isolation level, used for operations on
InnoDB
tables.
Scope of the Isolation Level
You can set the isolation level globally, for the current session, or for the next transaction: