I have written a custom php session handler which uses the default session id's but stores the session information in a MySQL database. The site running the manager handles roughly 30k visitors a day, each of which generate roughly 5 session writes and lookups in very rapid succession due to AJAX requests. The same issue has arisen using MS SQL Server as a storage platform. The problem occurs on session writes. The write function first tries to locate an existing entry in the session table with the session id (used as the primary key) and then either inserts or updates the record accordingly. Occasionally (1%-5% of session writes) the initial lookup comes back empty even though the session is not new, and there is a row in the database attached to the session id. The logic then tries to insert a new record, only to run afoul of primary key uniqueness constraint. This causes unacceptable failure. The failure is handled relatively gracefully, but provides a significantly degraded user experience. After many attempts to resolve the issue, I have been unsuccessful. The table is using InnoDB as an engine and utf8_general_ci as a character set. The web app is running on Zend Framework and using the framework's DB functionality. The problem still occurs when reglar mysqli connections are employed instead. Any insight would be greatly appreciated. Psuedo code for the session write is provided below.
function sessionWrite( id, sessionData )
sql = 'select 1 from sessions where sessionId = id';
rows = db->query(sql); // occasionally comes back with
// null result set even though
// session record exists
if( count(rows) > 0 )
// Then tries to insert duplicate row