Hi, I just came across a method provided by SQLite, called the WAL - Write Ahead Logging.
Anybody has any Good-Bad experiences related to using WAL? Can it be safely used with a heavily threaded application with very high rate of Insert Operations and simultaneously Select Operations also. Thanks & Regards, Sachin Gupta -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, December 29, 2010 12:01 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Cancel execution of a executing Query On 29 Dec 2010, at 5:17am, Sachin Gupta wrote: > I thought so too. But the application that we are working on is kind of > heavily multi-threaded and needs to be thread safe. SQLite is itself thread safe, as long as you don't use the PRAGMAs to defeat all the safety precautions. It might be worth reading http://www.sqlite.org/threadsafe.html and using http://www.sqlite.org/c3ref/threadsafe.html if your code will be run on any platforms you don't control. While there're probably bugs in SQLite that will cause it to hang, the writers would like to know about them and kill them, so please post if you find one. I don't see any entry in the bug database for one right now. > When we "Begin Transaction", does Sqlite return any kind of unique handle or > ID which can be used to poll a list of open transactions and kill or cancel > the transaction if required. I did not find it and I suppose that it does not > also. > > For e.g. > Suppose one thread opens a transaction and does some operations. > Simultaneously another and many other threads open up another transaction. > Now for some reason, the first transaction query hangs, and does not complete > execution. This would cause problems! So to come out of this, we would need a > unique transaction id to kill it. Is there a way to achieve this or is there > a better way to do this? Killing the hung transaction is probably not the right way to recover from this situation. There are a number of things that might cause the transaction to hang, but almost all of them are related to hardware failure. In other words, if thread B kills thread A's transaction so it can execute a transaction of its own, thread B's transaction is probably going to hang in the same way and for the same reason. So the best thing to do is to not do the killing in the first place, and let SQLite handle the problem its own way. The simplest is to look for results like SQLITE_BUSY and SQLITE_LOCKED (see section 1.4 of http://www.sqlite.org/c_interface.html ) which might result if a lock fails for too long. A more sophisticated way is to use the unlock_notify API: http://www.sqlite.org/unlock_notify.html I don't understand all of that, but from experience with other systems, unless you're in a situation where it's absolutely vital to cope with every possible source of error (i.e. you're using SQLite to control a nuclear power-plant or in a processor embedded into a missile) this API is of limited use once your application is released to customers. As the page describes, even without using that API SQLite handles hangs well. The correct way to treat a thread hanging due to an underlying hardware problem is, of course, to let your application hang. This tells your user that their hardware is not to be trusted, and lets them start putting the problem right instead of running on hardware which is about to fail. Simon. _______________________________________________ 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
