I had closed the connection after the COMMIT, but it works great if I don't. Thank you for that! I like the kitchen analogy, but it's more like 'Form an orderly queue for the electric citrus juicer' instead of all crowding round it and trying to juice 6 oranges and 4 lemons at once. In the end, 'one-at-a-time' is quicker than 'all-at-once'. I plan to use this to lock down the database while a user interrogates the database for the next available Invoice Number (which must be unique), then to release it when that is done. I've already had to cope with two separate invoices with the same number. Oooops.
Thanks for your help! Jonathan On Wed, 24 Apr 2013 11:19:32 +0200 "J Trahair" <[email protected]> wrote: > Thanks for the replies so far. > > I've been trying to get the database to lock - I am using a program I developed which runs on 2 of my computers but connected to the same SQLite database: > > mstrSQL = "PRAGMA locking_mode = EXCLUSIVE;BEGIN EXCLUSIVE;UPDATE Utilities SET OwnersName = '" & Date.Now & "' WHERE RecNo = 1;COMMIT;" I found, by bad experiences, that it's better to have a program/process that owns the database and others send petitions to it, something like a client-server, than have an always growing number of process/programs that works with the same db file. If some of them are remote process, its even worse. You can think in a kitchen were lot of cookers work on one meal, using one knive, one fire, one pot, one... sooner or later the chaos arrives and the meal is carbonized. > > On running this code (VB.Net), the record updates successfully, the message 'Done!" displays, no error message - but the same program on another computer pointing to the same database across the network is still able to open the database and run the program. > Do you close the db connection after the commit? Or reset locking mode to normal? See recent posts about locking db files using nfs. > I have missed something - but what is it? Thanks for your help. > > Jonathan > > > On 17 April 2013 14:39, J Trahair <[email protected]> wrote: > > Hi everyone > > > > Can my application lock the database for its own exclusive use at the beginning of a series of INSERTs UPDATEs and SELECTs, then unlock it again afterwards? I've read about the five locking states (unlocked, shared, etc.) but I assume the dll handles all that at INSERT or UPDATE time. I'm looking for a way to ensure other users aren't allowed to write to the database during this quarter-second period. > > http://www.sqlite.org/lang_transaction.html > BEGIN EXCLUSIVE > > > > > Thanks in advance. > > > > Jonathan Trahair > > Regards, > Simon > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras <[email protected]> ----- No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3272 / Virus Database: 3162/6269 - Release Date: 04/23/13 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
