Search code examples
phpmysqlmysqlimaster-slave

MySQL Master/Slaves, asynchronous query and unintended table locking?

I just face some weird problems about SELECT query and table locking(?) on mysql master/salve environment.

I do have 1 master and 2 slaves. All of my tables are innoDB (some of them used to be MyISAM). I use php with mysqli to asynchronous query from both slaves in parallel.

The problem is, during my parallel query are being processed, all others MySQL related tasks are on hold, even I try to select from master that has no task at all, until all of query are finished for some reasons.

I suspect that because of table locking ??? I did try "ISOLATION LEVEL READ UNCOMMITTED" and changed all tables to innoDB but still no luck. I don't know where to diagnostic anymore

UPDATE #1 : Just found more clues. During intensive select in slaves, new connections from PHP to master will be held/freeze until select queries on slaves are finished.

UPDATE #2 : Found more clues. Just change all leftover mysql connection to mysqli. It works!...but on some page that do not use session_start(). Why session_start() ?? How can session_start() hold my connection to my database anyway??

UPDATE #3 : FOUND A SOLUTION. I will post below

Solution

  • FOUND A SOLUTION

    There are 2 problems combined.

    1. I still have normal mysql connection/queries co-exist with mysqli. Just change all of it to mysqli. I don't know what or why it can't works together. But change it all to mysqli can fix my problem at some point.

    2. I just learned that PHP's session can be locked as well. If your pages take a lot of time to compute/process. Don't forget to put session_write_close() to prevent session locking in PHP.