Oracle 5.0, Reference Manual

Page: 1266 / 3232

InnoDB
Startup Options and System Variables
1246
that enabling this variable still does not permit operations such as
UPDATE
to overtake other similar
operations (such as another
UPDATE
) even when they affect different rows.
Consider the following example, beginning with this table:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
In this case, table has no indexes, so searches and index scans use the hidden clustered index for
record locking (see
Section 14.2.9.1, “Clustered and Secondary Indexes” ).
Suppose that one client performs an
UPDATE
using these statements:
SET autocommit = 0;
UPDATE t SET b = 5 WHERE b = 3;
Suppose also that a second client performs an
UPDATE
by executing these statements following
those of the first client:
SET autocommit = 0;
UPDATE t SET b = 4 WHERE b = 2;
As
InnoDB
executes each
UPDATE
, it first acquires an exclusive lock for each row,
and then determines whether to modify it. If
InnoDB
does not modify the row and
innodb_locks_unsafe_for_binlog
[1244] is enabled, it releases the lock. Otherwise,
InnoDB
retains the lock until the end of the transaction. This affects transaction processing as follows.
If
innodb_locks_unsafe_for_binlog
[1244] is disabled, the first
UPDATE
acquires x-locks and
does not release any of them:
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
The second
UPDATE
blocks as soon as it tries to acquire any locks (because first update has
retained locks on all rows), and does not proceed until the first
UPDATE
commits or rolls back:
x-lock(1,2); block and wait for first UPDATE to commit or roll back
If
innodb_locks_unsafe_for_binlog
[1244] is enabled, the first
UPDATE
acquires x-locks and
releases those for rows that it does not modify:
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)
The second
UPDATE
proceeds part way before it blocks. It begins acquiring x-locks, and blocks when
it tries to acquire one for a row still locked by first
UPDATE
. The second
UPDATE
does not proceed
until the first
UPDATE
commits or rolls back:
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); block and wait for first UPDATE to commit or roll back
In this case, the second
UPDATE
must wait for a commit or rollback of the first
UPDATE
, even though
it affects different rows. The first
UPDATE
has an exclusive lock on row (2,3) that it has not released.
As the second
UPDATE
scans rows, it tries to acquire an exclusive lock for that same row, which it
cannot have.
•
innodb_log_arch_dir