List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 2 1999 5:25pm
Subject:GET_LOCK() and mysqld spinning out of control.
View as plain text  
>>>>> "Scott" == Scott Hess <scott@stripped> writes:

Scott> While we're on the subject of GET_LOCK()...
Scott> We have an operation with a critical path, and for sake of consistency
Scott> cannot allow multiple instances (as from seperate web servers) of this
Scott> operation to be in that critical path at the same time.  Previously, we had
Scott> implemented this as:

Scott>     LOCK TABLES table WRITE;
Scott>         critical_operation here;

Scott> Unfortunately, the table in question is hit very frequently, and as time
Scott> went by, though there aren't mnay critical_operations in an absolute sense,
Scott> we found that consecutive critical_operation instances were able to starve
Scott> the rest of the system.  If the next critical_operation or a bunch of
Scott> updates were ready to go before the previous critical_operation was
Scott> finished, they would get the lock before the other locked processes goth
Scott> their change to read from the table.  We couldn't use a LOW_PRIORITY lock,
Scott> because we couldn't guarantee during peak times that there would ever be
Scott> pauses for the lock to be aquired.

Scott> Since all other updates to the table are well-behaved WRT
Scott> critical_operation, we decided to remove the write lock on that table, and
Scott> just use a lock specific to critical_operation.  Our first attempt was:

Scott>     GET_LOCK('mylock',1000);
Scott>         critical_operation here;
Scott>     RELEASE_LOCK('mylock');

Scott> Unfortunately, the next morning, the server wedged itself.  There was no
Scott> idle time on the machine, and the cycles were 80% sys and 20% user.  We
Scott> could not connect to the mysql server.  After five minutes or so of zero
Scott> disk i/o, we killed the server manually.  Anyone have any ideas of why this
Scott> might have happened?  I've been unable to replicate it "in the wild".

Scott> [We fixed the problem by replicating the data important to
Scott> critical_operation into a seperate table, which we write lock again,
Scott> something like:

Scott>     LOCK TABLES alt_table WRITE;
Scott>         critical_operation here;
Scott>     update the original table with the results of critical_operation's
Scott> consistency checks;

Scott> This has worked very well, but is somewhat of a hack.]


GET_LOCK() uses pthreads_mutex() locks and should be safe.  It would
be nice if you can repeate this somehow.

You may be able to do this by modifying the '' script

GET_LOCK() and mysqld spinning out of control.Scott Hess28 Jul
  • GET_LOCK() and mysqld spinning out of control.Michael Widenius2 Aug