~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

121

122

123

<style>

124

pre a:visited, pre a:link { text-decoration: none ; color: #40534b }

125

pre {

126

background: #F3F3F3;

127

float: right;

128

padding: 1ex 2ex;

129

margin-left: 1em;

130

border: solid black 1px;

131

}

132

h1,h2 { clear: both ; text-align: center }

133

</style>

134

135

<h1>Using the sqlite3_unlock_notify() API</h1>

136

137

<pre>

138

<span style="color:blue;font-style:italic">/* This example uses the pthreads API */</span>

139

#include <pthread.h>

140

141

142

<span style="color:blue;font-style:italic">** A pointer to an instance of this structure is passed as the user-context</span>

143

<span style="color:blue;font-style:italic">** pointer when registering for an unlock-notify callback.</span>

144

145

typedef struct UnlockNotification UnlockNotification;

146

struct UnlockNotification {

147

int fired; <span style="color:blue;font-style:italic">/* True after unlock event has occurred */</span>

148

pthread_cond_t cond; <span style="color:blue;font-style:italic">/* Condition variable to wait on */</span>

149

pthread_mutex_t mutex; <span style="color:blue;font-style:italic">/* Mutex to protect structure */</span>

150

};

151

152

153

<span style="color:blue;font-style:italic">** This function is an unlock-notify callback registered with SQLite.</span>

154

155

static void unlock_notify_cb(void **apArg, int nArg){

156

int i;

157

for(i=0; i<nArg; i++){

158

UnlockNotification *p = (UnlockNotification *)apArg[i];

159

pthread_mutex_lock(&p->mutex);

160

p->fired = 1;

161

pthread_cond_signal(&p->cond);

162

pthread_mutex_unlock(&p->mutex);

163

}

164

}

165

166

167

<span style="color:blue;font-style:italic">** This function assumes that an SQLite API call (either <a href="c3ref/prepare.html">sqlite3_prepare_v2</a>() </span>

168

<span style="color:blue;font-style:italic">** or <a href="c3ref/step.html">sqlite3_step</a>()) has just returned SQLITE_LOCKED. The argument is the</span>

169

<span style="color:blue;font-style:italic">** associated database connection.</span>

170

171

<span style="color:blue;font-style:italic">** This function calls <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify</a>() to register for an </span>

172

<span style="color:blue;font-style:italic">** unlock-notify callback, then blocks until that callback is delivered </span>

173

<span style="color:blue;font-style:italic">** and returns SQLITE_OK. The caller should then retry the failed operation.</span>

174

175

<span style="color:blue;font-style:italic">** Or, if <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify</a>() indicates that to block would deadlock </span>

176

<span style="color:blue;font-style:italic">** the system, then this function returns SQLITE_LOCKED immediately. In </span>

177

<span style="color:blue;font-style:italic">** this case the caller should not retry the operation and should roll </span>

178

<span style="color:blue;font-style:italic">** back the current transaction (if any).</span>

179

180

static int wait_for_unlock_notify(<a href="c3ref/sqlite3.html">sqlite3</a> *db){

181

int rc;

182

UnlockNotification un;

183

184

<span style="color:blue;font-style:italic">/* Initialize the UnlockNotification structure. */</span>

185

un.fired = 0;

186

pthread_mutex_init(&un.mutex, 0);

187

pthread_cond_init(&un.cond, 0);

188

189

<span style="color:blue;font-style:italic">/* Register for an unlock-notify callback. */</span>

190

rc = <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify</a>(db, unlock_notify_cb, (void *)&un);

191

assert( rc==SQLITE_LOCKED || rc==SQLITE_OK );

192

193

<span style="color:blue;font-style:italic">/* The call to <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify</a>() always returns either SQLITE_LOCKED </span>

194

<span style="color:blue;font-style:italic"> ** or SQLITE_OK. </span>

195

196

<span style="color:blue;font-style:italic"> ** If SQLITE_LOCKED was returned, then the system is deadlocked. In this</span>

197

<span style="color:blue;font-style:italic"> ** case this function needs to return SQLITE_LOCKED to the caller so </span>

198

<span style="color:blue;font-style:italic"> ** that the current transaction can be rolled back. Otherwise, block</span>

199

<span style="color:blue;font-style:italic"> ** until the unlock-notify callback is invoked, then return SQLITE_OK.</span>

200

201

if( rc==SQLITE_OK ){

202

pthread_mutex_lock(&un.mutex);

203

if( !un.fired ){

204

pthread_cond_wait(&un.cond, &un.mutex);

205

}

206

pthread_mutex_unlock(&un.mutex);

207

}

208

209

<span style="color:blue;font-style:italic">/* Destroy the mutex and condition variables. */</span>

210

pthread_cond_destroy(&un.cond);

211

pthread_mutex_destroy(&un.mutex);

212

213

return rc;

214

}

215

216

217

<span style="color:blue;font-style:italic">** This function is a wrapper around the SQLite function <a href="c3ref/step.html">sqlite3_step</a>().</span>

218

<span style="color:blue;font-style:italic">** It functions in the same way as step(), except that if a required</span>

219

<span style="color:blue;font-style:italic">** shared-cache lock cannot be obtained, this function may block waiting for</span>

220

<span style="color:blue;font-style:italic">** the lock to become available. In this scenario the normal API step()</span>

221

<span style="color:blue;font-style:italic">** function always returns SQLITE_LOCKED.</span>

222

223

<span style="color:blue;font-style:italic">** If this function returns SQLITE_LOCKED, the caller should rollback</span>

224

<span style="color:blue;font-style:italic">** the current transaction (if any) and try again later. Otherwise, the</span>

225

<span style="color:blue;font-style:italic">** system may become deadlocked.</span>

226

227

int sqlite3_blocking_step(<a href="c3ref/stmt.html">sqlite3_stmt</a> *pStmt){

228

int rc;

229

while( SQLITE_LOCKED==(rc = <a href="c3ref/step.html">sqlite3_step</a>(pStmt)) ){

230

rc = wait_for_unlock_notify(<a href="c3ref/db_handle.html">sqlite3_db_handle</a>(pStmt));

231

if( rc!=SQLITE_OK ) break;

232

<a href="c3ref/reset.html">sqlite3_reset</a>(pStmt);

233

}

234

return rc;

235

}

236

237

238

<span style="color:blue;font-style:italic">** This function is a wrapper around the SQLite function <a href="c3ref/prepare.html">sqlite3_prepare_v2</a>().</span>

239

<span style="color:blue;font-style:italic">** It functions in the same way as prepare_v2(), except that if a required</span>

240

<span style="color:blue;font-style:italic">** shared-cache lock cannot be obtained, this function may block waiting for</span>

241

<span style="color:blue;font-style:italic">** the lock to become available. In this scenario the normal API prepare_v2()</span>

242

<span style="color:blue;font-style:italic">** function always returns SQLITE_LOCKED.</span>

243

244

<span style="color:blue;font-style:italic">** If this function returns SQLITE_LOCKED, the caller should rollback</span>

245

<span style="color:blue;font-style:italic">** the current transaction (if any) and try again later. Otherwise, the</span>

246

<span style="color:blue;font-style:italic">** system may become deadlocked.</span>

247

248

int sqlite3_blocking_prepare_v2(

249

<a href="c3ref/sqlite3.html">sqlite3</a> *db, <span style="color:blue;font-style:italic">/* Database handle. */</span>

250

const char *zSql, <span style="color:blue;font-style:italic">/* UTF-8 encoded SQL statement. */</span>

251

int nSql, <span style="color:blue;font-style:italic">/* Length of zSql in bytes. */</span>

252

<a href="c3ref/stmt.html">sqlite3_stmt</a> **ppStmt, <span style="color:blue;font-style:italic">/* OUT: A pointer to the prepared statement */</span>

253

const char **pz <span style="color:blue;font-style:italic">/* OUT: End of parsed string */</span>

254

){

255

int rc;

256

while( SQLITE_LOCKED==(rc = <a href="c3ref/prepare.html">sqlite3_prepare_v2</a>(db, zSql, nSql, ppStmt, pz)) ){

257

rc = wait_for_unlock_notify(db);

258

if( rc!=SQLITE_OK ) break;

259

}

260

return rc;

261

}

262

</pre>

263

264

265

<p>

266

When two or more connections access the same database in shared-cache

267

mode, read and write (shared and exclusive) locks on individual tables

268

are used to ensure that concurrently executing transactions are kept

269

isolated. Before writing to a table, a write (exclusive) lock must be

270

obtained on that table. Before reading, a read (shared) lock must be

271

obtained. A connection releases all held table locks when it concludes

272

its transaction. If a connection cannot obtain a required lock, then

273

the call to <a href="c3ref/step.html">sqlite3_step()</a> returns SQLITE_LOCKED.

274

275

<p>

276

Although it is less common, a call to <a href="c3ref/prepare.html">sqlite3_prepare()</a> or

277

<a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> may also return SQLITE_LOCKED if it cannot obtain

278

a read-lock on the sqlite_master table of each attached database. These

279

APIs need to read the schema data contained in the sqlite_master table

280

in order to compile SQL statements to <a href="c3ref/stmt.html">sqlite3_stmt*</a> objects.

281

282

<p>

283

This article presents a technique using the SQLite <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a>

284

interface such that calls to <a href="c3ref/step.html">sqlite3_step()</a> and <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a>

285

block until the required locks are available instead of returning

286

SQLITE_LOCKED immediately. If the

287

sqlite3_blocking_step() or sqlite3_blocking_prepare_v2() functions presented

288

to the left return SQLITE_LOCKED, this indicates that to block would

289

deadlock the system.

290

291

<p>

292

The <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> API, which is only available if the library is

293

compiled with the pre-processor symbol <a href="compile.html#enable_unlock_notify">SQLITE_ENABLE_UNLOCK_NOTIFY</a> defined,

294

is <a href="c3ref/unlock_notify.html">documented here</a>. This article is not a

295

substitute for reading the full API documentation!

296

297

<p>

298

The <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> interface is designed for use in systems

299

that have a separate thread assigned to each <a href="c3ref/sqlite3.html">database connection</a>. There

300

is nothing in the implementation that prevents a single thread from running

301

multiple database connections. However, the <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a>

302

interface only works on a single connection at a time, so the lock

303

resolution logic presented here will only work for a single

304

database connection per thread.

305

306

<p><b>The sqlite3_unlock_notify() API</b>

307

308

<p>

309

After a call to <a href="c3ref/step.html">sqlite3_step()</a> or <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> returns

310

SQLITE_LOCKED, the <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> API may be invoked to register

311

for an unlock-notify callback. The unlock-notify callback is invoked by

312

SQLite after the database connection holding the table-lock that prevented

313

the call to <a href="c3ref/step.html">sqlite3_step()</a> or <a href="c3ref/prepare.html">sqlite3_prepare_v2()</a> from succeeding has

314

finished its transaction and released all locks. For example, if a call to

315

sqlite3_step() is an attempt to read from table X, and some other connection

316

Y is holding a write-lock on table X, then sqlite3_step() will return

317

SQLITE_LOCKED. If <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> is then called, the unlock-notify

318

callback will be invoked after connection Y's transaction is concluded. The

319

connection that the unlock-notify callback is waiting on, in this case

320

connection Y, is known as the "blocking connection".

321

322

<p>

323

If a call to sqlite3_step() that attempts to write to a database table

324

returns SQLITE_LOCKED, then more than one other connection may be holding

325

a read-lock on the database table in question. In this case SQLite simply

326

selects one of those other connections arbitrarily and issues the

327

unlock-notify callback when that connection's transaction is finished.

328

Whether the call to sqlite3_step() was blocked by one or many connections,

329

when the corresponding unlock-notify callback is issued it is not

330

guaranteed that the required lock is available, only that it may be.

331

332

<p>

333

When the unlock-notify callback is issued, it is issued from within a

334

call to sqlite3_step() (or sqlite3_close()) associated with the blocking

335

connection. It is illegal to invoke any sqlite3_XXX() API functions from

336

within an unlock-notify callback. The expected use is that the unlock-notify

337

callback will signal some other waiting thread or schedule some action

338

to take place later.

339

340

<p>

341

The algorithm used by the sqlite3_blocking_step() function is as follows:

342

343

<ol>

344

<li><p> Call sqlite3_step() on the supplied statement handle. If the call

345

returns anything other than SQLITE_LOCKED, then return this value

346

to the caller. Otherwise, continue.

347

348

<li><p> Invoke <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> on the database connection handle

349

associated with the supplied statement handle to register for an

350

unlock-notify callback. If the call to unlock_notify() returns

351

SQLITE_LOCKED, then return this value to the caller.

352

353

<li><p> Block until the unlock-notify callback is invoked by another thread.

354

355

<li><p> Call sqlite3_reset() on the statement handle. Since an

356

SQLITE_LOCKED error may only occur on the first call to sqlite3_step()

357

(it is not possible for one call to sqlite3_step() to return

358

SQLITE_ROW and then the next SQLITE_LOCKED), the statement handle may

359

be reset at this point without affecting the results of the query

360

from the point of view of the caller. If sqlite3_reset() were not

361

called at this point, the next call to sqlite3_step() would return

362

SQLITE_MISUSE.

363

364

<li><p> Return to step 1.

365

</ol>

366

367

<p>

368

The algorithm used by the sqlite3_blocking_prepare_v2() function is similar,

369

except that step 4 (resetting the statement handle) is omitted.

370

371

372

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

373

374

<p>

375

Multiple connections may hold a read-lock simultaneously. If many threads

376

are acquiring overlapping read-locks, it might be the case that at least

377

one thread is always holding a read lock.

378

Then a table waiting for a write-lock will wait forever.

379

This scenario is called "writer starvation."

380

381

<p>

382

SQLite helps applications avoid writer starvation. After any attempt to

383

obtain a write-lock on a table fails (because one or more other

384

connections are holding read-locks), all attempts to open new transactions

385

on the shared-cache fail until one of the following is true:

386

387

<ul>

388

<li> The current writer concludes its transaction, OR

389

<li> The number of open read-transactions on the shared-cache drops to zero.

390

</ul>

391

392

<p>

393

Failed attempts to open new read-transactions return SQLITE_LOCKED to the

394

caller. If the caller then calls <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> to register for

395

an unlock-notify callback, the blocking connection is the connection that

396

currently has an open write-transaction on the shared-cache. This prevents

397

writer-starvation since if no new read-transactions may be opened and

398

assuming all existing read-transactions are eventually concluded, the

399

writer will eventually have an opportunity to obtain the required

400

write-lock.

401

402

<p><b>The pthreads API</b>

403

404

<p> By the time <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> is invoked by

405

wait_for_unlock_notify(), it is possible that the blocking connection

406

that prevented the sqlite3_step() or sqlite3_prepare_v2() call from

407

succeeding has already finished its transaction. In this case, the

408

unlock-notify callback is invoked immediately, before

409

<a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> returns. Or, it is possible that the

410

unlock-notify callback is invoked by a second thread after

411

<a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> is called but before the thread starts waiting

412

to be asynchronously signaled.

413

414

<p> Exactly how such a potential race-condition is handled depends on the

415

threads and synchronization primitives interface used by the application.

416

This example uses pthreads, the interface provided by modern UNIX-like

417

systems, including Linux.

418

419

<p> The pthreads interface provides the pthread_cond_wait() function.

420

This function allows the caller to simultaneously release a mutex

421

and start waiting for an asynchronous signal. Using this function,

422

a "fired" flag and a mutex, the race-condition described above may

423

be eliminated as follows:

424

425

<p> When the unlock-notify callback is invoked, which may be before the

426

thread that called <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> begins waiting for the

427

asynchronous signal, it does the following:

428

429

<ol>

430

<li> Obtains the mutex.

431

<li> Sets the "fired" flag to true.

432

<li> Attempts to signal a waiting thread.

433

<li> Releases the mutex.

434

</ol>

435

436

<p> When the wait_for_unlock_notify() thread is ready to begin waiting for

437

the unlock-notify callback to arrive, it:

438

439

<ol>

440

<li> Obtains the mutex.

441

<li> Checks if the "fired" flag has been set. If so, the unlock-notify

442

callback has already been invoked. Release the mutex and continue.

443

<li> aAtomically releases the mutex and begins waiting for the

444

asynchronous signal. When the signal arrives, continue.

445

</ol>

446

447

<p> This way, it doesn't matter if the unlock-notify callback has already

448

been invoked, or is being invoked, when the wait_for_unlock_notify()

449

thread begins blocking.

450

451

<p><b>Possible Enhancements</b>

452

453

<p> The code in this article could be improved in at least two ways:

454

455

<ul>

456

<li> It could manage thread priorities.

457

<li> It could handle a special case of SQLITE_LOCKED that can occur

458

when dropping a table or index.

459

</ul>

460

461

<p>

462

Even though the <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> function only allows the caller

463

to specify a single user-context pointer, an unlock-notify callback

464

is passed an array of such context pointers. This is because if when

465

a blocking connection concludes its transaction, if there is more

466

than one unlock-notify registered to call the same C function, the

467

context-pointers are marshaled into an array and a single callback

468

issued. If each thread were assigned a priority, then instead of just

469

signaling the threads in arbitrary order as this implementation does,

470

higher priority threads could be signaled before lower priority threads.

471

472

<p>

473

If a "DROP TABLE" or "DROP INDEX" SQL command is executed, and the

474

same database connection currently has one or more actively

475

executing SELECT statements, then SQLITE_LOCKED is returned. If

476

<a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a> is called in this case, then the specified

477

callback will be invoked immediately. Re-attempting the "DROP

478

TABLE" or "DROP INDEX" statement will return another SQLITE_LOCKED

479

error. In the implementation of sqlite3_blocking_step() shown to the

480

left, this could cause an infinite loop.

481

482

<p>

483

The caller could distinguish between this special "DROP TABLE|INDEX"

484

case and other cases by using <a href="c3ref/c_abort_rollback.html">extended error codes</a>. When it is appropriate

485

to call <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a>, the extended error code is

486

SQLITE_LOCKED_SHAREDCACHE. Otherwise, in the "DROP TABLE|INDEX" case,

487

it is just plain SQLITE_LOCKED. Another solution might be to limit

488

the number of times that any single query could be reattempted (to say

489

100). Although this might be less efficient than one might wish, the

490

situation in question is not likely to occur often.

491

492

493