Databases Reference
In-Depth Information
when it's hard to do, it's often worth it. (It's quite simple with the
pt-archiver
tool in
Percona Toolkit.)
Another workaround is to replace
INSERT ... SELECT
statements with a combination
of
SELECT INTO OUTFILE
followed by
LOAD DATA INFILE
on the master. This is fast and
doesn't require locking. It is admittedly a hack, but it's sometimes useful anyway. The
biggest issues are choosing a unique name for the output file, which must not already
exist, and cleaning up the output file when you're done with it. You can use the
CON
NECTION_ID()
technique we just discussed to ensure that the filename is unique, and
you can use a periodic job (
crontab
on Unix, scheduled tasks on Windows) to purge
unused output files after the connections that created them are finished with them.
You might be tempted to try to disable the locks instead of using these workarounds.
There is a way to do so, but it's not a good idea for most scenarios, because it makes it
possible for your replica to fall silently out of sync with the master. It also makes the
binary log useless for recovering a server. If, however, you decide that the risks are
worth the benefits, the configuration change that accomplishes this is as follows:
# THIS IS NOT SAFE!
innodb_locks_unsafe_for_binlog = 1
This allows a statement's results to depend on data it doesn't lock. If a second statement
modifies that data and then commits before the first statement, the two statements
might not produce the same results when you replay the binary log. This is true both
for replication and for point-in-time recovery.
To see how locking reads prevent chaos, imagine you have two tables: one without
rows, and one whose single row has the value
99
. Two transactions update the data.
Transaction 1 inserts the second table's contents into the first table, and transaction 2
updates the second (source) table, as depicted in
Figure 10-16
.
Step 2 in this sequence of events is very important. In it, transaction 2 tries to update
the source table, which requires it to place an exclusive (write) lock on the rows it wants
to update. An exclusive lock is incompatible with any other lock, including the shared
lock transaction 1 has placed on that row, so transaction 2 is forced to wait until trans-
action 1 commits. The transactions are serialized in the binary log in the order they
committed, so replaying these transactions in binary log (commit) order will give the
same results.
On the other hand, if transaction 1 doesn't place a shared lock on the rows it reads for
the
INSERT
, no such guarantee exists. Study
Figure 10-17
, which shows a possible se-
quence of events without the lock.
The absence of locks allows the transactions to be written to the binary log in an order
that will produce different results when that log is replayed, as you can see in the il-
lustration. MySQL logs transaction 2 first, so it will affect transaction 1's results on the
replica. This didn't happen on the master. As a result, the replica will contain different
data than the master.
