Hi all,
I'd like to receive help on how to properly combine record locking and insert in a case like the one below.
Among others, I've got two tables : table1 and table2.
My intent is to add a new record in table2 but at the same time prevent concurrent inserts by users because table2 has a foreign key to table1 and table1 has a field that needs to get updated after a successful insert.
To make things clearer, here it's what it should be:
BEFORE
table1
id = 27, scheme = XXXXXX
table2
empty
AFTER
table2
id = 1, fk_table1_id = 27, data = whatever (related to the third element in table1.scheme)
table1
id = 27, scheme = XXTXXX
So, as long as users need to insert data not related to table1.id = 27, they could do it.
But if they need to insert data related to that id, they have to wait the currently executing insert operation.
As for code, I did this way:
Is it correct? Or does it incur in a dead-lock or other possible problem?
How would you code yourself in a case like the above?
TIA
I'd like to receive help on how to properly combine record locking and insert in a case like the one below.
Among others, I've got two tables : table1 and table2.
My intent is to add a new record in table2 but at the same time prevent concurrent inserts by users because table2 has a foreign key to table1 and table1 has a field that needs to get updated after a successful insert.
To make things clearer, here it's what it should be:
BEFORE
table1
id = 27, scheme = XXXXXX
table2
empty
AFTER
table2
id = 1, fk_table1_id = 27, data = whatever (related to the third element in table1.scheme)
table1
id = 27, scheme = XXTXXX
So, as long as users need to insert data not related to table1.id = 27, they could do it.
But if they need to insert data related to that id, they have to wait the currently executing insert operation.
As for code, I did this way:
B4X:
Dim sql1 As SQL = pool.GetConnection
sql1.BeginTransaction
Try
Dim Cursor As ResultSet
'read and record-lock table1
Cursor = sql1.ExecQuery2($"SELECT * FROM table1 WHERE id = ? FOR UPDATE;"$, _
Array As Object(slbkid))
'insert in table2
sql1.ExecNonQuery2($"INSERT INTO table2 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"$, _
Array As Object(Null, slbkid, blah blah..))
'get ID of the inserted record
Dim lastID As Long =sql1.ExecQuerySingleResult("SELECT LAST_INSERT_ID();")
'update data in table1 based on data just inserted in table2
Dim newslbk As String = "XXTXXX"
sql1.ExecNonQuery2($"$UPDATE table1 SET scheme= ? WHERE id = ?"$, _
Array As Object(newslbk, slbkid ))
'close transaction and unlock table1
sql1.TransactionSuccessful
Catch
sql1.Rollback
End Try
Cursor.Close
sql1.CloseIs it correct? Or does it incur in a dead-lock or other possible problem?
How would you code yourself in a case like the above?
TIA
Last edited: