Re: [sqlite] Database locking problems

On Mon, 21 Jan 2019 18:12:25 -0500 Richard Damon wrote: > Some operations can be order of microseconds if the data resides in > cache, Thank you, I hadn't considered that. I was thinking that seek times on "spinning rust" -- which is the only economically feasible technology for large database

Re: [sqlite] Database locking problems

On 1/21/19 4:38 PM, James K. Lowden wrote: > On Sun, 20 Jan 2019 17:01:25 -0700 > "Keith Medcalf" wrote: > >> SQLite3 however has latencies on the order of microseconds > Is that really true? Are there machines for which SQLite's throughput > can be measured in transactions per millisecond? >

Re: [sqlite] Database locking problems

On Sun, 20 Jan 2019 21:51:19 + wrote: > > insert into t > > select :pid, nrows, N > > from (select 1 as N union select 2 union select 3) as cardinals > > cross join (select :pid, count(*) as nrows from t) as how_many; > > > > By using a single SQL statement, you avoid a user-defined > > trans

Re: [sqlite] Database locking problems

On Sun, 20 Jan 2019 17:01:25 -0700 "Keith Medcalf" wrote: > SQLite3 however has latencies on the order of microseconds Is that really true? Are there machines for which SQLite's throughput can be measured in transactions per millisecond? I think you're referring to the latency of the functi

Re: [sqlite] Database locking problems

On Mon, 21 Jan 2019 at 07:21, Keith Medcalf wrote: > In DELETE or TRUNCATE (that is, all modes except WAL) a READ transaction > in progress blocks a WRITE transaction and a WRITE transaction in progress > blocks all other attempts to commence a transaction of any type on any > other connection. >

Re: [sqlite] Database locking problems

On Mon, 21 Jan 2019 at 15:46, wrote: > For the moment, the solution that is working for me is to disable syncing > with PRAGMA synchronous = OFF. This is acceptable in this particular > application because a power failure or OS crash will necessitate restarting > the data gathering process anywa

Re: [sqlite] Database locking problems

On 21/01/2019 18:46, andrew.g...@l3t.com wrote: Okay, I put in some instrumentation. Basically I print out all database queries as they happen, along with all calls to sqlite3OsLock() and sqlite3OsUnlock() (including their lockType argument and any abnormal return code). Also I print out how

Re: [sqlite] Database locking problems

Okay, I put in some instrumentation. Basically I print out all database queries as they happen, along with all calls to sqlite3OsLock() and sqlite3OsUnlock() (including their lockType argument and any abnormal return code). Also I print out how many times sqlite3InvokeBusyHandler() has to call

Re: [sqlite] Database locking problems

Keith Medcalf wrote: > Andy Goth wrote: >> There are two possibilities: >> >> 1. Transactions do work, but I'm misusing them and must learn how to be >> more careful. In this case, I will update documentation to properly >> explain their use to others. >> >> 2. Transactions don't work, at least no

Re: [sqlite] Database locking problems

On Sunday, 20 January, 2019 17:19, Simon Slavin wrote: >> If the issue is the dead lock, you get similar issues with all >> DBMSes. > I'm not perfectly sure of my logic here, but OP posted elsewhere that > replacing BEGIN with BEGIN IMMEDIATE cures the problem. I think this > indicates that hi

Re: [sqlite] Database locking problems

On 20 Jan 2019, at 11:54pm, Richard Damon wrote: > If the issue is the dead lock, you get similar issues with all DBMSes. I'm not perfectly sure of my logic here, but OP posted elsewhere that replacing BEGIN with BEGIN IMMEDIATE cures the problem. I think this indicates that his problem isn't

Re: [sqlite] Database locking problems

On Sunday, 20 January, 2019 16:32, Thomas Kurz wrote: >Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue? >I guess the keypoint is that no matter where the query comes from, >the database files are always under control of the same process which >then can take care of the corre

Re: [sqlite] Database locking problems

On 1/20/19 6:32 PM, Thomas Kurz wrote: > Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue? I guess > the keypoint is that no matter where the query comes from, the database files > are always under control of the same process which then can take care of the > correct order in

Re: [sqlite] Database locking problems

said, just for curiosity - no offense against SQlite because I can well understand the problem that SQlite has to deal with :-) - Original Message - From: Richard Damon To: sqlite-users@mailinglists.sqlite.org Sent: Monday, January 21, 2019, 00:21:48 Subject: [sqlite] Database locking

Re: [sqlite] Database locking problems

