SQL atomic increment and locking strategies - is this safe?

Question

I have a question about SQL and locking strategies. As an example, suppose I have a view counter for the images on my website. If I have a sproc or similar to perform the following statements:

START TRANSACTION; UPDATE images SET counter=counter+1 WHERE image_id=some_parameter; COMMIT; 

Assume that the counter for a specific image_id has value '0' at time t0. If two sessions updating the same image counter, s1 and s2, start concurrently at t0, is there any chance that these two sessions both read the value '0', increase it to '1' and both try to update the counter to '1', so the counter will get value '1' instead of '2'?

s1: begin s1: begin 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 s1: commit, ok s2: commit, ok 

End result: incorrect value '1' for image_id=15, should have been 2.

My questions are:

1. Is this scenario possible?
2. If so, does the transaction isolation level matter?
3. Is there a conflict resolver which would detect such a conflict as an error?
4. Can one use any special syntax in order to avoid a problem (something like Compare And Swap (CAS) or explicit locking techniques)?

I'm interested in a general answer, but if there are none I'm interested in MySql and InnoDB-specific answers, since I'm trying to use this technique to implement sequences on InnoDB.

EDIT: The following scenario could also be possible, resulting in the same behavior. I'm assuming that we are in isolation level READ_COMMITED or higher, so that s2 gets the value from the start of the transaction although s1 already wrote '1' to the counter.

s1: begin s1: begin s1: read counter for image_id=15, get 0, store in temp1 s1: write counter for image_id=15 to (temp1+1), which is 1  s2: read counter for image_id=15, get 0 (since another tx), store in temp2 s2: write counter for image_id=15 to (temp2+1), which is also 1 s1: commit, ok s2: commit, ok 

Answer 1

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.