![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961288.webp)
The
InnoDB
Transaction Model and Locking
1268
• Consistent read does not work over
ALTER TABLE
because
ALTER TABLE
works by making a
temporary copy of the original table and deleting the original table when the temporary copy is
built. When you reissue a consistent read within a transaction, rows in the new table are not visible
because those rows did not exist when the transaction's snapshot was taken.
InnoDB
uses a consistent read for select in clauses like
INSERT INTO ... SELECT
,
UPDATE ...
(SELECT)
, and
CREATE TABLE ... SELECT
that do not specify
FOR UPDATE
or
LOCK IN SHARE
MODE
if the
innodb_locks_unsafe_for_binlog
[1244]
option is set and the isolation level of the
transaction is not set to
SERIALIZABLE
[1121]
. Thus, no locks are set on rows read from the selected
table. Otherwise,
InnoDB
uses stronger locks and the
SELECT
part acts like
READ COMMITTED
[1121]
,
where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
14.2.7.3.
SELECT ... FOR UPDATE
and
SELECT ... LOCK IN SHARE MODE
Locking
Reads
In some circumstances, a consistent (nonlocking) read is not convenient and a locking read is required
instead.
InnoDB
supports two types of locking reads:
•
SELECT ... LOCK IN SHARE MODE
sets a shared mode lock on the rows read. A shared mode
lock enables other sessions to read the rows but not to modify them. The rows read are the latest
available, so if they belong to another transaction that has not yet committed, the read blocks until
that transaction ends.
• For index records the search encounters,
SELECT ... FOR UPDATE
blocks other sessions from
doing
SELECT ... LOCK IN SHARE MODE
or from reading in certain transaction isolation levels.
Consistent reads will ignore any locks set on the records that exist in the read view. (Old versions of
a record cannot be locked; they will be reconstructed by applying undo logs on an in-memory copy of
the record.)
Locks set by
LOCK IN SHARE MODE
and
FOR UPDATE
reads are released when the transaction is
committed or rolled back.
As an example of a situation in which a locking read is useful, suppose that you want to insert a new
row into a table
child
, and make sure that the child row has a parent row in table
parent
. The
following discussion describes how to implement referential integrity in application code.
Suppose that you use a consistent read to read the table
parent
and indeed see the parent row of the
to-be-inserted child row in the table. Can you safely insert the child row to table
child
? No, because
it is possible for some other session to delete the parent row from the table
parent
in the meantime
without you being aware of it.
The solution is to perform the
SELECT
in a locking mode using
LOCK IN SHARE MODE
:
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
A read performed with
LOCK IN SHARE MODE
reads the latest available data and sets a shared mode
lock on the rows read. A shared mode lock prevents others from updating or deleting the row read.
Also, if the latest data belongs to a yet uncommitted transaction of another session, we wait until that
transaction ends. After we see that the
LOCK IN SHARE MODE
query returns the parent
'Jones'
, we
can safely add the child record to the
child
table and commit our transaction.
Let us look at another example: We have an integer counter field in a table
child_codes
that we
use to assign a unique identifier to each child added to table
child
. It is not a good idea to use either
consistent read or a shared mode read to read the present value of the counter because two users
of the database may then see the same value for the counter, and a duplicate-key error occurs if two
users attempt to add children with the same identifier to the table.
Here,
LOCK IN SHARE MODE
is not a good solution because if two users read the counter at the same
time, at least one of them ends up in deadlock when it attempts to update the counter.
In this case, there are two good ways to implement reading and incrementing the counter:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...