LOCK TABLES and InnoDB Row-Level locking : pros and cons
The main difference between InnoDB & MyISAM engines is that InnoDB supports transactions and row-level locking, while MyISAM can handle only table-level locks.
What does this mean ? Simply, as an example, when you use MyISAM engine and you have to read some data from the table, manipulate it and then update the record(s), you must LOCK the entire table, so that concurrent reads to all data in the table are prohibited. When a table is locked any select or DML command will be waiting for the lock to be release with the “UNLOCK TABLES” command, issued by the locker. This kind of implementation is very simple, but, if a table becomes locked very often, this can lead to performance issues.
While using InnoDB engine, you can avoid locking the entire table and lock only the row(s) that need to be updated. Concurrent reads to not updating records can still be done. This can be achieved starting a transaction (disabling autocommit) and then add the “FOR UPDATE” clause to SELECT statements: in this scenario every SELECT … FOR UPDATE will be locked only if where select has the same WHERE condition, otherwise there will be no lock. There will be no lock also when your SELECT statement doesn’t include the FOR UPDATE clause. This is not much harder to implement, but you can still read from the table and even write if the rows you want to update are not locked. The row-level locks will be removed when the transaction ends, with a COMMIT or ROLLBACK command.
EXAMPLES
Let’s assume we have to tables with the following DDL statements:
CREATE TABLE test_1 (id INTEGER NOT NULL PRIMARY KEY, my_value VARCHAR(20)) Engine=MyISAM;
CREATE TABLE test_2 (id INTEGER NOT NULL PRIMARY KEY, my_value VARCHAR(20)) Engine=InnoDB;… and that some data is already in it. Let’s start with the old-fashioned MyISAM lock tables:
LOCK TABLES test_1 WRITE;
SELECT @a:=my_value FROM test_1 WHERE id = 2;
UPDATE test_1 SET my_value = IF(@a='hello','Hello, World!','Greetings!') WHERE id = 2;
UNLOCK TABLES;While using an InnoDB table the same thing can be achieved in this way:
START TRANSACTION;
SELECT @a:=my_value FROM test_2 WHERE id = 2 FOR UPDATE;
UPDATE test2_ SET my_value = IF(@a='hello','Hello, World!','Greetings!') WHERE id = 2;
COMMIT;SUMMARY
Both methods of handling concurrency in MySQL have their pros and cons. I think my favorite is the InnoDB row-level lock, due to its better performances.
PROS for LOCK TABLES method:
– easy to implement
– prevents “dirty reads”
– works with both MyISAM and InnoDB engines
CONS for LOCK TABLES method:
– can lead to performance issues
– can’t read other non-update-needed rows
PROS for InnoDB row-level lock:
– easy to implement
– let read non-update-needed rows
– possibile performance issues only if updating the same row(s)
CONS for InnoDB row-level lock:
– requires InnoDB storage engine
– permits “dirty reads”
Hope you enjoyed this article!
