Quite strange that Prepare() makes a difference. The system.data.sqlite documentation states that Prepare() does nothing, and a code inspection of the system.data.sqlite source shows that it does nothing but check that the command hasn't been disposed, and that the connection is still valid (which is done at the start of almost every method in the class, and therefore again when you call ExecuteNonQuery.)
> On 27 Sep 2017, at 5:07 am, Roberts, Barry (FINTL) <b.robe...@fugro.com> > wrote: > > Hi, > > I am aware of the sqlite FAQ, and especially the comments regarding file > systems. We only ever use NTFS file system, and never have any FAT or FAT32 > file systems. Given that the old 1.0.80.0 driver is totally stable, and the > newer one is not, on the same exact hardware, I do not think the issue is > hardware related. > > I enforce that the writer to a specific database can only run 1 at a time, > this is done via the C# Interlocked mechanism which has always proved to be > very robust. There is a separate logger object instance per database, and > within each of those the writing is controlled with the interlock. This > allows the application to buffer for longer if the writing to disk encounters > a short term slow down. > > As per Clemens suggestion I could replace the use of the connection pool and > hold a connection open per database. The code can be structured to do that, > because object instances are not shared between database logger instances. > However I would have preferred to use the connection pool, because that > allows me to open late/close early on my connection usage, which is generally > better for maintenance and how the production code is currently strutured. I > could restructure the code, but that is not really desirable in production > code, simply due to an ADO.NET driver update. > > I have been running my test application for days now without issues. This is > configured with pooling on, statement preparation off. If I enable statement > preparation the system will fail after an hour or two. So it looks like our > production code needs to run the same, and remove the IDbCommand.Prepare() > calls. The old driver works well but has lots of lock contention, the new one > resolves that issue, but is not as stable in some configurations. It is a > trade-off, I need the new driver to improve performance, but will trade off > the command prepare calls, to enable driver stability. > > Kind Regards, > Barry Roberts. > b.robe...@fugro.com<mailto:b.robe...@fugro.com> | www.fugro.com > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
