By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,649 Members | 2,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,649 IT Pros & Developers. It's quick & easy.

effects of innodb_locks_unsafe_for_binlog parameter

P: n/a
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/...arameters.html) 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

Feb 1 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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" <sa*********@gmail.com> kirjoitti
viestissä:11**********************@f14g2000cwb.goo glegroups.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/...arameters.html) 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

Feb 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.