On 1/20/19 4:51 PM, andrew.g...@l3t.com wrote: > James K. Lowden wrote: >> On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp wrote: >>> The busy timeout is not working because you start out your transaction >>> using a read operation - the first SELECT statement - which gets a read >>> lock. Later

Re: [sqlite] Database locking problems

>There are two possibilities: >1. Transactions do work, but I'm misusing them and must learn how to >be more careful. In this case, I will update documentation to >properly explain their use to others. >2. Transactions don't work, at least not for my task. In this case, >I will do my best to i

Re: [sqlite] Database locking problems

James K. Lowden wrote: > On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp wrote: >> The busy timeout is not working because you start out your transaction >> using a read operation - the first SELECT statement - which gets a read >> lock. Later when you go to COMMIT, this has to elevate to a write

Re: [sqlite] Database locking problems

On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp wrote: > The busy timeout is not working because you start out your transaction > using a read operation - the first SELECT statement - which gets a > read lock. Later when you go to COMMIT, this has to elevate to a > write lock. But SQLite sees t

Re: [sqlite] Database locking problems

On 19 Jan 2019, at 4:43pm, wrote: > I'm thinking one possible sequence might be: It's the right suggestion, but you're still in diagnostic stage, so this is a faster test: 1) Make sure your code still sets timeouts on every connection. 5000 ms should be fine. 2) Replace every BEGIN with BEG

Re: [sqlite] Database locking problems

> Not sure if this is the problem you are running into, but it might be > because SQLite is detecting a potential deadlock. I think that's exactly what's going on. SQLite is not bothering with the busy handler since it already knows no amount of waiting will solve the problem. > When you just u

Re: [sqlite] Database locking problems

Scott Vallery wrote: > have you considered 'threading'? This sounds like something that you need to > create a thread to do. Creating a thread should then allow you to set > parameters to wait until one process completes and switch back and forth. Yes, threads would make cooperation easier. SQLit

Re: [sqlite] Database locking problems

On 2019/01/19 5:20 PM, Richard Hipp wrote: On 1/19/19, R Smith wrote: "merged at some stage" and "merged into the next release" are different things. The latter will likely not happen, but I cannot say about the former, just yet. Thank you - I believe my original mail did say "... a next

Re: [sqlite] Database locking problems

On 1/19/19, R Smith wrote: > I make scripts/software for SQLite used by many, I > can dictate the lowest version number for a script, but usually they use > whatever engine is downloadable from the site (sqlite3.exe, sqlite3.dll > etc.), so if the very useful BEGIN CONCURRENT won't be merged at s

Re: [sqlite] Database locking problems

On 2019/01/19 4:55 PM, Richard Hipp wrote: On 1/19/19, R Smith wrote: Hi RIchard, any chance this BEGIN CONCURRENT branch will make it into a next SQLite standard release? You can always pull it from the branch. The branch will not go away. Thank you kindly, this much is known. Allow me t

Re: [sqlite] Database locking problems

On 1/19/19, R Smith wrote: > Hi RIchard, any chance this BEGIN CONCURRENT branch will make it into a > next SQLite standard release? You can always pull it from the branch. The branch will not go away. -- D. Richard Hipp d...@sqlite.org ___ sqlite-us

Re: [sqlite] Database locking problems

