1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Greenflash
  • 21
  • 2
  • Consider using a different storage for the sessions. Either a non-clustered MariaDB instance, or something like redis, memcached which both support locking or even putting `/var/lib/php/sessions/` on a shared network mount that supports locks. – jlh Aug 09 '23 at 06:47
  • Thanks, but I'm afraid using something other than a clustered MariaDB system is not an option. – Greenflash Aug 24 '23 at 15:30

1 Answers1

-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.

    }
}
danblack
  • 12,130
  • 2
  • 22
  • 41
  • Thanks. But how do I lock the row so that other database sessions can't read it? – Greenflash May 24 '23 at 08:46
  • You don't need to. As the HA Galera database is there, sessions should be able to be read by any node. When [write](https://www.php.net/manual/en/sessionhandlerinterface.write.php), I guess there's a possibility that two nodes might deadlock resulting in 0 or 1 successful writes. Like all deadlocks, a application space resolution of an appropriate response is needed (`write` the latest based on a timestamp in `$data` maybe). – danblack May 24 '23 at 23:11
  • But I need to lock other processes (eg Ajax calls) from reading the session data until the process that has the lock has written out the new data. On a standard MySQL database I can achieve this with GET_LOCK, but this isn't possible with Galera Cluster. Nor is it possible using SELECT FOR UPDATE because nested transactions aren't supported. – Greenflash May 26 '23 at 09:52
  • Set a cookie to [wsrep_last_seen_gtid](https://mariadb.com/kb/en/wsrep_last_seen_gtid/) after an update, and before retreival [wsrep_sync_wait_upto_gtid](https://mariadb.com/kb/en/wsrep_sync_wait_upto_gtid/) to ensure you retrieve the latest that user/application is aware of. – danblack May 26 '23 at 22:11