MySQL Transcactions and Multiuser usage
Posted: Thu Dec 06, 2018 11:49 am
I my application I am making many connections with a MySQL server.
In single user it wokst fine, smooth and quick.
A LC-connection script looks like:
put "UPDATE " & tTable & " SET " & tColumnName & " =" & tContent & " WHERE ID=" & tID into tSQL
put revOpenDatabase(MySQL, tDBAddress, tDBName, tUser, tPassword) into tResult
revExecuteSQL gConID, tSQL
revCloseDatabase gConID
put the result into tResult
handleRevDBerror
Now more then 1 user is going to use the application. In total 20 users could have acces at the same moment.
In the mySQL manual I use, there is a chapter about Transactions and Multiuser usage. In this chapter there are subjects discussed about "Dirty Read or uncommitted read", "Non repeatable read or non reproducable read", "Phantom Read" and "Lost Update".
The solution to these problems is "Locking" through "Lock Table and "unlock table" instructions.
I wonder if I need to this locking in the LC-connection script to avoid this possibillity of errors in the database. If so what is the easiest way to do that?
Thanks
Rob van der Sloot
In single user it wokst fine, smooth and quick.
A LC-connection script looks like:
put "UPDATE " & tTable & " SET " & tColumnName & " =" & tContent & " WHERE ID=" & tID into tSQL
put revOpenDatabase(MySQL, tDBAddress, tDBName, tUser, tPassword) into tResult
revExecuteSQL gConID, tSQL
revCloseDatabase gConID
put the result into tResult
handleRevDBerror
Now more then 1 user is going to use the application. In total 20 users could have acces at the same moment.
In the mySQL manual I use, there is a chapter about Transactions and Multiuser usage. In this chapter there are subjects discussed about "Dirty Read or uncommitted read", "Non repeatable read or non reproducable read", "Phantom Read" and "Lost Update".
The solution to these problems is "Locking" through "Lock Table and "unlock table" instructions.
I wonder if I need to this locking in the LC-connection script to avoid this possibillity of errors in the database. If so what is the easiest way to do that?
Thanks
Rob van der Sloot