[UPHPU] MySQL Locking Question

[Chad Sollis]

The process actually only clears out the sessions on "garbage collection"
which is still occuring too frequently.  I like your suggestion of having
the job run once or twice a day, and remove it from the garbage collection
process.


~Chad

On 8/7/07, Lonnie Olson <olsonl at fungus.sosstaffing.com> wrote:
>
> 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
> > (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(session_date_created))>86400
> >
> > 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.
>
> Pros:
>   Write locks caused by the DELETE only occur during maintenance.
>   Increased performance.
> Cons:
>   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.
>
> --lonnie
>
>


-- 
/--------------------------/
  Chad Sollis
  chad at sollis.net
  801.792.7651