I'm using MySql(innodb storage engine). I want to implement row level implicit locking on update statement. so, that no other transaction can read or update that row concurrently.
Example: Transaction1 is executing "UPDATE Customers SET City='Hamburg' WHERE CustomerID=1;"
Then, at same time Transaction2 should not able to read or update same row but Transaction2 should be able to access other rows.
Any help would be appreciated. Thank you for your time.
If there are no other statements supporting that UPDATE, it is atomic.
If, for example, you needed to look at the row before deciding to change it, then it is a little more complex:
BEGIN;
SELECT ... FOR UPDATE;
decide what you need to do
UPDATE ...;
COMMIT;
No other connection can change with the row(s) SELECTed before the COMMIT.
Other connections can see the rows involved, but they may be seeing the values before the BEGIN started. Reads usually don't matter. What usually matters is that everything between BEGIN and COMMIT is "consistent", regardless of what is happening in other connections.
Your connection might be delayed, waiting for another connection to let go of something (such as the SELECT...FOR UPDATE). Some other connection might be delayed. Or there could be a "deadlock" -- when InnoDB decides that waiting will not work.