[UPHPU] MySQL Locking Question
breece at doba.com
Tue Aug 7 11:54:21 MDT 2007
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.
Chad Sollis wrote:
> 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
> 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?
> Any help/direction would be great.
> Thank you in advance.
> UPHPU mailing list
> UPHPU at uphpu.org
> IRC: #uphpu on irc.freenode.net
More information about the UPHPU