[UPHPU] MySQL Locking Question
lists at kittypee.com
Tue Aug 7 14:36:50 MDT 2007
On Tue, 2007-08-07 at 11:44 -0600, Chad Sollis wrote:
> SELECT session_id, session_array FROM session_table WHERE
> session_key='keygoeshere' limit 0,1
> DELETE FROM session_table WHERE
> Can I reconstruct these queries to not lock the table. I understand when
> creating/updating the table it needs to lock, but I am under the assumption
> that for select and delete it does not need to lock?
I suggest taking a different approach. Instead of running the DELETE
query at every single session lookup, just add the date expiration to
the WHERE clause in the SELECT. This way the table will have old items,
but you can clean them out as maintenance once every so often.
Write locks caused by the DELETE only occur during maintenance.
Lookups for sessions will be slightly slower, but can be helped by an
index on session_date_created.
Requires separate maintenance to be performed, but could be automated.
More information about the UPHPU