is a LOCK TABLE needed for `UPDATE tbl SET col = col + 1`?

lets say that col is 0, and 100 clients is trying to increase it by 1 at the exact same time, is the LOCK TABLE needed here?

LOCK TABLE tbl;
UPDATE tbl SET col = col + 1;
UNLOCK TABLE;

or can i just do

UPDATE tbl SET col = col + 1;

and be sure that col becomes 100? (eg that mariadb makes sure none of them reads the same value twice~)

i’m basically trying to do the equivalent of this php code

$fp = fopen("counter.txt", "r+b");
flock($fp, LOCK_EX);
$number = (int) stream_get_contents($fp);
$number += 1;
rewind($fp);
fwrite($fp, (string) $number);
flock($fp, LOCK_UN);
fclose($fp);

but with a database instead of a text file

and i doubt there’s a difference between MariaDB and MySQL on this one, but if there is, i’d like to learn about it, so tagging both (as an anecdote, i’m using MySQL at work, and MariaDB at home~)

Answer

First, don’t use MyISAM, use InnoDB. Do not use LOCK TABLES with InnoDB.

In no situation do you need any kind of a lock for a single query. A query has an implicit lock. (MyISAM locks the table; InnoDB locks the row(s).)

Locks / transactions are needed when you have multiple statements that need to be kept together “atomically”. Notice how your example does both a read and a write, plus doing something with the value read.

The equivalent in InnoDB (in pseudo-code):

START TRANSACTION;
$num = SELECT num FROM tbl WHERE id=1  FOR UPDATE;
$num = $num + 1;
UPDATE tbl SET num = $num;
COMMIT;

True, UPDATE table SET num = num + 1 does the same thing atomically, but it does not let you do anything else with the value of num.

MariaDB has a RETURNS clause in some cases. There is LAST_INSERT_ID(). But these are limited exceptions to the rule that you should really think in terms of multi-statement transactions if you expect to have multiple threads messing with the same data.