I don’t get this. SQLite can perform 500k writes/s (or 5k write txns/s). What app are you building which requires more than this?

To optimize the writes, you must look at inotify.

The sqlite commit is a specific pattern, it is either write or close/write.

Using inotify events can see these faster than random waits.

Respect. I’m curious what use case for >500k writes/second or 5k write txns/second you’re implementing? I know nothing about inotify and random waits. What else can you tell me?

In SQLite's C API, there is a busy handler that you can set that accepts a pointer to a function. This is where inotify would be very practical:

https://sqlite.org/c3ref/busy_handler.html

The normal operation of the busy handler is to accept a maximum timeout (also implement at the SQL level with a PRAGMA). Setting this at the C API also sets a dedicated handler.

https://sqlite.org/c3ref/busy_timeout.html

The inotify interface (which is specific to Linux) allows the kernel to alert a process when writes or close/writes occur (among other filesystem activities). The arrival of such events is a more efficient way to check if the lock has been released, rather than a wait/retry of limited duration.

Let's look at this from the shell:

  # session 1:
  $ sqlite3 test.db
  SQLite version 3.34.1 2021-01-20 14:10:07
  Enter ".help" for usage hints.
  sqlite> create table foo(bar);
  sqlite> .quit

  # session 2:
  $ inotifywait -m test.db  
  Setting up watches.
  Watches established.

  # session 1:
  $ sqlite3 test.db
  SQLite version 3.34.1 2021-01-20 14:10:07
  Enter ".help" for usage hints.
  sqlite> insert into foo values('hello, world!');
  sqlite> begin transaction;
  sqlite> insert into foo values('so long, world!');
  sqlite> delete from foo;
  sqlite> commit;
 sqlite> .quit

  # session 2 output:
  Setting up watches.
  Watches established.
  test.db OPEN 
  test.db ACCESS 
  test.db CLOSE_NOWRITE,CLOSE 
  test.db OPEN 
  test.db ACCESS 
  test.db ACCESS 
  test.db ACCESS 
  test.db ACCESS 
  test.db MODIFY 
  test.db ACCESS 
  test.db MODIFY 
  test.db CLOSE_WRITE,CLOSE 

"Path units" under systemd also use this interface.