Custom PHP Session Handler locking with MariaDB cluster

Question

Has anyone got a solution for implementing locking in a custom PHP session handler that stores sessions in the database when the database is a MariaDB cluster?

The problem is that the session handler needs to implement locking (the default file based session handler uses flock()). However GET_LOCK isn't supported by MariaDB Galera cluster and MySQL doesn't support nested transactions, so I can't do a SELECT FOR UPDATE because the first transaction commit will release the row.

Answer 1

Lucky databases are pretty good at locking. In Galera when you have an AUTO_INCREMENT column the auto_increment_increment and auto_increment_offset are automagically set to avoid conflicts inserting/ aka creating sessions on those tables.

So take a table structure like:

CREATE TABLE session (
id bigint unsigned NOT NULL PRIMARY KEY,
sid varbinary(80),
data varchar(16384),
INDEX sid_idx (sid))

Rough draft (to the extent of my poor php skills):

class SessionHandler implements SessionHandlerInterface, SessionIdInterface {

    private $dbh, $stm_read, $stm_write;

    public __construct)
    {
      $dbh = new PDO("mysql:host=localhost;dbname=app", 'my_user', 'my_password');
      $stm_read =  $dbh->prepare("SELECT data FROM sessions WHERE sid = :sid");
      $stm_write =  $dbh->prepare("INSERT INTO sessions (sid, data) VALUES (:sid, :data) ON DUPLICATE KEY UPDATE data= :data1");
    }

    public function read($id)
    {
      $stm_read->execute([$id]);
      $rs = $stm_read->fetchAll();
      foreach ($rs as $r)
      {
        return $r['data'];
      }
    }

    ....

    public create_sid()
    {
       // something unique, even better if derived/appended from the MariaDB session variable @@auto_increment_increment which is unique in cluster.

    }
}