CubeSQL locking database

Databases
1

I am testing an unlimited licence version of the latest CubeSQL via a desktop app running on both macOS and Windows. Normally everything is working fine, but periodically I have this error:

Database HM_Data.SQLite is currently locked for write operations. Try to increase timeout value (12.0001). Lock owner: HM_Data.’ (7055)

The only change I have made is on connection to the database I execute:

SET AUTOCOMMIT TO ON;

I have tried to change the Lock DB (ms) in Settings from 800 to a large number, but the new number never ‘sticks’.

The number of users is rarely more than three. I think all the SQL code is clean — I connect, run the SQL then exit. The application is not freezing, just giving the error. I don’t think I can turn on MVCC. I connect to the database once on login then reconnect via db.Connect if it has timed out. Over time the database will become used by many more people at once (but not heavy usage).

Is there any obvious reason for this locking? How can I increase the timeout to a larger number?

2

Have you looked for a BEGIN TRANSACTION that is not eventually committed?

3

I don’t use BEGIN TRANSACTION or COMMIT since I set AUTOCOMMIT on. Is this a mistake?
It seems to work, other than the locking issue.

Even if I was missing a COMMIT statement, shouldn’t it time out and cancel the locking after 800 milliseconds?

4

I had this problem when I first started using cubeSQLServer, and I think an email conversation with Marco solved it. I have

DB.AutoCommit = True

in the method that connects the database.

5 
DB.AutoCommit = True

I had this line BEFORE I did a Connect to the database and it crashed the app! I’ll try adding it after I connect. Thank you.

6

I just looked over my emails with Marco and see that

DB.AutoCommit = True

solved this problem. cubeSQLServer is in AutoTransaction mode if you don’t do that. Here are the last few lines in my ConnectDB method in Session:

[code]If Not Self.DB.Connect Then
MsgBox “Could not connect to Main Database file.”
Self.Quit
Return
End If

Self.DB.AutoCommit = True

If Self.DB.error Then
MsgBox "An error occurred: " + Self.DB.ErrorMessage
End If[/code]

7

By the way, if you have your project hosted over at serverwarp.com, you get an unlimited cubeSQLServer license for free. That’s what I do.

8

Nice recommendation! Do you know if serverwarp supports secure SSL connections to the CubeSQL database?

9

CubeSQL and it’s Xojo plug-ins natively support AES encryption over the wire. You can also configure SSL to use a certificate but it’s not necessary and is more complex.

We can configure it either way but AES is the great out of box solution.

10

[quote=369563:@David Cox]DB.AutoCommit = True
I had this line BEFORE I did a Connect to the database and it crashed the app! I’ll try adding it after I connect. Thank you.[/quote]
Did it help?

11

Yes. It’s not crashing! I am also adding:

 db.Encryption = CubeSQLPlugin.kAES256

to encrypt the network traffic. But I’m not sure which is the best amongst kAES128, kAES192, kAES256 and kSSL.

12

Also, I have no means of verifying if the traffic is now indeed being encrypted.

13

Glad to hear that solved the crashing and locking problem. Don’t know much about encrypting traffic.

14

I’m not sure about the locking — only time will tell that, but the crashing on DB.AutoCommit = True definitely doesn’t happen.

15

You have to be aware that CubeSQL only supports one concurrent “writer” (but of course multiple concurrent “readers”). so while one connection is writing to the DB, others can’t - and will have to wait in queue until the “writer” is finished (). if that takes too long (or mess up with AutoCommit, such as explained and solved above) you’ll get the “db is locked for write operations” error.
() which is obvious, because the database file is sqlite

16

What I have discovered:
• having db.AutoCommit = True before the db.Connect command causes an app crash
• having db.AutoCommit = True after the db.Connect command doesn’t cause an app crash, but you can still get ‘database locked’ errors
• leaving out the db.AutoCommit = True command, but placing a db.Commit command after every successful db.SQLExecute(…) (you need to check if db.Error not True) fixed the problem

17

Also setting

db.Encryption = CubeSQLPlugin.kAES256

does in fact encrypt all the network traffic.

18

[quote=371676:@David Cox]Also setting

db.Encryption = CubeSQLPlugin.kAES256

does in fact encrypt all the network traffic.[/quote]

This works as well:

db.Encryption=256
19

I think my way is clearer, and how the designer intended it!

20

[quote=371673:@David Cox]What I have discovered:
• having db.AutoCommit = True before the db.Connect command causes an app crash
• having db.AutoCommit = True after the db.Connect command doesn’t cause an app crash, but you can still get ‘database locked’ errors
• leaving out the db.AutoCommit = True command, but placing a db.Commit command after every successful db.SQLExecute(…) (you need to check if db.Error not True) fixed the problem[/quote]

You might want to check with Marco Bambini at marco@sqlabs.com about this. I have never had a Locked Database problem after using db.AutoCommit=True after DB.Connect, as long as I make sure any BEGIN TRANSACTION is completed with DB.Commit. If I don’t use a Transaction, I don’t bother with DB.Commit.