Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Editorial changes to the unlock-notify application note. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e5ce92418151dde2791452d341510dc6 |
User & Date: | drh 2010-02-15 13:41:05.000 |
Context
2010-02-16
| ||
13:35 | Fix typo in the cintro.html document. (check-in: 56add885f4 user: drh tags: trunk) | |
2010-02-15
| ||
13:41 | Editorial changes to the unlock-notify application note. (check-in: e5ce924181 user: drh tags: trunk) | |
2010-02-13
| ||
15:43 | Correction to the PRAGMA secure_delete documentation. (check-in: 9523519a73 user: drh tags: trunk) | |
Changes
Changes to pages/unlock_notify.in.
︙ | ︙ | |||
67 68 69 70 71 72 73 | Although it is less common, a call to [sqlite3_prepare()] or [sqlite3_prepare_v2()] may also return SQLITE_LOCKED if it cannot obtain a read-lock on the sqlite_master table of each attached database. These APIs need to read the schema data contained in the sqlite_master table in order to compile SQL statements to [sqlite3_stmt*] objects. <p> | | | | | | | 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | Although it is less common, a call to [sqlite3_prepare()] or [sqlite3_prepare_v2()] may also return SQLITE_LOCKED if it cannot obtain a read-lock on the sqlite_master table of each attached database. These APIs need to read the schema data contained in the sqlite_master table in order to compile SQL statements to [sqlite3_stmt*] objects. <p> This article presents a technique using the SQLite [sqlite3_unlock_notify()] interface such that calls to [sqlite3_step()] and [sqlite3_prepare_v2()] block until the required locks are available instead of returning SQLITE_LOCKED immediately. If the sqlite3_blocking_step() or sqlite3_blocking_prepare_v2() functions presented to the left return SQLITE_LOCKED, this indicates that to block would deadlock the system. <p> The [sqlite3_unlock_notify()] API, which is only available if the library is compiled with the pre-processor symbol [SQLITE_ENABLE_UNLOCK_NOTIFY] defined, is [sqlite3_unlock_notify | documented here]. This article is not a substitute for reading the full API documentation! <p> The [sqlite3_unlock_notify()] interface is designed for use in systems that have a separate thread assigned to each [database connection]. There is nothing in the implementation that prevents a single thread from running multiple database connections. However, the [sqlite3_unlock_notify()] interface only works on a single connection at a time, so the lock |
︙ | ︙ | |||
110 111 112 113 114 115 116 | connection that the unlock-notify callback is waiting on, in this case connection Y, is known as the "blocking connection". <p> If a call to sqlite3_step() that attempts to write to a database table returns SQLITE_LOCKED, then more than one other connection may be holding a read-lock on the database table in question. In this case SQLite simply | | | | 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | connection that the unlock-notify callback is waiting on, in this case connection Y, is known as the "blocking connection". <p> If a call to sqlite3_step() that attempts to write to a database table returns SQLITE_LOCKED, then more than one other connection may be holding a read-lock on the database table in question. In this case SQLite simply selects one of those other connections arbitrarily and issues the unlock-notify callback when that connection's transaction is finished. Whether the call to sqlite3_step() was blocked by one or many connections, when the corresponding unlock-notify callback is issued it is not guaranteed that the required lock is available, only that it may be. <p> When the unlock-notify callback is issued, it is issued from within a call to sqlite3_step() (or sqlite3_close()) associated with the blocking connection. It is illegal to invoke any sqlite3_XXX() API functions from within an unlock-notify callback. The expected use is that the unlock-notify callback will signal some other waiting thread or schedule some action to take place later. <p> The algorithm used by the sqlite3_blocking_step() function is as follows: <ol> <li><p> Call sqlite3_step() on the supplied statement handle. If the call |
︙ | ︙ | |||
149 150 151 152 153 154 155 156 157 158 159 160 | SQLITE_ROW and then the next SQLITE_LOCKED), the statement handle may be reset at this point without affecting the results of the query from the point of view of the caller. If sqlite3_reset() were not called at this point, the next call to sqlite3_step() would return SQLITE_MISUSE. <li><p> Return to step 1. <p> The algorithm used by the sqlite3_blocking_prepare_v2() function is similar, except that step 4 (reseting the statement handle) is omitted. | > < | | | | | | | | 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 | SQLITE_ROW and then the next SQLITE_LOCKED), the statement handle may be reset at this point without affecting the results of the query from the point of view of the caller. If sqlite3_reset() were not called at this point, the next call to sqlite3_step() would return SQLITE_MISUSE. <li><p> Return to step 1. </ol> <p> The algorithm used by the sqlite3_blocking_prepare_v2() function is similar, except that step 4 (reseting the statement handle) is omitted. <p><b>Writer Starvation</b> <p> Multiple connections may hold a read-lock simultaneously. If many threads are acquiring overlapping read-locks, it might be the case that at least one thread is always holding a read lock. Then a table waiting for a write-lock will wait forever. This scenario is called "writer starvation." <p> SQLite helps applications avoid writer starvation. After any attempt to obtain a write-lock on a table fails (because one or more other connections are holding read-locks), all attempts to open new transactions on the shared-cache fail until one of the following is true: <ul> <li> The current writer concludes its transaction, OR <li> The number of open read-transactions on the shared-cache drops to zero. </ul> <p> Failed attempts to open new read-transactions return SQLITE_LOCKED to the caller. If the caller then calls [sqlite3_unlock_notify()] to register for an unlock-notify callback, the blocking connection is the connection that currently has an open write-transaction on the shared-cache. This prevents writer-starvation since if no new read-transactions may be opened and assuming all existing read-transactions are eventually concluded, the writer will eventually have an opportunity to obtain the required write-lock. <p><b>The pthreads API</b> <p> By the time [sqlite3_unlock_notify()] is invoked by |
︙ | ︙ | |||
237 238 239 240 241 242 243 | <p> This way, it doesn't matter if the unlock-notify callback has already been invoked, or is being invoked, when the wait_for_unlock_notify() thread begins blocking. <p><b>Possible Enhancements</b> | | | 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 | <p> This way, it doesn't matter if the unlock-notify callback has already been invoked, or is being invoked, when the wait_for_unlock_notify() thread begins blocking. <p><b>Possible Enhancements</b> <p> The code in this article could be improved in at least two ways: <ul> <li> It could manage thread priorities. <li> It could handle a special case of SQLITE_LOCKED that can occur when dropping a table or index. </ul> |
︙ | ︙ | |||
269 270 271 272 273 274 275 | statements complete execution in the meantime, re-attempting the "DROP TABLE" or "DROP INDEX" statement will return another SQLITE_LOCKED error. In the implementation of sqlite3_blocking_step() shown to the left, this could cause an infinite loop. <p> The caller could distinguish between this special "DROP TABLE|INDEX" | | | 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 | statements complete execution in the meantime, re-attempting the "DROP TABLE" or "DROP INDEX" statement will return another SQLITE_LOCKED error. In the implementation of sqlite3_blocking_step() shown to the left, this could cause an infinite loop. <p> The caller could distinguish between this special "DROP TABLE|INDEX" case and other cases by using [extended error codes]. When it is appropriate to call [sqlite3_unlock_notify()], the extended error code is SQLITE_LOCKED_SHAREDCACHE. Otherwise, in the "DROP TABLE|INDEX" case, it is just plain SQLITE_LOCKED. Another solution might be to limit the number of times that any single query could be reattempted (to say 100). Although this might be less efficient than one might wish, the situation in question is not likely to occur often. <div style="clear:both"></div> |