LC and transactions in MySQL

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
xanant
Posts: 25
Joined: Sun Nov 09, 2014 5:38 pm

LC and transactions in MySQL

Post by xanant » Thu Nov 21, 2019 5:47 pm

Hi
I would like to understand something more about the transactions and use them to avoid anomalies in case of multiple concurrent operations.
This LC script example, used with MySQL database, presents two problems:

global gDataBaseID
.........
put "START TRANSACTION; " into tSQL
put "UPDATE Corsi " after tSQL
put "SET PostiLiberi = 34 , PostiRiserva = 0 “ & \
"WHERE IDCorso = 257 “ & \
"AND IDAnnoIscrizione = 5; " after tSQL
put "COMMIT" after tSQL /* senza il “;” */
eseguiComandoSQL tSQL
.........

on eseguiComandoSQL pSQL
revExecuteSQL gDataBaseID, pSQL
if the result is not a number or the result < 0 then error the result
end eseguiComandoSQL

on error pError
answer error "Si è verificato un errore: " & pError as sheet
exit to top
end error


Problem (1): written in this way, using the custom command 'eseguiComandoSQL tSQL', the routine provides database ERROR and stops.
Problem (2): If instead I simply use 'revExecuteSQL gDataBaseID, tSQL', instead of the custom one, the routine DOES NOT report an error but the database is NOT updated as it should happen!
Without the use of 'START TRANSACTION' command by writing only the simple LC code:

put "UPDATE Corsi " & \
"SET PostiLiberi = 34, " & \
"PostiRiserva = 0 “ & \
"WHERE IDCorso = 257 “ & \
"AND IDAnnoIscrizione = 5 “ into tSQL


it is indifferent to use one (custom) or the other command (LC std) to execute the query and everything works regularly, including database updating, but in this way I am not repaired by any problems of concomitance of operations: this is the reason why I want to use the transactions mode when necessary.
How is it possible that the database, when executing a transaction, is not updated? Should "COMMIT" not immediately register the query result in the database?
Excuse my ignorance, but don't understand.
Sandro

AxWald
Posts: 449
Joined: Thu Mar 06, 2014 2:57 pm

Re: LC and transactions in MySQL

Post by AxWald » Fri Dec 06, 2019 11:19 am

Hi,
just an idea: Youre table is not MyISAM, right? Because there's no transactions there ...

And in InnoDB, the behaviour of the db depends of the setting of "autocommit" (default: on).

I rarely use transactions, but when I do, I don't send them as a block ("START TRANSACTION; UPDATE t_table SET [...]; COMMIT;") - I send them line by line & check the results each time. Much more easy to find possible culprits ;-)

Hope I could help a bit. Have fun!
Livecode programming until the cat hits the fan ...

xanant
Posts: 25
Joined: Sun Nov 09, 2014 5:38 pm

Re: LC and transactions in MySQL

Post by xanant » Tue Dec 10, 2019 4:26 pm

Hi, thanks for the reply. Yes, it's InnoDB. Probably I have reset the autocommit to "ON" somewhere in the scripts, or maybe it automatically returns to "ON". I will investigate further.

Post Reply

Return to “Databases”