effects of innodb_locks_unsafe_for_binlog parameter

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.ht ml) as to
what the "unsafe" effects of setting this parameter on the binary log
are.

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.

Thanks,
Saran

Re: effects of innodb_locks_unsafe_for_binlog parameter

Saran,

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,

Heikki

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
tables
http://www.innodb.com/order.php



"saran" kirjoitti
viestissä:1138837159.884329.119280@f14g2000cwb.googlegroups. com...
> 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.ht ml) as to
> what the "unsafe" effects of setting this parameter on the binary log
> are.
>
> 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.
>
> Thanks,
> Saran
>