Documentation Source Text

Check-in [e5ce924181]
Login

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: e5ce92418151dde2791452d341510dc65e330ea7
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
Unified Diff Ignore Whitespace Patch
Changes to pages/unlock_notify.in.
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 page presents a technique using the SQLite [sqlite3_unlock_notify()]
  API to create a versions of [sqlite3_step()] and [sqlite3_prepare_v2()] that 
  block until the required locks are available instead of returning 
  SQLITE_LOCKED immediately, for use in multi-threaded applications. 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 page is no 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







|
|

|







|
|







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
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 at random 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 single 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







|










|







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
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.


<p>
  The algorithm used by the sqlite3_blocking_prepare_v2() function is similar,
  except that step 4 (reseting the statement handle) is omitted.

</ol>

<p><b>Writer Starvation</b>

<p>
  Based on the description above, it could be concluded that if there are
  sufficient database readers reading the same table often enough, it is
  possible that the table will never become unlocked and that a connection
  waiting for a write-lock on the table will wait indefinitely. This 
  phenomena is known as writer-starvation.

<p>
  SQLite helps applications avoid this scenario. 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 as, 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







>





<




|
|
|
|
|


|














|







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
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 on this page 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>








|







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
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>







|








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>