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