MySQL Transcactions and Multiuser usage

[Rob van der Sloot]

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

[sphere]

Hallo Rob,

I use middleware like PHP files if multiple users need access to the DB when they all have a different IP addres not related to the servers address.
But when it is internally then you can use LC's internal DB functions just fine.
Yur question made me curious as i've not used(thought about it) before.

But these are a few nice websites with more info about your question:
http://www.methodsandtools.com/archive/ ... .php?id=83
https://vladmihalcea.com/a-beginners-gu ... phenomena/
https://stackoverflow.com/questions/771 ... rite-locks

I think you need to check out:
revCommitDatabase in the dictionary

EDIT*** oh ja, also read this :http://blog.danyll.com/myisam-vs-innodb/ very nice article.
MyISAM uses Table lock so your whole table would be locked, while innoDB uses row-lock leaving the rest of the table accessible. innoDB is the type of DB-table which is best to use.

Groet,
Sphere