[UPHPU] MySQL Locking Question

[Ben Reece]

Chad,
    Unfortunately, the MyISAM engine has no way to not lock tables when 
running queries.  A SELECT statement will do a read lock, and you can 
have many read locks at a time, so you can query your session table 
without problems.  When UPDATEing or DELETEing, you get a write lock, 
which must wait until all read locks are cleared, and all other queries 
must wait until the write lock is cleared.  So if your DELETE statement 
takes 20 seconds to run, your table will be locked during that time, 
preventing any SELECTs for those 20s.

The other engines have options to allow dirty reads, but MyISAM doesn't, 
unless use the MySQL handlers instead of queries, but it's a lot more work.

Ben

Chad Sollis wrote:
> Greetings,
>
> I have a question for any MySQL pros out there.  I am using a custom
> session_handler built in PHP.  The table is built on MyISAM engine.  These
> queries are locking up the table as they occur creating some slowness on the
> site.
>
> 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?
>
> Any help/direction would be great.
>
> Thank you in advance.
>
> ~Chad
>
> _______________________________________________
>
> UPHPU mailing list
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
>