1.Dd January 24, 2024 2.Dt SQLITE3_UNLOCK_NOTIFY 3 3.Os 4.Sh NAME 5.Nm sqlite3_unlock_notify 6.Nd unlock notification 7.Sh SYNOPSIS 8.In sqlite3.h 9.Ft int 10.Fo sqlite3_unlock_notify 11.Fa "sqlite3 *pBlocked" 12.Fa "void (*xNotify)(void **apArg, int nArg)" 13.Fa "void *pNotifyArg" 14.Fc 15.Sh DESCRIPTION 16When running in shared-cache mode, a database operation may fail with 17an SQLITE_LOCKED error if the required locks on the shared-cache 18or individual tables within the shared-cache cannot be obtained. 19See SQLite Shared-Cache Mode for a description 20of shared-cache locking. 21This API may be used to register a callback that SQLite will invoke 22when the connection currently holding the required lock relinquishes 23it. 24This API is only available if the library was compiled with the SQLITE_ENABLE_UNLOCK_NOTIFY 25C-preprocessor symbol defined. 26.Pp 27Shared-cache locks are released when a database connection concludes 28its current transaction, either by committing it or rolling it back. 29.Pp 30When a connection (known as the blocked connection) fails to obtain 31a shared-cache lock and SQLITE_LOCKED is returned to the caller, the 32identity of the database connection (the blocking connection) that 33has locked the required resource is stored internally. 34After an application receives an SQLITE_LOCKED error, it may call the 35sqlite3_unlock_notify() method with the blocked connection handle as 36the first argument to register for a callback that will be invoked 37when the blocking connections current transaction is concluded. 38The callback is invoked from within the sqlite3_step or 39sqlite3_close call that concludes the blocking connection's 40transaction. 41.Pp 42If sqlite3_unlock_notify() is called in a multi-threaded application, 43there is a chance that the blocking connection will have already concluded 44its transaction by the time sqlite3_unlock_notify() is invoked. 45If this happens, then the specified callback is invoked immediately, 46from within the call to sqlite3_unlock_notify(). 47.Pp 48If the blocked connection is attempting to obtain a write-lock on a 49shared-cache table, and more than one other connection currently holds 50a read-lock on the same table, then SQLite arbitrarily selects one 51of the other connections to use as the blocking connection. 52.Pp 53There may be at most one unlock-notify callback registered by a blocked 54connection. 55If sqlite3_unlock_notify() is called when the blocked connection already 56has a registered unlock-notify callback, then the new callback replaces 57the old. 58If sqlite3_unlock_notify() is called with a NULL pointer as its second 59argument, then any existing unlock-notify callback is canceled. 60The blocked connections unlock-notify callback may also be canceled 61by closing the blocked connection using 62.Fn sqlite3_close . 63The unlock-notify callback is not reentrant. 64If an application invokes any sqlite3_xxx API functions from within 65an unlock-notify callback, a crash or deadlock may be the result. 66.Pp 67Unless deadlock is detected (see below), sqlite3_unlock_notify() always 68returns SQLITE_OK. 69.Pp 70\fBCallback Invocation Details\fP 71.Pp 72When an unlock-notify callback is registered, the application provides 73a single void* pointer that is passed to the callback when it is invoked. 74However, the signature of the callback function allows SQLite to pass 75it an array of void* context pointers. 76The first argument passed to an unlock-notify callback is a pointer 77to an array of void* pointers, and the second is the number of entries 78in the array. 79.Pp 80When a blocking connection's transaction is concluded, there may be 81more than one blocked connection that has registered for an unlock-notify 82callback. 83If two or more such blocked connections have specified the same callback 84function, then instead of invoking the callback function multiple times, 85it is invoked once with the set of void* context pointers specified 86by the blocked connections bundled together into an array. 87This gives the application an opportunity to prioritize any actions 88related to the set of unblocked database connections. 89.Pp 90\fBDeadlock Detection\fP 91.Pp 92Assuming that after registering for an unlock-notify callback a database 93waits for the callback to be issued before taking any further action 94(a reasonable assumption), then using this API may cause the application 95to deadlock. 96For example, if connection X is waiting for connection Y's transaction 97to be concluded, and similarly connection Y is waiting on connection 98X's transaction, then neither connection will proceed and the system 99may remain deadlocked indefinitely. 100.Pp 101To avoid this scenario, the sqlite3_unlock_notify() performs deadlock 102detection. 103If a given call to sqlite3_unlock_notify() would put the system in 104a deadlocked state, then SQLITE_LOCKED is returned and no unlock-notify 105callback is registered. 106The system is said to be in a deadlocked state if connection A has 107registered for an unlock-notify callback on the conclusion of connection 108B's transaction, and connection B has itself registered for an unlock-notify 109callback when connection A's transaction is concluded. 110Indirect deadlock is also detected, so the system is also considered 111to be deadlocked if connection B has registered for an unlock-notify 112callback on the conclusion of connection C's transaction, where connection 113C is waiting on connection A. 114Any number of levels of indirection are allowed. 115.Pp 116\fBThe "DROP TABLE" Exception\fP 117.Pp 118When a call to 119.Fn sqlite3_step 120returns SQLITE_LOCKED, it is almost always appropriate to call sqlite3_unlock_notify(). 121There is however, one exception. 122When executing a "DROP TABLE" or "DROP INDEX" statement, SQLite checks 123if there are any currently executing SELECT statements that belong 124to the same connection. 125If there are, SQLITE_LOCKED is returned. 126In this case there is no "blocking connection", so invoking sqlite3_unlock_notify() 127results in the unlock-notify callback being invoked immediately. 128If the application then re-attempts the "DROP TABLE" or "DROP INDEX" 129query, an infinite loop might be the result. 130.Pp 131One way around this problem is to check the extended error code returned 132by an sqlite3_step() call. 133If there is a blocking connection, then the extended error code is 134set to SQLITE_LOCKED_SHAREDCACHE. 135Otherwise, in the special "DROP TABLE/INDEX" case, the extended error 136code is just SQLITE_LOCKED. 137.Sh IMPLEMENTATION NOTES 138These declarations were extracted from the 139interface documentation at line 9316. 140.Bd -literal 141SQLITE_API int sqlite3_unlock_notify( 142 sqlite3 *pBlocked, /* Waiting connection */ 143 void (*xNotify)(void **apArg, int nArg), /* Callback function to invoke */ 144 void *pNotifyArg /* Argument to pass to xNotify */ 145); 146.Ed 147.Sh SEE ALSO 148.Xr sqlite3_close 3 , 149.Xr sqlite3_step 3 , 150.Xr SQLITE_OK 3 151