Exclusive Lock (Row Level) - MySql need an example

Question

I want to lock a specific row in MySQL (InnoDB), so that specific row can't be read by another connection.

start transaction;
Select apples from fruit where apples = 'golden';
commit;

Now I want to apply an exclusive lock

exclusive lock A kind of lock that prevents any other transaction from locking the same row.

https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html#innodb-shared-exclusive-locks

I'm assuming, the lock get's applied, then the second connection trying to access the same row, has to wait till the first connection releases the lock with a "commit;"

My question is, how do I adjust my SQL Statement to apply that lock? Or does this automatically happen with the start transaction?

I've been trying to find a good example and haven't.

Answer 1

START TRANSACTION;
Select apples 
  from fruit 
 where apples = 'golden'
   FOR UPDATE;
...
COMMIT;

The SELECT ... FOR UPDATE statement first locks the row, then returns it to you.

Anyone else trying to do the same thing to the same row at the same time will wait for your COMMIT or ROLLBACK and then they will receive the row... with any changes you made.

If you have no intention of changing the row, you just want to make sure nobody else does can, then you can SELECT ... LOCK IN SHARE MODE.

Note that, either way, it's technically not the "row," it's actually the index record that is being locked, but in InnoDB, "everything is an index" (even a table with no indexes at all does in fact still have one index, siently created by the system) so the net result is the same.