MySQL Transcactions and Multiuser usage

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
Rob van der Sloot
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 68
Joined: Sat Apr 17, 2010 9:21 am

MySQL Transcactions and Multiuser usage

Post by Rob van der Sloot » 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

sphere
Posts: 924
Joined: Sat Sep 27, 2014 10:32 am
Location: Earth, Except when i Jump

Re: MySQL Transcactions and Multiuser usage

Post by sphere » Mon Dec 24, 2018 10:09 am

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
2018 Worldwide Mobile Market share: Android 84.4% !!!, iOs 15.1%, others 0.1%

Post Reply

Return to “Databases”