7

I have a few questions about MySQL table lock. I appreciate if anyone answers :)

  1. Does MySQL lock tables automatically in the following cases:

    • SELECT id FROM members;
    • UPDATE members SET name = 'john' WHERE id = 7;

  2. What is the difference between these two:

    • LOCK TABLE items READ ; SELECT * FROM 'items;
    • SELECT * FROM 'items';

  3. For some reason I was under the impression that MySQL automatically locks the tables on necessary occasions! How can I check when and how the locking happens?

Thank you.

ASGM
  • 11,051
  • 1
  • 32
  • 53
ben jaz
  • 99
  • 1
  • 2
  • 7

2 Answers2

5

1.a) no lock required
1.b) locks the table (myisam engine) or you have row level locking if your using innodb engine

2.a) locks the table for read operations (until this lock is released no writing operations occur)
2.b) no lock required

As Lithu T.V suggested please read the DOCS to get the all the use cases.

Stephan
  • 8,000
  • 3
  • 36
  • 42
0

  1. As far as I know, in mysql the table is not automatically locked. But it depends on what you use. For example with a SqlTransaction, the affected tables will be locked until you commit or rollback.

  2. a. you lock the table first, this will make sure that while you read the table, noone else could lock it to write and you would get a deadlock ;) b. In some special circumstances, a lock on the table could prevent you from reading the table, therefore the sql fails.

  3. With "SHOW OPEN TABLES" , you will see all available tables and their lock status It's a bit complicated because of the system tables you want to exclude, sth like:

$sql = "SHOW OPEN TABLES FROM '".$db_name."'
WHERE In_use > 0
AND
'Table' IN (";

$tables = count($array_tables_names);
for($i = 0; $i < $tables; $i++):
$sql .= "'".$array_tables_names[$i]."'";
if($i < ($tables-1)) $sql .= ",";
endfor;

$sql .= ")";

More information on locks can be found here: http://dev.mysql.com/doc/refman/5.1/en//lock-tables.html

Xavjer
  • 8,838
  • 2
  • 22
  • 42