Behavior Change
Posted by: walid abdelnour
Date: July 15, 2014 06:45AM

Dear all,

We are currently migrating an internal solution from a UNIX / mysql 5.0.67 server to a RedHat / mysql 5.6.16 server.
The solution consists of:

-MySQL DB.
-Web site manager / web interface (war deployed on Glassfish).
-Background Java processes (long operations).

(Evreything is in java using same connector: mysql-connector-java-5.1.18-bin.jar)

The web interface is the starting point of the solution, (updating, inseting, deleting whatever is necessary)
THEN
it launches or stop the background processes.

The background processes are using the LOCK TABLE and UNLOCK TABLE command for access exclusion.
The solution has been running without problems on the old server for almost 3 years, however on
the new server the solution is acting differently.

What is happening is that, the background processes are being blocked until the web connection terminates
(by user loggout or tab closing...) whereas before this wasn't the case.

I can clearly see the blocking happening from the cli :

mysql> show processlist;
+-----+-------+-----------------+----------------+---------+------+---------------------------------+------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------+-----------------+----------------+---------+------+---------------------------------+------------------------------+
| 234 | user1 | localhost | NULL | Query | 0 | init | show processlist |
| 246 | user1 | localhost:56200 | myapp | Sleep | 21 | | NULL |
| 247 | user1 | localhost:56205 | myapp | Query | 13 | Waiting for table metadata lock | LOCK TABLES person WRITE |
| 248 | user1 | localhost:56206 | myapp | Query | 13 | Waiting for table metadata lock | LOCK TABLES person WRITE |
| 249 | user1 | localhost:56207 | myapp | Query | 13 | Waiting for table metadata lock | LOCK TABLES person WRITE |
| 250 | user1 | localhost:56208 | myapp | Query | 13 | Waiting for table metadata lock | LOCK TABLES person WRITE |
+-----+-------+-----------------+----------------+---------+------+---------------------------------+------------------------------+

It seems that even if only the DB/tcp connection is alive (persistent connection?) (but no actual sql commands are being sent)
the acquiring of the WRITE lock fails. Is this the expected behavior ? Did it change from version 5.0.67 to 5.6.16 ?
If yes, can the behavior be modified by a configuration setting or do we have to modify the web module ?

Thanks in advance for any tip.

Options: ReplyQuote


Subject
Written By
Posted
Behavior Change
July 15, 2014 06:45AM
August 11, 2014 02:24AM
August 14, 2014 11:29AM
August 18, 2014 01:59AM
August 19, 2014 04:09AM
August 23, 2014 01:30PM
September 01, 2014 11:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.