Oracle 5.0 Reference Manual Download

Page: 1295 / 3232

InnoDB

Multi-Versioning

1275

• Add well-chosen indexes to your tables. Then your queries need to scan fewer index records and

consequently set fewer locks. Use

EXPLAIN SELECT

to determine which indexes the MySQL server

regards as the most appropriate for your queries.

• Use less locking. If you can afford to permit a

SELECT

to return data from an old snapshot, do not

add the clause

FOR UPDATE

LOCK IN SHARE MODE

to it. Using the

READ COMMITTED

[1121]

isolation level is good here, because each consistent read within the same transaction reads from its
own fresh snapshot. You should also set the value of

innodb_support_xa

[1250]

to 0, which will

reduce the number of disk flushes due to synchronizing on disk data and the binary log.

• If nothing else helps, serialize your transactions with table-level locks. The correct way to use

LOCK TABLES

with transactional tables, such as

InnoDB

tables, is to begin a transaction with

SET

autocommit = 0

(not

START TRANSACTION

) followed by

LOCK TABLES

, and to not call

UNLOCK

TABLES

until you commit the transaction explicitly. For example, if you need to write to table

and

read from table

, you can do this:

SET autocommit=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

... do something with tables t1 and t2 here ...

COMMIT;

UNLOCK TABLES;

Table-level locks make your transactions queue nicely and avoid deadlocks.

• Another way to serialize transactions is to create an auxiliary “semaphore” table that contains just

a single row. Have each transaction update that row before accessing other tables. In that way, all
transactions happen in a serial fashion. Note that the

InnoDB

instant deadlock detection algorithm

also works in this case, because the serializing lock is a row-level lock. With MySQL table-level
locks, the timeout method must be used to resolve deadlocks.

14.2.8.

InnoDB

Multi-Versioning

Because

InnoDB

is a multi-versioned storage engine, it must keep information about old versions of

rows in the tablespace. This information is stored in a data structure called a rollback segment (after an
analogous data structure in Oracle).

Internally,

InnoDB

adds three fields to each row stored in the database. A 6-byte

DB_TRX_ID

field

indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a
deletion is treated internally as an update where a special bit in the row is set to mark it as deleted.
Each row also contains a 7-byte

DB_ROLL_PTR

field called the roll pointer. The roll pointer points to an

undo log record written to the rollback segment. If the row was updated, the undo log record contains
the information necessary to rebuild the content of the row before it was updated. A 6-byte

DB_ROW_ID

field contains a row ID that increases monotonically as new rows are inserted. If

InnoDB

generates a

clustered index automatically, the index contains row ID values. Otherwise, the

DB_ROW_ID

column

does not appear in any index.

InnoDB

uses the information in the rollback segment to perform the undo operations needed in a

transaction rollback. It also uses the information to build earlier versions of a row for a consistent read.

Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs
are needed only in transaction rollback and can be discarded as soon as the transaction commits.
Update undo logs are used also in consistent reads, but they can be discarded only after there is no
transaction present for which

InnoDB

has assigned a snapshot that in a consistent read could need

the information in the update undo log to build an earlier version of a database row.

You must remember to commit your transactions regularly, including those transactions that issue only
consistent reads. Otherwise,

InnoDB

cannot discard data from the update undo logs, and the rollback

segment may grow too big, filling up your tablespace.

The physical size of an undo log record in the rollback segment is typically smaller than the
corresponding inserted or updated row. You can use this information to calculate the space need for
your rollback segment.

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands