one sqlite database to multiple autoit users

[alexlimlexart]

I was reading this "How to corrupt your database" in the sqlite help file and my condern right now is a single sqlite database being used by multiple autoit at the same time. This means that different users in the network are opening/writing/saving to a single shared sqlite database. Is this possible? If not then what are the best approach for this to prevent corruption? And the file lock thing mentioned in the help file.. will this prevent multiple users from accessing the file at the same time?

Thanks in advance.

[footswitch]

Hey there.

Yes, network shares are way dangerous because network file locks don't work the same way as local file system locks.

You can use a "semaphore" system, I know I've seen it in the forum in the past.

Ah, here it is: www.autoitscript.com/forum/topic/117033-file-locking-with-cooperative-semaphores/

Basically it creates a folder via command line, in the same directory structure as the database file.

Whenever you want to write to the database, you need to check for the existance of that semaphore folder. If it's there, some other user is writing to the database, thus locking it.

When you stop writing to the databse, remove the folder, thus removing the lock.

You also need to implement a timeout feature that will bypass a lock when it's there for too long (these are called dead locks, I guess).

footswitch

[footswitch]

I know this isn't what you asked, but let me say that SQLite just wasn't made for concurrent access over a network share.

My suggestion is, if this isn't a portable software, neither is it stored in one of those standalone network drives, and if you have administrative privileges over the server, you may as well install MySQL or MS SQL Express, which handle "locks" appropriately.

There are UDFs for those as well; though not officially supported, the SQL one for instance works really well for me.

Good luck either way

[jchd]

Hi alex,

Please read again what I wrote about this in your previous thread.

Now you have basically two ways to handle this: either drop SQLite and use a client/server engine instead or workaround the unreliable file locking issue.

The latter solution is much easier to manage: for a way to do it.

@footswitch,

I disagree: SQL indeed _was_ made for shared access. It just happens that no commonly available remote file locking protocol works correctly. This isn't SQLite fault, since its code is correct.

[footswitch]

jchd,

I'm not fond of disagreeing over semantics

In SQLite, "shared access" is supported within the same "machine".

From what I've read some time ago (in Mike Owens' book if I'm not mistaken), when SQLite was first developed, shared access across the network was an intention but not an actual goal, not one that was "written in stone" anyways. If you ask me, it still remains very much so.

EDIT: but yes you're absolutely right that it's not SQLite's "fault"!

Edited January 11, 2012 by footswitch

[ChrisL]

I do this thing where I create a text file in the same network folder as the database and I write the local computer name in the text file.

If the text file exists and it is not "My" @ComputerName on line 1 then I know someone else is using the database, so I wait until the text file is removed before "My Computer" then creates a new one and writes its @ComputerName to the file.

So far this has worked OK but I don't have a deadlock get around

[alexlimlexart]

All replies are good... I am so limited with privileges and so looks like a "turn based" system would work plus a timeout. Jchd, glad you replied. I know what you meant from the other thread but i just wanted to put this into a new one. I guess i am going on a fair direction. Great help from here thanks a lot guys.

[jchd]

As I said, I've something much better in the works, but not yet ready for consumption.

Anyway, keep in mind that reads (SELECTs) also need to read-lock the DB file: searching an index or doing a full table scan while another process is changing the DB under your feet would certainly be a very bad thing!

Hence the locking semaphore file has to be used not only for writes (UPDATE, INSERT, CREATE, ALTER, REINDEX, ..., various pragmas).

Note that the semaphore file is subject itself to the same locking issues than the DB, albeit at a slower pace and without the difference in locking types SQLite uses. So it isn't a perfectly failproof method either.

What I and other say about all this is nonetheless overly pessimistic in many use cases. In practice, unless you're doing a heavy use of the DB from 20+ machines, you're unlikely to encounter corruption easily (modulo decent network reliability). But as the saying goes, better sad than sorry.

Have a look and use this wrapper UDF to backup your DB at times. It will happily hot backup on the fly reliably (but backup restarts when a write is made, if using the default journaling mode).

In any event, make good use of transactions to group statements that need to operate fast (bulk inserts) or as an atomic block (typically read-modify-write operations).

Right after openning a DB connection, use _SQLite_SetTimeout with a (ms) value greater than the duration of the worst possible sequence of worst duration statements you might ever encounter. To make a long story short, don't hesitate to make it 5 or 10 minutes or much more if you need much more, meaning "wait forever, but not that much".

Use BEGIN IMMEDIATE .. COMMIT transactions. With that, you are sure to never get SQLITE_BUSY nor risk a DB deadlock. This way, you don't even have to test for that.

I'll do my best to come up with a better transparent and reliable LAN support but my current "half homeless" situation makes it difficult for me to work normally.

Edited January 12, 2012 by jchd

[skin27]

As I said, I've something much better in the works, but not yet ready for consumption.

JCHD, is this anything near consumption

[jchd]

I'm really sorry to say NO.

I've not written a single line of AutoIt code for months and that situation will last for some time.

In the meantime, I've heard of a promising solution which I've had no time to try myself.

Have a look at this. From what I read it should be fairly easy to adapt the standard SQLite UDF to invoke this library instead.

[skin27]

Ok, that's clear. Thanks for the link, this seems an interesting approach to try out.

[StringingLong]

Why struggle with the inadequacies of SqlLite and just use MySql instead?

It's like if you know it has issues, irregardless of who's fault it is, then why bother using it at all if it can't be fixed?

In fact, if you took the time to read the FAQ, its all layed out here:

http://www.sqlite.org/faq.html#q5

If it isn't the right tool, trying to fit a round peg into a square hole is a complete waste of time. Bite the bullet and use something else.

[jchd]

Fast, flexible, reliable, unumcombered from legaleese, portable, blind easy to setup, predictable, stable, ...

These are only some of the advantages of SQLite over most (if not all) RDBMS, especially over MySQL.

I fail to see why the fore-mentionned DLL wouldn't fit the sqare hole.