Given a table like this:
id | counter
1 | 3
(the field id is primary) and the following query:
$sql = 'INSERT INTO `test`(`id`, `counter`) VALUES (1,1)
ON DUPLICATE KEY UPDATE counter= counter+1'
$db->query($sql);
Would it be possible that that two different user execute this script from the server and the counter only gets updated to 4 instead of 5, because they were executed at the same time and increased both 3 to 4 at once? Or is this not possible?
Do not use LOCK TABLE with InnoDB. It is essentially never necessary.
IODKU locates the one row, locks it, then performs the operation, then unlocks. No problem with multiple 'simultaneous' connections, except that one will wait (a tiny amount of time) for the other to release the lock.
What you have is a good table for "Likes" (social media) or "Hits" (web pages). Note that id is the PRIMARY KEY, but not auto_increment. It is the same id as for the main table (which probably has auto_increment).
If you have more than hundreds of likes/hits per second, there will be performance problems. (But that is another discussion.) Obviously, you are not there yet.