Hi all
Today, we had another replag problem, caused by Aka's user stats script.
Replag kept rising even after the script was stopped (not killed).
Apparently, the reason lies with the transaction mode used: a long
running query may lock (parts of) the tables it uses, denying any
updates the replication daemon wants to make.
As far as I know, this should not happen if the long running query uses
READ UNCOMMITTED (dirty read). Using it should not cause serious
problems in our context, save a bit of resources and allow replication
to continue while reading from tables. Details on transaction isolation
levels can be found here:
<http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html>
DaB. has set the global default isolation level to READ UNCOMMITTED for
now. If you are experiencing problems or need data that is guarantied to
be consistent under all circumstances, *and* your query does not take
long (no more than, say, a minute or so), you can change the isolation
level like this:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
REPEATABLE READ is MySQL's "normal" default, i.e. what you have been
using until now if you did not set the isolation level explicitly. There
are other options, look at the documentation link above.
I hope this actually does solve the problem, and does not cause you too
much trouble.
Regards
-- Daniel
--
Homepage:
http://brightbyte.de