Preamble
As many of us know through the master slave node in RDS, by default famous PHP frameworks such as PHP, Symfony all support config master slave endpoint, for efficient query rotation, WRITE queries will be transferred. via the WRITE node, the READ (SELECT) queries will be passed to the READ node.
Problem.
And this is where the problem begins. ECCUBE is written based on symfony 4, but custom style:
All requests are wrapped in a transaction query of the DB query, even though the request is only a READ query
src/Eccube/EventListener/TransactionListener.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | /** * Kernel request listener callback. * * @param GetResponseEvent $event */ public function onKernelRequest(GetResponseEvent $event) { if (!$this->isEnabled) { log_debug('Transaction Listener is disabled.'); return; } if (!$event->isMasterRequest()) { return; } /** @var Connection $Connection */ $Connection = $this->em->getConnection(); if (!$Connection->isConnected()) { $Connection->connect(); } $Connection->setAutoCommit(false); $Connection->setTransactionIsolation(TransactionIsolationLevel::READ_COMMITTED); $this->em->beginTransaction(); log_debug('Begin Transaction.'); } |
And then the story goes further 🥲🥲🥲 , for doctrine2 (query library used in Symfony ECCUBE, an ORM library similar to Laravel’s Eloquent), queries written in a default transaction will be moved to the WRITE node ( because it considers this as an upcoming query). TOANG!!!!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | /** * Primary-Replica Connection * * Connection can be used with primary-replica setups. * * Important for the understanding of this connection should be how and when * it picks the replica or primary. * * 1. Replica if primary was never picked before and ONLY if 'getWrappedConnection' * or 'executeQuery' is used. * 2. Primary picked when 'exec', 'executeUpdate', 'executeStatement', 'insert', 'delete', 'update', 'createSavepoint', * 'releaseSavepoint', 'beginTransaction', 'rollback', 'commit', 'query' or * 'prepare' is called. * 3. If Primary was picked once during the lifetime of the connection it will always get picked afterwards. * 4. One replica connection is randomly picked ONCE during a request. * * ATTENTION: You can write to the replica with this connection if you execute a write query without * opening up a transaction. For example: * * $conn = DriverManager::getConnection(...); * $conn->executeQuery("DELETE FROM table"); * * Be aware that Connection#executeQuery is a method specifically for READ * operations only. * * Use Connection#executeStatement for any SQL statement that changes/updates * state in the database (UPDATE, INSERT, DELETE or DDL statements). * |
Solution
Here we need to customize the connection class a bit, create a CustomMasterSlaveConnection.php file that inherits MasterSlaveConnection, the important point here is the connect() method. They are overriden the connect method to select the appropriate WRITE READ node, implement an additional flag to lock, in this case we can force the query to run on any NODE.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | <?php namespace CustomizeDoctrine; use DoctrineDBALConnectionsMasterSlaveConnection; use DoctrineDBALDriverConnection; use DoctrineDBALDriverPDOConnection; use InvalidArgumentException; class CustomMasterSlaveConnection extends MasterSlaveConnection implements Connection { private $currentConnectionName = null; protected $lock = false; /** * Check is slave node. */ public function isSlave() { return $this->currentConnectionName == 'replica'; } public function getCurrentConnectionName() { return $this->currentConnectionName; } /** * Lock change connection. */ public function lock() { $this->lock = true; return $this; } /** * Unlock change connection. */ public function unlock() { $this->lock = false; return $this; } /** * @param string|null $connectionName * * @return bool */ public function forceConnect($connectionName = null) { if ($connectionName !== 'master' && $connectionName !== 'slave') { throw new InvalidArgumentException('Invalid option to connect(), only master or slave allowed.'); } if ($connectionName === 'master') { $connectionName = 'primary'; } if ($connectionName === 'slave') { $connectionName = 'replica'; } if (isset($this->connections[$connectionName])) { $this->_conn = $this->connections[$connectionName]; $this->currentConnectionName = $connectionName; return false; } $this->connections[$connectionName] = $this->_conn = $this->connectTo($connectionName); $this->currentConnectionName = $connectionName; return true; } /** * @param string|null $connectionName * * @return bool */ public function connect($connectionName = null) { if ($connectionName === 'master') { $connectionName = 'primary'; } if ($connectionName === 'slave') { $connectionName = 'replica'; } if ($this->lock) { return false; } return $this->performConnect($connectionName); } } |
Usage is quite easy, just lock() at the beginning of the segment you want to use the READ node, then use the unlock() function after that.
1 2 3 4 5 6 | $conn = $this->entityManager->getConnection(); $conn->lock()->forceConnect('slave'); // do something query SELECT // ... $conn->unlock(); |
Conclude
Hopefully the above is a solution to help you customize the ECCUBE framework to choose the right READ WRITE node to meet the system’s load capacity.
Tks for reading!!!