Tutorial :Question on locking and transactions on MyISAM table



Question:

I have a counter field in a myisam table. To update the counter value in a multitasking environment (web server, concurrent queries from PHP) I need to lock the record for update. So I do it like this:

START TRANSACTION;   SELECT Counter FROM mytable ... FOR UPDATE;   UPDATE Counter value or INSERT INTO mytable;   // let's make sleep for 20 seconds here to make transaction longer   COMMIT;   

As I understand, in MyISAM the whole table should be locked until transaction ends. And when I initiate concurrent query from PHP, opening script in a browser, it really waits until lock is gone. But If I select all records from a table with mysql.exe - it selects all records even when lock should still be hold.

So it seems I don't understand something. Please, explain such a behavior.


Solution:1

MyISAM tables don't support transactions - START TRANSACTION and COMMIT do nothing.

You can use LOCK TABLES:

LOCK TABLES mytable READ;  ...  UNLOCK TABLES;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »