MySQL Transcactions and Multiuser usage

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
Rob van der Sloot
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 75
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

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?

Rob van der Sloot

Posts: 1109
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: ... .php?id=83 ... phenomena/ ... rite-locks

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

EDIT*** oh ja, also read this : 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.


Post Reply

Return to “Databases”