effects of innodb_locks_unsafe_for_binlog parameter

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I'm hoping someone can clarify the side-effects of setting the
innodb_locks_unsafe_for_binlog parameter for me.

I'm running a fairly standard web application hitting a MySQL 5.0
backend, currently using the default transaction isolation level of
repeatable-read.  We're seeing a fair amount of lock contention, and
think it would be safe to move down to read-committed.  We also want to
disable next-key locking as this also causes some contention that just
moving to read-committed does not eliminate.  So it appears that we
need to set innodb_locks_unsafe_for_binlog to do this.

I'm OK with this potentially causing some phantom reads in the
application, nothing is so critical that a few errors like this will
cause damage, but I definitely do NOT want to lose point-in-time
recover capabilities from the binary log.  I didn't see too much
documentation here
(http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html) as to
what the "unsafe" effects of setting this parameter on the binary log

If I set innodb_locks_unsafe_for_binlog to "on", will I just miss some
transactions in my binary log?  Or does this have the potential to
render it completely useless for point-in-time recovery?

Any guidance is much appreciated.


Re: effects of innodb_locks_unsafe_for_binlog parameter


if INSERT ... SELECT ... does a 'phantom read', that can spoil your binlog.
It will not replay in the exact same way that the original execution went.
That can break replication, and also point-in-time recovery from a backup
using the binlog. InnoDB's normal crash recovery will still work, though.

MySQL-5.1 will have 'row-based binlogging'. Then using the option
innodb_locks_unsafe_for_binlog will be safe.

In practice, innodb_locks_unsafe_for_binlog is probably safe to use for most
applications if you only do very simple SQL statements. Then phantoms are
not a problem.

Best regards,


Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM

Quoted text here. Click to load it

Site Timeline