Re: table locking expected behavior
Both implied and explicit read locks are still a read lock, however a read lock that had been obtained will prevent any writes to the table.
Difference between implicit and explicit locks.
1. A select statement will cause an implicit read lock within the execution of the select, release after the select completes. If there are multiple selects, the read lock is obtained for each select separately, so writes is possible in between the multiple selects.
2. An explicit read lock will keep the lock until it is released. You can do multiple select between the LOCK TABLES and UNLOCK TABLES and you are sure that no data in the tables you have read locked is changed in between the multiple selects.
Take an example:
LOCK TABLE t1 READ, t2 READ;
SELECT * FROM t1;
SELECT * FROM t2;
UNLOCK TABLES;
With the explicit LOCK TABLES, you can be sure that there is no changes to the tables in between your selects. So, the data you read is consistant.
Assuming you do not have the Lock Tables, what happened is that another connection is able to update the 2 tables in between your select, example, after you complete the 1st select, another connection is able to update the table t1 and t2 before you execute the 2nd select, in this case the data you have obtained from the 2 selects may no longer be consistant since the data had been changed between your 1st and 2nd select.
Therefore, you need the explicit Read Lock to get a consistant view of the data for the 2 selects.