Other MySQL - record locking and insert

[udg]

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:

   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.Close

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

 

[Erel]

I think that your code is correct.

Assuming that it is only your program that modifies the database you can use a ReadWriteLock to allow multiple readers to read from the database and a single writer to write (not at the same time).

 

[udg]

Assuming that it is only your program that modifies the database

This sounds interesting. Should I concern about two different apps that make use of the same DB? They will differ for layouts and data displayed/inserted but I designed (in my mind) the DB as a central repository for a few apps that share a common structure while differentiating in data meaning.
Obviously two different apps will work on different record sets, so we can consider each one like "sole owner" of a given data set.

About data meaning, I see it like app1 could read/write data as "room data", app2 as "seat data" and so on.
Those XXTXX.. worked as a semaphore to signal if a resource (room, vehicle, seat, whatever) is already in use or booked etc. So I could design specialized apps that share a single DB (knowing beforehand that each app will make use of very few data).

My idea is that many users can read the status of the XXTXX at any time, but when one of those locks the record for update, the others have to wait and eventually given back an updated status of that same XXTXX scheme.
The code in post#1 is what I would insert in a function made to reserve a slot in the XXTXXXX scheme, while a different function will simply try to read it.

Generally an app will use the read-only function to acquire current status, then eventually prepare new data to insert and use function like post#1 to try to insert its data. This is the moment where two users can conflict if they issue the insert command at the same time. Locking should block the second arrived user, complete insert with data for first user, then insert data for second user if it's not conflicting with that from first user or give feedback about the changed semaphore status (so that the second user could make a different choice).
It's like when to users want to reserve the same seat for a train, cinema or other similar situation.

Sorry for the long message and thank for your reply. Please comment further if anything in this post makes you believe a new comment or hint is needed. Thanks again.

 

[Erel]

Should I concern about two different apps that makes use of the same DB?

Only if you want to use the ReadWriteLock to control the writing access in your own app instead of the database itself.

Your solution will work with multiple apps.