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);
