UPDATE query places an update lock on the pages or records it reads.
When a decision is made whether to update the record, the lock is either lifted or promoted to the exclusive lock.
This means that in this scenario:
s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1
s2: write counter for image_id=15 to (temp2+1), which is also 1
s2 will wait until s1 decides whether to write the counter or not, and this scenario is in fact impossible.
It will be this:
s1: place an update lock on image_id = 15
s2: try to place an update lock on image_id = 15: QUEUED
s1: read counter for image_id=15, get 0, store in temp1
s1: promote the update lock to the exclusive lock
s1: write counter for image_id=15 to (temp1+1), which is 1
s1: commit: LOCK RELEASED
s2: place an update lock on image_id = 15
s2: read counter for image_id=15, get 1, store in temp2
s2: write counter for image_id=15 to (temp2+1), which is 2
Note that in InnoDB, DML queries do not lift the update locks from the records they read.
This means that in case of a full table scan, the records that were read but decided not to update, will still remain locked until the end of the transaction and cannot be updated from another transaction.