mysql - do locks propagate over replication?

Question

I have a Mysql master-slave(s) replication with MyISAM tables. All updates are done on the master and selects are done on either the master or slaves.

It appears that we might need to manually lock a few tables when we do certain updates. While this write lock is on the tables, no selects can happen on the locked table. But what about on the slaves? Does the lock propagate out?

Say I have table_A and table_B. I initiate a lock on table_A and table_B on the master and start performing the update. At this time no other connection can read table_A and table_B off the master? But what if at this time another connection tries to read the tables off of a slave, can they do so?

Frustule

Hi Adam, did you find an answer to this? – Redmond

This is an argument for moving to InnoDB -- to avoid table locks. – Inhabited

Answer 1

Everything that MySQL replicates can be found in the binary logs.

You can run the following command to see the details.

show global variables like 'log_bin%';

log_bin_basename will tell you the path to your binary logs with base file name.

and run

show binary logs

to find the binary files that are currently present on your server.

You can check the actual commands that are written to the file by using mysqlbinlog command together with the file name or by running show binlog events ... from the MySQL CLI.

Also, check what binlog_format are you using.

Basically - the lock of the tables is not directly propagated to slaves, but at the time, whey will execute the performed updates they will perform a lock of the updated table if needed.

Lowther

Answer 2

As far as I know write locks do not propagate into the binlog, You can verify that by doing quick test and looking at the binlog. If you want to avoid issues on the master aswell and for some reason can not migrate to InnoDB consider integrating something like GET_LOCK() into your application instead of completely locking a table. MyISAM is quite iffy when it comes to concurrency.

Multiversity