title: sqlite and threading with iPhone SDK
body: <p>I have an iPhone app that is using sqlite 3.6 (not with FMDB) to store and load data. I load the database when the app loads and uses the same database connection through the whole app.</p>
  
  <p>In a background thread the app downloads some data from a webserver and writes to the database. At the same time the main thread also might need to write to the same database. This sometimes leads to EXC_BAD_ACCESS as both threads are trying to access the database.</p>
  
  <p>What is the best and easiest way to be able to use the database from different threads?</p>
  
  <p>This is an example that shows the problem:</p>
  
  <pre><code>sqlite3 *database;   
  
  - (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {   
  
      NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
      NSString *documentsDirectory = [paths objectAtIndex:0];
      NSString *path = [documentsDirectory stringByAppendingPathComponent:@"database.db"];
  
      if (sqlite3_open([path UTF8String], &amp;database) != SQLITE_OK) {
          sqlite3_close(database);
          return YES;
      }
  
      [NSThread detachNewThreadSelector:@selector(test) toTarget:self withObject:nil];
      [self test];
      return YES;
  }
  
  -(void)test {
      for (int i = 0; i &lt; 2000; i++) {
          NSLog(@"%i",i);
          sqlite3_exec([self getDb],"UPDATE mytable SET test=''", 0, 0, 0);
      }
  }
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>After willcodejavaforfood's answer below I've tried to change my code to use a separate database object (connection) for each separate thread and also added sqlite3_busy_timeout() so that sqlite will retry to write if the database is busy. Now I don't get EXC_BAD_ACCESS anymore but I've noticed that not all data get inserted. So this is not a stable solution either. It seems to be really hard to get sqlite working with threading..</p>
  
  <p>My new solution with separate connections:</p>
  
  <pre><code>-(void)test {
      sqlite3 *db = [self getNewDb];
      for (int i = 0; i &lt; 2000; i++) {
          NSLog(@"%i",i);
          sqlite3_exec(db,"UPDATE mytable SET test=''", 0, 0, 0);
      }
  }
  
  - (sqlite3 *)getNewDb {
      sqlite3 *newDb = nil;
      if (sqlite3_open([[self getDbPath] UTF8String], &amp;newDb) == SQLITE_OK) {
          sqlite3_busy_timeout(newDb, 1000);
      } else {
          sqlite3_close(newDb);
      }
      return newDb;
  }
  </code></pre>