Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Trouble With High Volume Select Statments

Avatar of ckoning
ckoningFlag for United States of America asked on
PHPMySQL Server
4 Comments1 Solution391 ViewsLast Modified:
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 )
        updateRecord(id,sessionData);
    else
        // Then tries to insert duplicate row
        insertNewRecord(id,sessionData);
    return;
ASKER CERTIFIED SOLUTION
Avatar of Bernard Savonet
Bernard SavonetFlag of France imageCIO

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answers