Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
February 1, 2006, 11:39 pm
rate this thread
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
(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.
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
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum