2
* See the file LICENSE for redistribution information.
4
* Copyright (c) 1997-2010 Oracle. All rights reserved.
8
#include "ex_sql_utils.h"
11
* Create dozens of writer threads to insert data in parallel. The key point
12
* is that this program has to detect busy/locked status and retry if necessary.
13
* sqlite3_busy_handler() detects busy/locked status.
17
const char* db_name; /* The filename of db. */
18
int num_of_records; /* The number of records to insert. */
19
int thread_sn; /* Serial number of thread. */
23
int thread_sn; /* Serial number of thread. */
24
int max_retry; /* Max retry times. */
25
int sleep_ms; /* Time to sleep before retry again. */
30
* Busy callback handler for all operations. If the busy callback handler is
31
* NULL, then SQLITE_BUSY or SQLITE_IOERR_BLOCKED is returned immediately upon
32
* encountering the lock. If the busy callback is not NULL, then the callback
35
* This callback will be registered by SQLite's API:
36
* int sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*);
37
* That's very useful to deal with SQLITE_BUSY event automatically. Otherwise,
38
* you have to check the return code, reset statement and do retry manually.
40
* We've to use ANSI C declaration here to eliminate warnings in Visual Studio.
43
busy_handler(void *data, int retry)
45
busy_handler_attr* attr = (busy_handler_attr*)data;
47
if (retry < attr->max_retry) {
48
/* Sleep a while and retry again. */
49
printf("Thread %d hits SQLITE_BUSY %d times, retry again.\n",
50
attr->thread_sn, retry);
51
sqlite3_sleep(attr->sleep_ms);
52
/* Return non-zero to let caller retry again. */
55
/* Return zero to let caller return SQLITE_BUSY immediately. */
56
printf("Error: Thread %d had retried %d times, exit.\n",
57
attr->thread_sn, retry);
62
* Define the writer thread's workload.
63
* The writer would insert 5000 records in its thread. Commit if succeeded
64
* and rollback if failed.
77
busy_handler_attr bh_attr;
79
txn_begin = 0; /* Mark that explicit txn does not begin yet. */
82
sqlite3_open(((thread_attr *)arg)->db_name, &db);
85
/* Fetch attributes. */
86
num_of_records = ((thread_attr *)arg)->num_of_records;
87
thread_sn = ((thread_attr *)arg)->thread_sn;
89
/* Setup busy handler for all following operations. */
90
bh_attr.thread_sn = thread_sn;
91
bh_attr.max_retry = 100; /* Max retry times */
92
bh_attr.sleep_ms = 100; /* Sleep 100ms before each retry */
93
sqlite3_busy_handler(db, busy_handler, &bh_attr);
95
/* Prepare the statement for use, many times over. */
96
sql = "INSERT INTO university VALUES"
97
"(147, 'Tsinghua University China', 'tsinghua.edu.cn',"
98
"'cn', 'Asia', 237,63,432,303);";
99
rc = sqlite3_prepare_v2(db, sql, (int)strlen(sql), &stmt, NULL);
104
* When we insert data many times over, we shall use explicit
105
* transaction to speed up the operations.
107
rc = sqlite3_exec(db, "BEGIN TRANSACTION", NULL, 0, NULL);
110
txn_begin = 1; /* Mark that explicit txn began. */
112
for (i = 0; i < num_of_records; i++) {
113
rc = sqlite3_step(stmt);
115
* Even if we encounter errors, the statement still has
116
* to be reset. Otherwise following rollback always
120
if (rc != SQLITE_DONE) {
121
/* We can not return here. Rollback is required. */
127
/* Commit if no errors. */
128
rc = sqlite3_exec(db, "COMMIT TRANSACTION", NULL, 0, NULL);
134
if (rc != SQLITE_OK && rc != SQLITE_DONE) {
135
fprintf(stderr, "ERROR: %s. ERRCODE: %d.\n",
136
sqlite3_errmsg(db), rc);
137
/* Rollback if explict txn had begined. */
139
sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, 0, NULL);
143
sqlite3_finalize(stmt);
151
ex_sql_multi_thread(db, db_name)
165
/* Display current status. */
166
echo_info("Check existing record number of the table");
167
sql = "SELECT count(*) FROM university;";
171
* Create n threads and write in parallel.
173
echo_info("Now we begin to insert records by multi-writers.");
174
attr.db_name = db_name;
175
attr.num_of_records = ninsert;
177
for (i = 0; i < nthreads; i++) {
179
if (os_thread_create(&pid, writer, (void *)&attr)) {
180
register_thread_id(pid);
181
printf("%02dth writer starts to write %d rows\n",
183
sqlite3_sleep(20); /* Milliseconds. */
185
fprintf(stderr, "Failed to create thread\n");
190
/* Display result. */
191
echo_info("Check existing record number of the table");
192
sql = "SELECT count(*) FROM university;";
202
const char* db_name = "ex_sql_multi_thread.db";
204
/* Check if current lib is threadsafe. */
205
if(!sqlite3_threadsafe()) {
207
"ERROR: The libsqlite version is NOT threadsafe!\n");
211
/* Setup environment and preload data. */
213
load_table_from_file(db, university_sample_data, 1/* Silent */);
216
ex_sql_multi_thread(db, db_name);