Mysql – Read after transaction from secondary session not visible

concurrencyMySQLmysql-5.7redistransaction

We've got a database (MySQL ) that has a client 'per channel'.
When a player changes channel from, say, 1 to 2, we have to save the state of the player to the database.

Saving is done in multiple transactions (per table), which include in most cases a DELETE FROM table WHERE player = X; INSERT INTO table VALUES (...);. Some other tables require only an UPDATE statement.

The problem is as follows:

Player starts moving from channel 1 to channel 2
Channel 1 registers transition in Redis, making sure that player is bounced on Channel 2 until player is saved.
Player disconnects from channel 1
Player connects to channel 2, gets asked to reconnect (bounce)
Channel 1 received disconnection, saves character, disables bouncing
Player connects to channel 2, gets accepted
Channel 2 loads info from database --THIS IS OUTDATED INFO--

I'm about to, instead of deleting the Redis key, to change the TTL of the key to a couple seconds to prevent it from loading old info. But it doesn't guarantee anything, as the database is a system on its own…

For the record, there's a MariaDB instance that acts as a slave for backup purposes.

Best Answer

If you can install Mysql UDF Plugin, you can using lib_mysqludf_redis plugin to synchronous records between Mysql and Redis by Mysql trigger.

Here is a example:

CREATE TABLE `player_channel_log` (
  id       int         AUTO_INCREMENT PRIMARY KEY,
  date     datetime,
  player   int,
  channel  varchar(32)
);

set up a trigger for the table and call redis command by mysql udf

DELIMITER $$
CREATE TRIGGER `after_insert_player_channel_log`
AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
  DO `redis`('redis://@127.0.0.1/0/', 'SET', new.`player`, new.`channel`);
END $$
DELIMITER ;

then you can get value by player id

from mysql:

mysql>  SELECT `redis`('redis://@127.0.0.1/0/', 'GET', <player id>)\G
*************************** 1. row ***************************
`redis`('redis://@127.0.0.1/0/', 'GET', <player id>): {
        "out":  <channel>
}

from redis-cli utility: 

$ redis-cli -h 127.0.0.1 -n 0 GET <player id>
"<channel>"
Related Question