A table with a primary key Id and an integer field, where the value is currently 100.
Two queries run at the same time on different connections, without an explicit "LOCK ROW" statement:
UPDATE TABLE SET thefield = thefield + 50 WHERE id = 12345
and
UPDATE TABLE SET thefield = thefield - 30 WHERE id = 12345
Does it matter whether I explicitly lock the row for the update?
I don't think it does. I think the result will always be 120 regardless of which query gets there first or if they both hit at the same second. I am pretty sure this gets evaluated as an atomic operation.
Or does it depend on the vendor and table engine?
Is there a potential for a collision where thefield is out of sync before the SET part of the query increments the value?
I thought that was the way it worked, and these will always have a consistent result. Am I a dummy who is that much out of practice?