2

I have a MySQL DB and an innoDB table in it. I have a php page that connects, locks the table, does some updates, then unlocks the table. The PHP page is being served up with apache via wamp.

The php page uploads a file to the database. I decided to simulate the system crashing by uploading a file that has a size larger than the memory that is allocated to PHP. This definitely caused this error: allowed memory size of 18874368 bytes exhausted (tried to allocate 6176754 bytes). After that, the tables that were locked during the updates are still locked.

The error I get when I try to access the tables after this error is: Table 'a' was not locked with LOCK TABLES. I know that it is a lock problem because I will bring up a SQL prompt and attempt to select from the table that was locked, and it just waits, exactly like it does when the table is locked. If I then kill the Apache process, the statement that I attempted to run in a SQL prompt will finally go through. My guess is that when I kill the Apache process, MySQL realizes that the table lock should be released due to the connection being severed.

Any ideas??

jworrin
  • 825
  • 1
  • 8
  • 20

3 Answers3

6

Qoute from: http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional). If the client reconnects, the locks will no longer be in effect. In addition, if the client had an active transaction, the server rolls back the transaction upon disconnect, and if reconnect occurs, the new session begins with autocommit enabled.

As your connection is persistent after the page has executed and completed / disposed of the connection is still present.

You should not make the connection persistent IMO

RobertPitt
  • 56,863
  • 21
  • 114
  • 161
3

Ok, so after reading my post, I realized that I kind of answered my own question. The connection was persisting, even through the crash, because I was using mysql_pconnect() to connect to the database. I changed it to mysql_connect(), and it worked just fine. Sorry if I wasted anyone's time, but I hope this helps someone out!!

--Joshua

jworrin
  • 825
  • 1
  • 8
  • 20
0

You could use a shutdown function to unlock the tables:

http://php.net/manual/en/function.register-shutdown-function.php

qbert220
  • 11,220
  • 4
  • 31
  • 31