Ray, Using multiple threads you will have locking contention on the database. Only one thread is allowed to write at a time. If you need concurrent writing then create multiple databases or maybe look into a different DB platform like mysql, postgress or oracle.
--- On Wed, 7/8/09, Rizzuto, Raymond <raymond.rizz...@sig.com> wrote: > From: Rizzuto, Raymond <raymond.rizz...@sig.com> > Subject: Re: [sqlite] multi-thread access to a db > To: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org> > Date: Wednesday, July 8, 2009, 3:28 PM > If I remove the > locking_mode=exclusive, I don't get those errors. > > I'd appreciate any advice on how I can get the best > performance using multiple threads in my application, given > that: > > > 1. I need maximum performance. That is also > why I need multiple threads > 2. All threads need to write to the same db > 3. No other application needs access to the db > 4. I don't care about durability, just fast insert > times since reads are much less frequent. > > Ray > > ________________________________ > From: Rizzuto, Raymond > Sent: Wednesday, July 08, 2009 3:27 PM > To: 'sqlite-users@sqlite.org' > Subject: multi-thread access to a db > > I have an application where I have 7 threads. > Each thread opens its own db connection object, but the > connections are to the same db. I am seeing sporadic > insert failures when a thread attempts to insert into the > db. sqlite3_errmsg returns this message: > > database is locked > > I am using sqlite3 version 3.6.1. I use the following > two pragmas to get the best insert performance (the db is > used exclusively by this application, and I don't need to > have the DB recover after an os crash or power fail): > > sqlite3_exec(result->db, > "pragma synchronous=off;", 0, 0, &zErrMsg); > sqlite3_exec(result->db, > "pragma locking_mode=exclusive;", 0, 0, &zErrMsg); > > I am using threading mode "multi-thread". > > Does mode=exclusive mean that the first thread that opens > and writes to the db locks out all other threads? > > Ray > > > ________________________________ > Ray Rizzuto > raymond.rizz...@sig.com > Susquehanna International Group > (610)747-2336 (W) > (215)776-3780 (C) > > > > ________________________________ > IMPORTANT: The information contained in this email and/or > its attachments is confidential. If you are not the intended > recipient, please notify the sender immediately by reply and > immediately delete this message and all its attachments. Any > review, use, reproduction, disclosure or dissemination of > this message or any attachment by an unintended recipient is > strictly prohibited. Neither this message nor any attachment > is intended as or should be construed as an offer, > solicitation or recommendation to buy or sell any security > or other financial instrument. Neither the sender, his or > her employer nor any of their respective affiliates makes > any warranties as to the completeness or accuracy of any of > the information contained herein or that this message or any > of its attachments is free of viruses. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
