I’m creating a app that runs in the XOJO Cloud, 11.3.2-MariaDB (MariaDB Server). The app spins up at least two instances, so the logic executing in app is executing in duplicate - which isn’t a bad thing, but something I’m not exactly clear on how to address in respect to colliding on database transactions.
Below is a simplified version of an update, where I’m changing the status of a record from ‘0’ (unprocessed) to ‘1’, to stage it for the next sweep of logic.
Question: If the 1st app instance is in the midst of this update, and the 2nd instance tries to perform the same routine, what happens? Is there a moment when the records will be locked, and the 2nd instance will be thrown a DatabaseException?
And, I’m not sure what current practices are - but is it best to still use .CommitTransaction and .RollbackTransaction methods? I’m in the dark as to what happens when ExecuteSQL() is called, and what happens when it fails.
Thanks for any advice for having multiple apps play well together with a single dbase.
Var xFNdbase As MySQLCommunityServer = new_FNdbase_connection()
Var stmt As MySQLPreparedStatement
Try
If Not xFNdbase.Connect() Then Return
stmt = MySQLPreparedStatement(xFNdbase.Prepare("UPDATE dbase.tablename SET RECORD_STATUS = ? WHERE (ID = ?);"))
stmt.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_SHORT)
stmt.Bind(0, new_status)
stmt.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_SHORT)
stmt.Bind(1, record_ID)
stmt.ExecuteSQL()
'xFNdbase.CommitTransaction
xFNdbase.Close
Catch error As DatabaseException
'xFNdbase.RollbackTransaction
xFNdbase.Close
// massive error handling goes here
End Try
Where in your web app are you creating (opening) the database connection(s)?
If you’re doing it in the Session object, you should be fine, the database is smart enough to handle your requests. If you’re not doing it in the Session object, well, you should
Hi Gabriel - this is mostly a “headless” app, which essentially a service, and the dbase gets opened (and closed) in a module in the app. The GUI (sessions) are only for administration use - and any dbase activity the sys admins have is done with a session dbase connection, which like you said is pretty much isolated and not of concern.
At startup these web ‘services’ create threads that periodically open a dbase connection, look for records with certain status, perform some processing, and then close. Depending on the processing task, which could be reaching out to another service on the internet, the activity of app instance #1 could be in process when an identical process kicks off in app instance #2. This is where my inexperience with MariaDB and the dbase connectors in Xojo fall short. Is there record locking? Can I force locking to occur in order to properly test for conflict resolution and appropriate error handling? Or, is there no conflict and am I simply worried about nothing…?
If each of your services, creates (opens) its own connection to the database and closes it after it no longer needs it, the database (MariaDB) has its own built-in concurrency control mechanisms that should make sure no problems arrise and prevent conflicts between the services.
And, when doing a “SelectSQL()” I assume there’s no reason to use BeginTransaction() or CommitTransaction(), or in the case of an error, RollbackTransaction() either?
Some additional items that made all the difference (pasted here for future reference) when using SELECT to target rows for updating and locking them so another instance doesn’t replicate/duplicate the process. I my case the 2nd instance was creating dupe entries as a result of stepping over other instances that had selected the same rows.
// turn off AUTOCOMMIT for the dbase: SET AUTOCOMMIT=0;
// follow this sequence
1.- .Connect() to the dbase.
2.- Create your prepared statement and BE SURE to the SELECT statement end with ‘FOR UPDATE;’
e.g., stmt = MySQLPreparedStatement(dbase.Prepare("SELECT * FROM dbase.table WHERE RECORD_STATUS = ? FOR UPDATE;"))
3.- followed by dbase.BeginTransaction()
4.- execute the statement, rows = stmt.SelectSQL()
5.- …work on the data you selected, alter it, etc…
6.- finally use dbase.CommitTransaction(), followed by rows.Close() to release the lock.
Greg, thanks for the feedback - since this is crucial to my process I want to make sure I didn’t mistake anything. In step #4 of my bare-bones outline, I have the transaction taking place in the stmt.SelectSQL() (after .BeginTransaction, and before .CommitTransaction), is that correct?
I think i saw somewhere in one of XOJO’s advertising for the new versions that now with the latest versions they do include automatically the Begin and commit transaction behind the scenes so you don’t need to doit anymore, but not ture if that is true and only while using their classes .
When I use their classes, it’s true, I don’t use the begin and commit and it works so far.
But the OP is using ExecuteSQL, that’s why I suggested to also include begin and commit transaction.
Autocommit controls how the database deals with multiple conatenated statements. If it is on, each command will be immediatedly committed before the next one is executed. As William seems to issue one command only (“update sometable”) the setting makes no difference.
Btw, Select for Update is useful in this scenario: The client pulls data and then changes it. To make sure the data doesn’t change after it has been pulled but before you change it by some other client you call Select for update instead of a simple select.
But this is relevant only when multiple commands are issued (like “select 1; update sometable…”). This is not the case here. Auto-transactions are always on for single statements. A dbms would not be considered ACID compliant it this was not the case.
