Database has locked during process

[jsadoine]

Describe the bug

Traceback (most recent call last):
  File "/casa/host/install/python/brainvisa/processes.py", line 3412, in _processExecution
    result = process.execution(self)
  File "/casa/host/install/brainvisa/toolboxes/morphologist/processes/segmentationpipeline/components/GreyWhiteMesh.py", line 74, in execution
    tm.copyReferential(self.hemi_cortex, self.white_mesh)
  File "/casa/host/install/python/brainvisa/registration.py", line 308, in copyReferential
    refId = self.referential(sourceDiskItem)
  File "/casa/host/install/python/brainvisa/registration.py", line 72, in referential
    return neuroHierarchy.databases.getDiskItemFromUuid(uuid, None)
  File "/casa/host/install/python/brainvisa/data/sqlFSODatabase.py", line 2259, in getDiskItemFromUuid
    item = database.getDiskItemFromUuid(uuid, None)
  File "/casa/host/install/python/brainvisa/data/sqlFSODatabase.py", line 1455, in getDiskItemFromUuid
    cursor = self._getDatabaseCursor()
  File "/casa/host/install/python/brainvisa/data/sqlFSODatabase.py", line 1071, in _getDatabaseCursor
    cursor.execute('PRAGMA synchronous =  0')
sqlite3.OperationalError: database is locked

To Reproduce
Steps to reproduce the behavior:

1. Go to 'Morphologist 2021'
2. Select T1 image
3. Unselect all before Hemispere Process + Grey White Classification +Grey White Topology (I need to reproduce only step for create white and hemi meshes)
4. Unselect all after Cortical Folds
5. Launch Process
6. See error on the database

Expected behavior
The Morphologist 2021 process crashes with the error message: "database has been locked". Afterthat I can go to Preference -> Database->Edit and I select or unselect activate history in expert setting, click OK and the process works.

Screenshots

Environment:

• Engine: Singularity
• Version of BrainVISA [5.1.1]

Additional context
Only one database is problematic, I try to delete sqlite files and uptade database and problem persist. I have to do my process one by one, because i need to go in database preference for each subject to run the subject.

[denisri]

Hi,
I don't experience this problem but I haven't tried on your database. Generally deleting the .sqlite file can help, but brainvisa has to be shut down before the file is removed, and restarted after that. Is it what you have done ?
In rare cases it has already happened that sqlite was totally locked on any .sqlite file on a given network filesystem, from a given machine. In such a case we had to ask the sys admins to restart a service on the filesystem server.

[jsadoine]

Yes I shut down before removing the file and I restarted after and do the update.

[denisri]

We have tried an attempt to solve the problem, but I'm not sure it will have any effect. We have fixed a few database cursors misuses, and increased the connection timeout (to 10s). But for me none of them would explain why the problem only happens on one database and not systematically on all.
Anyway you can give it a try. If you are using the brainvisa installed in Neurospin, then just restart it: it has already been patched. If you are using another installation, you have to install it read-write and use the update feature in the "bv" configuration GUI.

[jsadoine]

Thanks for your feedback, the problem persists. I have the same problem on other databases. I can manage the problem by changing the database configuration for each subject one by one by selecting or unselecting the historic.

[denisri]

Well, I feared that...
If I understand the problem exists both when history recording is activated or disabled on the database, but is just fixed temporarily when you change this switch ? That's kind of crazy... I cannot imagine how it comes...
I still suspect there is something wrong with the network filesystem server since we don't have this kind of issue on a local filesystem.
So there is a workaround, which is not very beautiful, and is not a long-term solution, especially for a database shared between users, but you can try it, and use it to do your processings: use a local database file:
in the database settings, edit the expert settings of the database (where you select "history management", also), and fill in the "sqliteFileName" with a file on the local filesystem, like /tmp/database-something.sqlite, or /volatile/this_database/database.sqlite.
Then you will need to rebuild the database (it should be prompted when you validate the database modifications).
The database will then be on the local filesystem, thus should be faster, and I hope will not suffer the problem. But as it is local, uses from another machine will duplicate the database, and the database file directory will need to exist on all machines.

[jsadoine]

Yes, that's exactly it. Thanks for the help.

[ylep]

I agree that the most likely candidate is a faulty lock implementation on the NFS... I’d be curious to know if the problem persists when using the nolock option for the NFS mount.

[denisri]

I don't know what exactly does the option nolock, but if it results into not locking at all, it will likely produce database corruptions on concurrent access, which will not be better... ?

[ylep]

nolock, as far as I understand, means that locks will be local to each NFS client. Indeed it can lead to database corruptions on concurrent access from different machines, so it is not a long-term solution, merely a way of making sure if the locking mechanism is at fault here. Unfortunately, I could not test this because I failed to remount the NFS on @jsadoine’s machine with nolock, or with local_lock=all. Maybe the mount options are forced by the server or the automounter...

Besides, it seems that the issue persists with a local database on the /volatile. I asked @jsadoine to deactivate all other databases to make sure that the active database is actually the one leading to the error.

[denisri]

So it also happens on a local filesystem ?? If this is true, then the suspicion on NFS locks is wrong.
The good news about it is that the problem is probably on our side, so it might be possible to fix it.
The bad news is that I really don't know what is wrong, and why it happens sometimes and not always. I'm not a database expert...