[Catalyst] Session duplicate key constraints on concurrent requests

[Janne Snabb]

On Fri, 7 Oct 2011, Janne Snabb wrote:

> I think the proper way to solve it is to drop the constraint on the
> cookie and just insert the cookie and have an auto_increment ID in
> the table. And when reading, select the cookie with the highest ID
> (because there might be several).

Something like this perhaps? Untested code.

--
Janne Snabb / EPIPE Communications
snabb at epipe.com - http://epipe.com/

diff -U5 -r Catalyst-Plugin-Session-Store-DBI-0.16/lib/Catalyst/Plugin/Session/Store/DBI.pm Catalyst-Plugin-Session-Store-DBI-0.16+sessionpatch//lib/Catalyst/Plugin/Session/Store/DBI.pm
--- Catalyst-Plugin-Session-Store-DBI-0.16/lib/Catalyst/Plugin/Session/Store/DBI.pm	2010-03-24 04:47:13.000000000 +0700
+++ Catalyst-Plugin-Session-Store-DBI-0.16+sessionpatch//lib/Catalyst/Plugin/Session/Store/DBI.pm	2011-10-08 01:13:30.183410460 +0700
@@ -120,10 +120,14 @@
 
 sub session_store_dbi_id_field {
     return shift->_session_plugin_config->{'dbi_id_field'} || 'id';
 }
 
+sub session_store_dbi_selector_field {
+    return shift->_session_plugin_config->{'dbi_selector_field'} || 'selector';
+}
+
 sub session_store_dbi_data_field {
     return shift->_session_plugin_config->{'dbi_data_field'} || 'session_data';
 }
 
 sub session_store_dbi_expires_field {
@@ -148,11 +152,11 @@
     my ( $table, $id_field, $data_field, $expires_field ) =
         map { $c->${\"session_store_$_"} }
             qw/dbi_table dbi_id_field dbi_data_field dbi_expires_field/;
     $c->_session_sql( {
         get_session_data        =>
-            "SELECT $data_field FROM $table WHERE $id_field = ?",
+            "SELECT $data_field FROM $table WHERE $id_field = ? ORDER BY selector ASC LIMIT 1",
         get_expires             =>
             "SELECT $expires_field FROM $table WHERE $id_field = ?",
         check_existing          =>
             "SELECT 1 FROM $table WHERE $id_field = ?",
         update_session          =>
@@ -338,11 +342,12 @@
 
 =head1 SYNOPSIS
 
     # Create a table in your database for sessions
     CREATE TABLE sessions (
-        id           char(72) primary key,
+        id           char(72) key,
+        selector     int auto_increment primary key,
         session_data text,
         expires      int(10)
     );
 
     # In your app
@@ -354,20 +359,22 @@
         dbi_dsn   => 'dbi:mysql:database',
         dbi_user  => 'foo',
         dbi_pass  => 'bar',
         dbi_table => 'sessions',
         dbi_id_field => 'id',
+        dbi_selector_field => 'selector',
         dbi_data_field => 'session_data',
         dbi_expires_field => 'expires',
     });
     
     # Or use an existing database handle from a DBIC/CDBI class
     MyApp->config('Plugin::Session' => {
         expires   => 3600,
         dbi_dbh   => 'DBIC', # which means MyApp::Model::DBIC
         dbi_table => 'sessions',
         dbi_id_field => 'id',
+        dbi_selector_field => 'selector',
         dbi_data_field => 'session_data',
         dbi_expires_field => 'expires',
     });
 
     # ... in an action:
@@ -423,10 +430,17 @@
 =head2 dbi_id_field
 
 The name of the field on your sessions table which stores the session ID.
 Defaults to C<id>.
 
+=head2 dbi_selector_field
+
+The name of the field on your sessions table which is used to differentiate
+amongst multiple instances of the id field (which happens when multiple
+instances try to store the same session at the same time).
+Defaults to C<selector>.
+
 =head2 dbi_data_field
 
 The name of the field on your sessions table which stores session data.
 Defaults to C<session_data>.
 
@@ -435,13 +449,14 @@
 The name of the field on your sessions table which stores the expiration
 time of the session. Defaults to C<expires>.
 
 =head1 SCHEMA
 
-Your 'sessions' table must contain at minimum the following 3 columns:
+Your 'sessions' table must contain at minimum the following 4 columns:
 
-    id           char(72) primary key
+    id           char(72) key
+    selector     int auto_increment primary key
     session_data text
     expires      int(10)
 
 The 'id' column should probably be 72 characters. It needs to handle the
 longest string that can be returned by