Hi RIchard, any chance this BEGIN CONCURRENT branch will make it into a next SQLite standard release? On 2019/01/19 3:07 PM, Richard Hipp wrote: If you are daring, you can also try building from the begin-concurrent branch (https://www.sqlite.org/src/timeline?r=begin-concurrent) and using "BEGI

Re: [sqlite] Database locking problems

Not sure if this is the problem you are running into, but it might be because SQLite is detecting a potential deadlock. When you just use BEGIN, SQLite will acquire locks lazily - the first read operation will acquire a shared lock, and the first write operation will acquire a reserved lock. Any nu

Re: [sqlite] Database locking problems

Thanks for the detailed trouble analysis! Short answer: Probably you want to do "BEGIN IMMEDIATE" rather than just "BEGIN" to start your transaction. The busy timeout is not working because you start out your transaction using a read operation - the first SELECT statement - which gets a read lo

Re: [sqlite] Database locking problems

I'm a newbie on the block and this is my first  post, but have you considered 'threading'? This sounds like something that you need to create a thread to do. Creating a thread should then allow you to set parameters to wait until one process completes and switch back and forth. I've done this in

[sqlite] Database locking problems

I am having significant difficulty getting two process to not slam into each other when accessing the same database. The behavior I want is for one process to wait while the database is busy being written to by the other, but instead I immediately get SQLITE_BUSY. This is happening to me when

Re: [sqlite] Database locking with online backup API

On Wed, 9 May 2018 at 11:13 Simon Slavin wrote: > On 8 May 2018, at 1:12pm, Simon Slavin wrote: > > > You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN > IMMEDIATE depending on which you want. Then do all the backup stuff, then > COMMIT or ROLLBACK without having changed anythi

Re: [sqlite] Database locking with online backup API

On 8 May 2018, at 1:12pm, Simon Slavin wrote: > You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN IMMEDIATE > depending on which you want. Then do all the backup stuff, then COMMIT or > ROLLBACK without having changed anything. On 9 May 2018, at 1:50am, Donald Shepherd wrote

Re: [sqlite] Database locking with online backup API

On Tue, 8 May 2018 at 22:12 Simon Slavin wrote: > On 8 May 2018, at 10:56am, R Smith wrote: > > > Thank you for clarifying - but it is still my understanding that the DB > is not locked (if only in WAL mode), so the backup API, even with -1, > either must ignore changes, or restart. My proposed

Re: [sqlite] Database locking with online backup API

On 8 May 2018, at 10:56am, R Smith wrote: > Thank you for clarifying - but it is still my understanding that the DB is > not locked (if only in WAL mode), so the backup API, even with -1, either > must ignore changes, or restart. My proposed flag is to lock rather than > restart or ignore. Yo

Re: [sqlite] Database locking with online backup API

On 2018/05/08 11:32 AM, Rowan Worth wrote: On 8 May 2018 at 17:22, R Smith wrote: On 2018/05/08 9:37 AM, Donald Shepherd wrote: It would actually be real nice if the backup API had a parameter or flag like "sqlite3_lockduringbackup". Not quite right. sqlite3_backup_step accepts a number of

Re: [sqlite] Database locking with online backup API

On 8 May 2018 at 17:22, R Smith wrote: > On 2018/05/08 9:37 AM, Donald Shepherd wrote: > >> I've long assumed that when using the online backup API on a SQLite >> database, other processes will not be able to write to the source database >> for the duration of the sqlite3_backup_step call. Howev

Re: [sqlite] Database locking with online backup API

On Tue, 8 May 2018 at 19:23 R Smith wrote: > > On 2018/05/08 9:37 AM, Donald Shepherd wrote: > > I've long assumed that when using the online backup API on a SQLite > > database, other processes will not be able to write to the source > database > > for the duration of the sqlite3_backup_step cal

Re: [sqlite] Database locking with online backup API

On 2018/05/08 9:37 AM, Donald Shepherd wrote: I've long assumed that when using the online backup API on a SQLite database, other processes will not be able to write to the source database for the duration of the sqlite3_backup_step call. However under some testing I've been performing, I've fo

[sqlite] Database locking with online backup API

I've long assumed that when using the online backup API on a SQLite database, other processes will not be able to write to the source database for the duration of the sqlite3_backup_step call. However under some testing I've been performing, I've found that this doesn't appear to be the case. Ins

Re: [sqlite] Database locking Error

On 30 Aug 2013, at 5:36am, techi eth wrote: > Thanks for suggestion. I will take it forward. > > locking will happen with two simultaneous processes accessing for read also? I don't think it can happen if they're both reading. But a process which is reading could block a process which is wri

Re: [sqlite] Database locking Error

Thanks for suggestion. I will take it forward. locking will happen with two simultaneous processes accessing for read also? Thanks On 8/29/13, Simon Slavin wrote: > > On 29 Aug 2013, at 6:48am, techi eth wrote: > >> I am checking for all the function.As of now i am not using sqlite3 time >> o

Re: [sqlite] Database locking Error

On 29 Aug 2013, at 6:48am, techi eth wrote: > I am checking for all the function.As of now i am not using sqlite3 time > out but testing application will take decision accordingly to recall the > operation based on type of error. If you have two simultaneous processes accessing the same databas

Re: [sqlite] Database locking Error

I am checking for all the function.As of now i am not using sqlite3 time out but testing application will take decision accordingly to recall the operation based on type of error. On Wed, Aug 28, 2013 at 3:03 PM, Simon Slavin wrote: > > On 28 Aug 2013, at 9:24am, techi eth wrote: > > > Yes, i

Re: [sqlite] Database locking Error

On 28 Aug 2013, at 9:24am, techi eth wrote: > Yes, i am checking the return code. Just for the function that gives the error, or for the calls before that too ? And are you setting a timeout ? If so, for how long ? Simon. ___ sqlite-users mailing l

Re: [sqlite] Database locking Error

Yes, i am checking the return code. On Tue, Aug 27, 2013 at 5:09 PM, Simon Slavin wrote: > > On 27 Aug 2013, at 5:15am, techi eth wrote: > > > For read operation i am doing _prepare(), _step(), _finalize(). > > For all other operation i am doing _exec(). > > > > Do you see any issue ? > > Noth

Re: [sqlite] Database locking Error

On 27 Aug 2013, at 5:15am, techi eth wrote: > For read operation i am doing _prepare(), _step(), _finalize(). > For all other operation i am doing _exec(). > > Do you see any issue ? Nothing obvious from what I already know apart from the fact that you don't mention setting a timeout:

Re: [sqlite] Database locking Error

For read operation i am doing _prepare(), _step(), _finalize(). For all other operation i am doing _exec(). Do you see any issue ? Cheers - Techi On Mon, Aug 26, 2013 at 9:22 PM, Simon Slavin wrote: > > On 26 Aug 2013, at 9:02am, techi eth wrote: > > > Fun_Read() on TblTest** > > /* Do the p

Re: [sqlite] Database locking Error

On 26 Aug 2013, at 9:02am, techi eth wrote: > Fun_Read() on TblTest** > /* Do the periodic read operation by using db handler return from above*/ How is your read done ? Do you use _exec() like you do for the PRAGMA, or _prepare(), _step(), _finalize(), or _query() or something else ? Simon.

Re: [sqlite] Database locking Error

> >You only have to do this once, e.g. at database creation. > >Journal mode WAL is a persistent property of the database file. > >Every connection will respect it. > All PRAGMA Option is attached to database as a persistent property? No, only the ones which are persistent. Journal_mode is pers

Re: [sqlite] Database locking Error

Oppps. Apologies for my mistake. My test is working correctly now. Thanks a lot. >You only have to do this once, e.g. at database creation. >Journal mode WAL is a persistent property of the database file. >Every connection will respect it. All PRAGMA Option is attached to database as a pers

Re: [sqlite] Database locking Error

On Mon, 26 Aug 2013 13:32:42 +0530, techi eth wrote: >PRAGMA jouranl_mode = WAL That's misspelled, both in your text and in your code. Try: PRAGMA journal_mode=WAL; You only have to do this once, e.g. at database creation. Journal mode WAL is a persistent property of the database file. Every

Re: [sqlite] Database locking Error

Thanks. I tried using PRAGMA jouranl_mode = WAL but still it is getting failed for some time. Find below more details. Process 1: Fun_Open() sqlite3_open(DATABASE, &dbUpdate); sqlite3_exec(dbUpdate, " PRAGMA jouranl_mode = WAL", NULL, NULL, &sErrMsg); Fun_Update() on TblTest /* Do the perio

Re: [sqlite] Database locking Error

On 08/26/2013 01:37 PM, techi eth wrote: Hi, I come across database file locking error while trying below case with Update. Case: One process is updating a Colum periodically & other process is selecting same Colum or other colum in table periodically for read. What is the best way to handle so

[sqlite] Database locking Error

Hi, I come across database file locking error while trying below case with Update. Case: One process is updating a Colum periodically & other process is selecting same Colum or other colum in table periodically for read. What is the best way to handle so that Update will not get locking error? C

Re: [sqlite] Database locking without locking database file

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 14/08/13 06:06, Ralf Ramsauer wrote: > Neither cifs. I worked on a CIFS server (visionfs)[1]. They are a convoluted complicated mess. During the OLE2 era, Microsoft's apps abused locking as a means of inter-process communication. It got very com

Re: [sqlite] Database locking without locking database file

On 14.08.2013 18:36, Stephan Beal wrote: > On Wed, Aug 14, 2013 at 3:06 PM, Ralf Ramsauer < > ralf+sql...@ramses-pyramidenbau.de> wrote: > >> Why is it proposed not to use NFS? Why is it so risky? I can hardly >> believe that NFS locking is that broken... >> > Few of us can believe it, but many of

Re: [sqlite] Database locking without locking database file

On 14 Aug 2013, at 5:36pm, Stephan Beal wrote: > On Wed, Aug 14, 2013 at 3:06 PM, Ralf Ramsauer < > ralf+sql...@ramses-pyramidenbau.de> wrote: > >> Why is it proposed not to use NFS? Why is it so risky? I can hardly >> believe that NFS locking is that broken... > > Few of us can believe it, bu

Re: [sqlite] Database locking without locking database file

On Wed, Aug 14, 2013 at 3:06 PM, Ralf Ramsauer < ralf+sql...@ramses-pyramidenbau.de> wrote: > Why is it proposed not to use NFS? Why is it so risky? I can hardly > believe that NFS locking is that broken... > Few of us can believe it, but many of us have had horrible experiences (regardless of sq

Re: [sqlite] Database locking without locking database file

Hi, thanks a lot for your answer. On 14.08.2013 13:52, Richard Hipp wrote >> Now my Question: >> According to [4], it seems that NFS also has problems with sharing >> locks. So why does Sqlite make use of file locks instead of writing >> those locks (including a timestamp for expiration) inside t

Re: [sqlite] Database locking without locking database file

On Tue, Aug 13, 2013 at 11:35 AM, Ralf Ramsauer wrote: > Now my Question: > According to [4], it seems that NFS also has problems with sharing > locks. So why does Sqlite make use of file locks instead of writing > those locks (including a timestamp for expiration) inside the database > file or b

[sqlite] Database locking without locking database file

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, the FAQ of qemu describes Sqlite to be threadsafe [1]. The "threadsafeness" can e.g. be chosen at compiletime [2]. If several Sqlite instances try to access the same database file on a system, the database file gets locked via simple file locks

[sqlite] database locking

Hi List I have a python script that runs 3 instances simultaneously on a quadcore x86 system. I'm not using the multiprocessing libraries. The programs are just run in parallel. They all access (for reading only) a single sqlite3 database. I'm setting the following pragma commands: offset_db.

Re: [sqlite] Database locking issue

Hi, now I have tried the actual version 3.6.3 as of sqlite- amalgamation-3.6.3.tgz, but still get database locks I cannot explain. Brief information: I use two database files. One (lbDMF.db3) for system informations such as what my application should show in forms of a database (Template Desi

Re: [sqlite] Database locking issue

Am 19.09.2008 um 17:03 schrieb Ken: > Try it with the latest full build say version 3.6.2 and see what > happens instead of a "patched" > I'll give that a try. Could the current code be compiled with Open Watcom as A DLL ? (I haven't seen these __declspec(dllexport) and the opposite stuff in

Re: [sqlite] Database locking issue

Try it with the latest full build say version 3.6.2 and see what happens instead of a "patched" --- On Fri, 9/19/08, Lothar Behrens <[EMAIL PROTECTED]> wrote: From: Lothar Behrens <[EMAIL PROTECTED]> Subject: [sqlite] Database locking issue To: "General Discussio

[sqlite] Database locking issue

Hi, I am using Version 3.5.2 of Sqlite with the changes of the following CVS checkin numbers: 4543 and 5243 to get the sqlite3_sql and sqlite3_next_stmt functions into my API. I have patched my files due to the need to figuring out what statements were unfinalized. But now I get 'database

[sqlite] Database locking and blocking

When a process gets SQLITE_BUSY, it needs to wait for the blocking operation to finish before it can successfully retry (whether it's just retrying a COMMIT or redoing a whole transaction). How do people typically handle this? If a different process is the one holding the lock, then you need to s

Re: [sqlite] Database Locking

> > In a message dated 6/4/2004 11:53:05 AM Eastern Daylight Time, > [EMAIL PROTECTED] writes: > > I'm using SQLite on a Windows XP system with 1GB of RAM. I'm writing a > server which spawns multiple threads for it's connections. It is > querying and writing to an SQLite database within each th

Re: [sqlite] Database Locking

I'm using SQLite on a Windows XP system with 1GB of RAM. I'm writing a server which spawns multiple threads for it's connections. It is querying and writing to an SQLite database within each thread. Whenever a query or insert/update statement is issued, the SQLite database is opened, quer

Re: [sqlite] Database Locking

In a message dated 6/4/2004 11:53:05 AM Eastern Daylight Time, [EMAIL PROTECTED] writes: I'm using SQLite on a Windows XP system with 1GB of RAM. I'm writing a server which spawns multiple threads for it's connections. It is querying and writing to an SQLite database within each thread. W

[sqlite] Database Locking

I'm using SQLite on a Windows XP system with 1GB of RAM. I'm writing a server which spawns multiple threads for it's connections. It is querying and writing to an SQLite database within each thread. Whenever a query or insert/update statement is issued, the SQLite database is opened, queried/wr