Table Locking?

Databases
1

Hi,

Do you know if Xojo allows for table locking via SQL statements when working with a MySQL database? If so, how would one do this?

Here’s what I was potentially thinking. Am I on the right track here?

db.executeSQL("LOCK TABLES Customers WRITE;") db.executeSQL(" ... some sql statement ...;") db.executeSQL("UNLOCK TABLES;")

Byron

2

SQLite doesn’t do table locking, dunno about the others.

3

Ah, my apologies! I forgot to include the database server… (MySQL)

4

Most databases handle this for you, that is the advantage of using a database like MSSLQ. You should not have to lock tables when inserting rows.

As far as SQLite, that is a file and the operating system is responsible for locking the file when writes occur. According to the SQLite documentation, that is sketchy on some OS’s but I have never experienced that issue on PC with anything Win 7 or greater.

5

It works perfectly well unless you try to use a network file system, where for performance reasons such systems tend to report that a write is complete when, in fact, it isn’t.

6

So, if I issue the sql command to lock the table, it will stay locked … until I issue another command to unlock it again?

db.executeSQL("LOCK TABLES Customers WRITE;")

The table stays locked … until I issue another command to unlock it?

db.executeSQL("UNLOCK TABLES;")

Just trying to figure this out… :slight_smile:

7

Usually yes BUT I would not do this
Certainly not in a multi user environment with a server like Mysql, MSSQL, PostgreSQL etc
Start a transaction and when everything has been “written” commit the transaction
Thats much more multi-usr friendly

If you lock a table you CAN block other users and this can result in deadlocks in the DB that you cannot fix without killing one client
its just not a good thing to do in this day & age

8

Thank you! I appreciate your (and everyone else’s) insights… :slight_smile:

9

I’ve done table locking many many many years ago when that was about all you had
and then someone would lock it for edit and that user would go to lunch :slight_smile:
and about 35 minutes later we’d get screams because no one could work because all the users were locked up waiting for that one user to come back from lunch & finish their edits
and I’ve seen issues with write locks NOT being dropped quickly when an app abrubtly disconnects and you have the same kind of issue

transactions are just much safer

10

That’s good to know! I’m learning something new about Xojo and … SQL databases all the time! :smiley:

Thank you.