PHP+MYSQL_locks.....please help .....i got stuck in this ..?

Question

I created a file in php and under this i wrote a code to lock a table with read mode. Below that file i wrote a insert operation into the same table, but as we know that read lock only supports read operation, meaning you can only view what is in the table, but you can't modify it. and it is running fine.

PROBLEM_EXIST_HERE: I created another file which contains the same insert operation into the same table. Here i'm only writing the insert operation, not the locking code.

Before using this file i used (RUN) the first file (which contains Locking+insert) operation and after that i opened the new tab in the same browser and run second file (containing only insert op.).

Now as we know that read lock is already fixed on the table, then why is the insert operation going on? Why won't it show me an error stating that the table is in READ locked mode and thus cannot be updated? Does anybody have any idea how to solve this problem?

1. And does anybody know how to know to see whether a table is locked in read or write condition in mysql db? Can anybody can tell me the php code for this? I have searched through the day and comes with no solution. Please write simple solution in simple language.

Answer 1

View the documentation for LOCK TABLE:

WRITE locks normally have higher priority than READ locks to ensure that updates 
are processed as soon as possible. This means that if one session obtains a READ 
lock and then another session requests a WRITE lock, subsequent READ lock requests 
wait until the session that requested the WRITE lock has obtained the lock and 
released it.

For InnoDB Tables, InnoDB has a Lock Monitor:

The InnoDB Lock Monitor is like the standard Monitor but also provides extensive lock
information. To enable this Monitor for periodic output, create a table named 
innodb_lock_monitor. 

More:

The Lock Monitor is the same as the standard Monitor except that it includes additional 
lock information. Enabling either monitor for periodic output by creating the associated
InnoDB table turns on the same output stream, but the stream includes the extra 
information if the Lock Monitor is enabled. For example, if you create the innodb_monitor 
and innodb_lock_monitor tables, that turns on a single output stream. The stream includes 
extra lock information until you disable the Lock Monitor by removing the 
innodb_lock_monitor table. 

Answer 2

Your another query is waiting for long time for lock to be resolved so that it can be executed. Currently, there is no direct way to put any counter, for checking if query exceeds certain threshold of time then it should be killed. There are certain indirect ways given here

There is no direct way of knowing whether table is locked by you or not. Sometimes MySQL also locks table while executing certain queries. You can get to know if table is locked or not by firing following query.

SHOW OPEN TABLES FROM <database name> WHERE `table` = "<table_name>";

In this query if in_use column has value 1 then table is locked, if 0 then not locked.

EDIT

There is one other way as well using Information_schema's processlist information.

Run the query:

SELECT * FROM information_schema.`PROCESSLIST` WHERE db="<DB name>" AND command = "Query";

From the result check out what is the "state" column message is? It will give you more insight into what is happening. It will also give you Query ID through which you can also kill the query using KILL <Query ID>. But for doing all these things, you will be required to make new connection to MySQL server. You will also get to know time for which query has been running.