"FOR UPDATE" v/s "LOCK IN SHARE MODE" : Allow concurrent threads to read updated "state" value of locked row

Question

I have the following scenario:

• User X logs in to the application from location lc1: call it Ulc1
• User X (has been hacked, or some friend of his knows his login credential, or he just logs in from a different browser on his machine,etc.. u got the point) logs in at the same time from location lc2: call it Ulc2

I am using a main servlet which :
- gets a connection from database pooling
- sets autocommit to false
- executes a command that goes through app layers: if all successful, set autocommit to true in a "finally" statement, and closes connection. Else if an exception happens, rollback().

In my database (mysql/innoDb) i have a "history" table, with row columns:
id(primary key) |username | date | topic | locked

The column "locked" has by default value "false" and it serves as a flag that marks if a specific row is locked or not.
Each row is specific to a user (as u can see from the username column)

So back to the scenario:
-->Ulc1 sends the command to update his history from the db for date "D" and topic "T".

-->Ulc2 sends the same command to update history from the db for the same date "D" and same topic "T" at the exact same time.

I want to implement an mysql/innoDB locking system that will enable whichever thread arriving to do the following check:

Is column "locked" for this row true or not?

• if true, return a message to the user that " he is already updating the same data from another location"
• if not true (ie not locked) : flag it as locked and update then reset locked to false once finished.

Which of these two mysql locking techniques, will actually allow the 2nd arriving thread from reading the "updated" value of the locked column to decide wt action to take?

Should i use "FOR UPDATE" or "LOCK IN SHARE MODE"?

This scenario explains what i want to accomplish:
- Ulc1 thread arrives first: column "locked" is false, set it to true and continue updating process
- Ulc2 thread arrives while Ulc1's transaction is still in process, and even though the row is locked through innoDb functionalities, it doesn't have to wait but in fact reads the "new" value of column locked which is "true", and so doesn't in fact have to wait till Ulc1 transaction commits to read the value of the "locked" column(anyway by that time the value of this column will already have been reset to false).

I am not very experienced with the 2 types of locking mechanisms, what i understand so far is that LOCK IN SHARE MODE allow other transaction to read the locked row while FOR UPDATE doesn't even allow reading. But does this read gets on the updated value? or the 2nd arriving thread has to wait the first thread to commit to then read the value?

Any recommendations about which locking mechanism to use for this scenario is appreciated.
Also if there's a better way to "check" if the row has been locked (other than using a true/false column flag) please let me know about it.
thank you

SOLUTION
(Jdbc pseudocode example based on @Darhazer's answer)

Table : [ id(primary key) |username | date | topic | locked ]

connection.setautocommit(false);
//transaction-1
PreparedStatement ps1 = "Select locked from tableName for update where id="key" and   locked=false);
ps1.executeQuery();

//transaction 2
PreparedStatement ps2 = "Update tableName set locked=true where id="key";
ps2.executeUpdate();
connection.setautocommit(true);// here we allow other transactions threads to see the new value

connection.setautocommit(false);
//transaction 3
PreparedStatement ps3 = "Update tableName set aField="Sthg" where id="key" And date="D" and topic="T";
ps3.executeUpdate();

// reset locked to false
 PreparedStatement ps4 = "Update tableName set locked=false where id="key";
ps4.executeUpdate();

//commit
connection.setautocommit(true);

Answer 1

LOCK IN SHARE MODE will allow the 2nd thread to read the value, but the actual value will be the one before the query (read commited) or before the transaction (repeatable read) has been started (as MySQL uses multi-versioning; and what have to be seen by the second transaction is defined by the isolation level). So if the 1st transaction is not commited at the time of the read, the old value will be read.

In your scenario best to have 1 transaction that locks the record with select for update, another than works on the record and on commit/rollback third one unlocks the record.

The second thread transaction with select for update will wait for the first to complete, then will read actual value and will decide not to continue with the other transactions, but to inform user that the record is locked.

To avoid deadlock, make sure you are doing the select for update using an unique index.

Example code:

connection.setautocommit(false);
//transaction-1
PreparedStatement ps1 = "Select locked from tableName for update where id="key" and   locked=false);
ps1.executeQuery();

//transaction 2
PreparedStatement ps2 = "Update tableName set locked=true where id="key";
ps2.executeUpdate();
connection.setautocommit(true); // here we allow other transactions / threads to see the new value

connection.setautocommit(false);
//transaction 3
PreparedStatement ps3 = "Update tableName set aField="Sthg" where id="key" And date="D" and topic="T";
ps3.executeUpdate();

// probably more queries

// reset locked to false
 PreparedStatement ps4 = "Update tableName set locked=false where id="key";
ps4.executeUpdate();

//commit
connection.setautocommit(true);