Explore sqlite processess on External Drives

[FLO-2DKaren]

If a project or a geopackage is on an external drive or even a different computer drive than the c drive, sqlite processing is either very slow or cannot proceed. Let's explore this in 2025.

[rpachaly]

I'm trying to understand the reason of this issue. Here are some information I collected:

SQLite uses file-level locking to manage access to the database file on disk. The “database is locked” error occurs when one process tries to access the database while another is actively writing to it. This situation can arise in various scenarios, such as:

1. Multiple applications accessing the database at the same time.
2. Long-running transactions that do not commit or rollback promptly.
3. Improper handling of database connections and cursors.

If the error comes from a situation like item 1, it is related to how the user is using the geopackage. Saving the geopackage in an external drive and accessing it from two different machines or using two instances of QGIS at the same time. In this case, there is nothing we can do.

If the error comes from a situation like items 2 and 3, this is something on us. We may need to check the portion of the code where this error is coming from and see if we are committing to the geopackage correctly.

About the external drive:

External drives, especially if connected via USB 2.0 or older interfaces, can have slower read/write speeds. This can make SQLite operations, which rely heavily on disk I/O, slower.
External drives, especially USB-connected ones, might enter a low-power state if inactive for some time, leading to delays when they wake up.

So, IMHO we should explore this in a case-by-case approach. When we see this in a user, let's check his workflow to see how he's working with the geopackage and, if he's not doing something wrong, then we check the code. What do you think?

[FLO-2DKaren]

Companies will have things like U drives and P drives that represent different functions. U might mean user and P might mean project but the bottom line is that the drive is only connected to the computer via a server connection. We'll test that later.

For now, the easiest test is to run the elevation sample from raster tool when the project and the data is on a USB drive. Or when the gpkg is on the c drive but the raster is on the usb drive.

What we need to know is 1. Is the plugin in a processing loop and that's why the cursor spins forever? 2. Is the plugin stuck and not actually processing data? I think we have issue 1 and if we leave the processor alone, it will finish overnight or something. It's not broken, it's just really slow. I think that because users have told me that they can get results if they leave the computer overnight.

[FLO-2DKaren]

Oh, I also just noticed the Database is locked error!!! I forgot about that message. It's common enough that we should see it during our testing but it changes my ideas expressed above.