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