The entire app hangs during the SQLITE timeout

There was a thread with this exact title in Feb 2016, reported as what is now issue 42544. That issue was eventually marked as solved and we all moved on. Looking at it again, however, I see that it is not solved (and I couldn’t figure out how to reopen the original thread).

It seems that it is the case that, should an SQLite query (such as begin exclusive transaction) be issued against a database by a thread, with then another thread doing the same thing soon after and in any case before the first thread does a commit, that second thread will block. Now that is entirely as it should be, EXCEPT THAT during the timeout the blocked thread is using 100% CPU, rather than being in a Wait. This locks up the UI.

I don’t now if this is the same bug as previously, so I’ll open a new case. This is 2022r4 under macOS Catalina.

You MUST use WAL mode, and check for raising errors as SQLITE BUSY. If busy, or you cancel the operation and take some action, or wait a bit , sleeping releasing some CPU, and retry few times before canceling it.

I’m already doing my own retry scheme with a database timeout set to 1, to avoid this problem. I know that WAL will reduce the likelihood of the problem, but it cannot prevent it altogether, and in any case while a thread is blocked in this way it should be asleep, not using 100% CPU.

Also: the database timeout is a double, but setting it to less than 1 seems not to work. I want to be able to either set the timeout to 120 secs, and have it work properly (i.e. not blocking the UI), or be able to set the timeout to 0.001 (1 msec), so I can time the retries better.

Just as an aside, since Xojo threading is cooperative it is possible for unyielding things in a thread to lock the whole application up. For example FolderItem actions, even threaded, are application-blocking.

Not to suggest that something isn’t malfunctioning here, I just thought to mention that it is possible (and expected) for this to happen in other circumstances. I wasn’t part of the original problem solving, so I’m not sure what to expect from SQLite.

1 Like

SQLite allows the setting of a busy handler for the case when the database is locked. SQLite will return “database is locked” immediately if the database is locked and the handler is NULL (Null is the default). There is a default busy handler available (I’m talking about the C API for SQLite) which allows a time period to be specified during which the handler retries some number of times until a specified period has elapsed. So a request can either succeed after some time has passed, or if too much time passes then “locked” will finally be returned.

I understand how that’s supposed to work, the thing I’m unsure on is how the Xojo interface for interacting with SQLite should behave. Whether it’s a while...true loop that eats up 100% cpu or if there’s a way the framework can yield the thread to other Xojo code.

I don’t know how the whole plugin / yield / thread thing works because I’m not a plugin guru. I know that Christian’s plugins generally have some kind of async method, so maybe it’s not even possible?

I dunno, that’s outside my realm of expertise. I was just offering data points, I don’t have a lot of knowledge with your issue. I do hope you get your issue resolved.

Thanks - I take your point. If there is some fundamental issue there preventing a proper handler being implemented, it would at least be nice if I could set a timeout under 1 sec. At present, if I do:

db.timeout = 0.025

it has much the same effect as:

db.timeout = 1

Hey ho.

As Parnell said, Xojo is cooperative. You put the system in a locked stated INSIDE the SQLite engine, so you can’t yield time to the event loop and that’s read as 100% CPU until it returns. Let alone that parallel access to SQLite should demand WAL and probably would solve your problem if you design a solution yielding time while getting the BUSY signal from the BEGIN EXCLUSIVE TRANSACTION. As the locked state occurs inside the SQLite engine, Xojo can only wait it to return, and a timeout probably will rollback. You have lots of things to think about and play now.

The 100% CPU may be seen in Activity Monitor.

The problem is not that oh dear the database is locked, the problem is the 100% CPU.

At the C language interface level, you can provide your own busy handler which at a minimum could sleep. OK - that would still hang the app but at 0% CPU instead of 100%. Now - from inside there I agree it may not be possible to yield to other threads or the main thread, but then I’d like to get an immediate return with error if I set the timeout to zero. I have implemented my own retry but it seems that the minimum timeout is always one second.

If I set the timeout to zero then, with a BUSY error, the SQLite interface never returns, just loops indefinitely.

As it should be, as you stopped the Xojo engine jumping inside the sql engine and getting busy there and now Xojo can’t yield idle time to other tasks.

Because you are blocked INSIDE the SQLite engine and stopped Xojo event loop doing that, and don’t intend to redesign a solution using the engine as it was designed for such cases of multiple parallel connections. What about WAL and a separate process instead of a fiber? (Xojo uses fibers, but call them threads).

Are you writing a plugin or a Xojo App? If you insist in not listening one word of what I say, and I don’t see me saying something different from what I said. I can only wish you good luck in your attempts and that’s all. :wink:

That’s how it is designed for. Zero = Forever, until ending the task. If such task takes 1 hour, it will take 1 hour waiting. If the task is in some busy deadlock state, it will never return.