SQLite updates failing with 'database is locked' despite being mutex-protected-CodePudding

Problem: I have multiple processes accessing the same SQLite database, all trying to update the same 3 columns, in the same row, in the same table. The UPDATE is protected by a sqlite3_mutex (the UPDATE is bracketed by sqlite3_mutex_enter and sqlite3_mutex_leave). This doesn't work, and it's common for one of the updates to fail with 'database is locked'.

In more detail, each process is created by Apache, which is responding to multiple (about a dozen) async Ajax requests from a client. Each request starts by recording the current time, for session management, so I get 12 processes all running at the "same" time doing the UPDATE (the logs all show them running in the same second, with unique PIDs, but I haven't checked a more precise time).

The database is opened by sqlite3_open with default options, so is serialized.

So, the obvious question - why doesn't this work? I don't think the mutex usage is wrong, so can the 'busy' period extend beyond the mutex hold? One thing I can do is open the database with sqlite3_open_v2, and change to multi-threaded instead of serialised (because each process has a unique database connection) - would this help?

I've added a simplified version of the code below. The statement being executed is UPDATE users SET Limit1=%lx, Limit2=%lx, Limit3=%lx WHERE UserName='%s', where the % fields are filled in at runtime in a C-like way.

Thanks.

   // sqlite3 version 3.26.0, Linux
   sqlite3_mutex *mutex = sqlite3_db_mutex(con);
   assert(mutex);
   sqlite3_mutex_enter(mutex);
   rc = sqlite3_exec(con, statement, 0, 0, 0);
   if(rc != SQLITE_OK)
      ... save error message from sqlite3_errmsg
   else
      ... save the changed row count from sqlite3_changes
   sqlite3_mutex_leave(mutex);

CodePudding user response：

294

In windows you can try this program http://www.nirsoft.net/utils/opened_files_view.html to find out the process is handling db file. Try closed that program for unlock database

In Linux and macOS you can do something similar, for example, if your locked file is development.db:

$ fuser development.db

This command will show what process is locking the file:

> development.db: 5430

Just kill the process...

kill -9 5430

...And your database will be unlocked.

CodePudding user response：

You mention using multiple processes, and are using the SQLite mutexes, which are designed for "thread synchronization".

Importantly, on Windows they're implemented with a Critical Section, and on Unix platforms, they're implemented with a pthread mutex without the PTHREAD_PROCESS_SHARED flag set. In both cases, they only work in the scope of one process, each process gets its own set of Mutexes for SQLite to use.

If you want to block multiple processes from accessing the same database, you'll need to use your own named mutex or other inter-process locking mechanism.