Write-read sequences can be tricky for users of MySQL replication, if what has been written needs to be available for reading immediately. Slaves could be behind. You have to run the sequence on the master. If MySQL semisynchronous replication would tell the client which slave is in sync, a client library could select that slave for load balancing.

The more we look into load balancing for PECL/mysqlnd_ms, the PHP mysqlnd master-slave replication load balancing proof-of-concept plugin, the more restrictions and issues come up. An obvious restriction is that connections have a state.

No solution: binlog monitoring

But not only state is a "trouble maker". Critical write-read sequences can be another one. If the client must be able to "see" the result of a write send to the master immediately, the safest choice is to run the next read on the master. The downside is what the master gets some read load. Alternatively, the client could try to monitor replication binlog or replication lag to choose a slave who has already replicated the write. If it hasn’t been replicated yet, the load balancer could block the client for a wait period until a slave catches up before the load balancer sends the read to the master. Uuups, conceptually that is what semisynchronous replication does. Of course, the server does it in a much more elegant way. And, if the server told PECL/mysqlnd_ms which slave has catched up…

No solution: global transaction ID

Another solution to the critical write-read sequence is using a global transaction ID as proposed by Jan in "MySQL Proxy and a Global Transaction ID". The idea is to maintain some sort of transaction ID or record version which is replicated by the server. The load balancer will pick a slave that, for example, has already replicated the critical write by checking the global transaction ID.

Shall PECL/mysqlnd_ms use a global transaction ID?

PECL/mysqlnd_ms could do the magic of maintaining a global transaction ID in C. Let us know if you want this. To me it does not really sound as it is a bit of a hack, but who cares about hacks, if it solves problems…