Re: [sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?

> Le 9 août 2019 à 17:58, Richard Hipp a écrit : > > On 8/9/19, David Raymond wrote: >> >> I'm pretty sure you missed the bit where he said that there was only a read, >> and no changes were made or requested. >> > > You are correct - I missed that part. In that case, it just drops the > SHA

Re: [sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?

On 8/9/19, David Raymond wrote: > > I'm pretty sure you missed the bit where he said that there was only a read, > and no changes were made or requested. > You are correct - I missed that part. In that case, it just drops the SHARED lock. -- D. Richard Hipp d...@sqlite.org _

Re: [sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?

>> A connection holds a SHARED lock. It did start a DEFERRED transaction then >> read something. Now it executes COMMIT. >You are one of the rollback journaling modes, not WAL mode, right? >Different rules apply for WAL mode. > >In rollback mode, the connection automatically promotes to EXCLUSIVE

Re: [sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?

On 8/9/19, Olivier Mascia wrote: > Hello, > > I generally understand the locking states of SQLite well enough, I think. > Though this specific case below caught me off guard. I couldn't give an > immediate intelligible answer to my coworker. Nor could I find the answer > on the website, or I'm b

Re: [sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?

On 8/9/2019 11:00 AM, Olivier Mascia wrote: A connection holds a SHARED lock. It did start a DEFERRED transaction then read something. Now it executes COMMIT. Will the lock be upgraded from SHARED to EXCLUSIVE for the very short duration of the COMMIT (the connection made no writing, as evide

[sqlite] COMMIT, what locking behaviour when current lock is merely SHARED?

Hello, I generally understand the locking states of SQLite well enough, I think. Though this specific case below caught me off guard. I couldn't give an immediate intelligible answer to my coworker. Nor could I find the answer on the website, or I'm blind today. A connection holds a SHARED

Re: [sqlite] Commit and rollback behaviour during power loss

There is one degenerate case, which has been discussed a few times on this list. With PRAGMA journal_mode=DELETE (the default), the atomic signal that marks a transaction being committed is the deletion of the rollback journal. Deleting a file is a directory level operation, which means there are t

Re: [sqlite] Commit and rollback behaviour during power loss

> If Commit returns with SQLITE_OK, then YES, it is handed off to the disk > with some caveats, namely: > > A - We are assuming the Python sqlite3 wrapper you use doesn't do > obfuscation of any sort and directly calls the sqlite3 API and returns the > direct result from those calls. If you haven't

Re: [sqlite] Commit and rollback behaviour during power loss

On 2017/11/24 10:47 AM, Blagovest Buyukliev wrote: Let's say we have the following Python code: import sqlite3 conn = sqlite3.connect('mydb.db') c = conn.cursor() c.execute("INSERT INTO ...") conn.commit() c.execute("INSERT INTO ...") conn.commit() Can it be assumed that after conn.commit() h

[sqlite] Commit and rollback behaviour during power loss

Let's say we have the following Python code: import sqlite3 conn = sqlite3.connect('mydb.db') c = conn.cursor() c.execute("INSERT INTO ...") conn.commit() c.execute("INSERT INTO ...") conn.commit() Can it be assumed that after conn.commit() has returned, fsync() has been called on the file and (a

Re: [sqlite] Commit ad601c7962 degrade performance in a client program

I loved this remark posted in the bugzilla chain: "(... Fossil? where do people find these version control systems?)" On Thu, Jan 5, 2017 at 2:09 AM, Richard Hipp wrote: > On 1/3/17, Jianxun Zhang wrote: >> I am working in Yocto project. We have a fake-root program “pseudo” that >> uses sqlite

Re: [sqlite] Commit ad601c7962 degrade performance in a client program

On 1/3/17, Jianxun Zhang wrote: > I am working in Yocto project. We have a fake-root program “pseudo” that > uses sqlite3 at a step when building Yocto images. We found a 2% increase of > the whole build time, and my bisecting shows the ad601c7962 in sqlite3 is > the root cause. That change was b

Re: [sqlite] Commit ad601c7962 degrade performance in a client program

On Tue, 2017-01-03 at 17:49 -0800, Jianxun Zhang wrote: > I am working in Yocto project. We have a fake-root program “pseudo” > that uses sqlite3 at a step when building Yocto images. We found a 2% > increase of the whole build time, and my bisecting shows the > ad601c7962 in sqlite3 is the root ca

[sqlite] Commit ad601c7962 degrade performance in a client program

I am working in Yocto project. We have a fake-root program “pseudo” that uses sqlite3 at a step when building Yocto images. We found a 2% increase of the whole build time, and my bisecting shows the ad601c7962 in sqlite3 is the root cause. Basically, the sqlite3 library is built first from a re

Re: [sqlite] COMMIT nested in SELECT returns unexpected

On 25 Dec 2014, at 3:17am, Keith Medcalf wrote: > Using a separate connection for the INSERT/COMMIT also will not work because > it will not be able to get a write lock while the select is running. Should some part of the library then be producing an error which can be trapped ? Would correc

Re: [sqlite] COMMIT nested in SELECT returns unexpected

Simon Slavin >Sent: Wednesday, 24 December, 2014 17:26 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] COMMIT nested in SELECT returns unexpected > > >On 24 Dec 2014, at 6:39pm, Jim Carroll wrote: > >> I actually tried this same idea yesterday, but it ma

Re: [sqlite] COMMIT nested in SELECT returns unexpected

On 24 Dec 2014, at 6:39pm, Jim Carroll wrote: > I actually tried this same idea yesterday, but it made no difference. Even > manually creating cursors and executing all statements through them yielded > the exact same problem. > > For simplicity, I kept the code sample short, but I've tried doz

Re: [sqlite] COMMIT nested in SELECT returns unexpected

> Date: Wed, 24 Dec 2014 14:53:47 + > From: Simon Slavin > Sorry, I may have just realised what's wrong. You are misusing your > python library. > See the documentation at > > > > You cannot always use .execute against the connection to the >

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

> Date: Wed, 24 Dec 2014 15:47:41 +0200 > From: RSmith > > In the meantime, could you kindly post the actual connector or SQLite > interface used and which version of it (both the > connector/interface and the SQLite versions) so that we can test fully. We are using sqlite3 version 3.7.13 The co

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

> On 24 Dec 2014, at 10:50am, Jim Carroll wrote: > > #!/usr/bin/env python > import sqlite3 as sq Sorry, I may have just realised what's wrong. You are misusing your python library. See the documentation at You cannot always use .execute aga

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

On 2014/12/24 12:50, Jim Carroll wrote: I understand that performing a SELECT and nested COMMIT on the same table is not supported in sqlite, but I would have expected a COMMIT on a separate table would not be a problem. Some test code in python however reveals that performing the COMMIT disrup

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

On 24 Dec 2014, at 10:50am, Jim Carroll wrote: > I understand that performing a SELECT and nested COMMIT on the same table is > not supported in sqlite, but I would have expected a COMMIT on a separate > table would not be a problem. Some test code in python however reveals that > performing th

[sqlite] COMMIT nested in SELECT returns unexpected results

I understand that performing a SELECT and nested COMMIT on the same table is not supported in sqlite, but I would have expected a COMMIT on a separate table would not be a problem. Some test code in python however reveals that performing the COMMIT disrupts the SELECT statement, and causes dupl

Re: [sqlite] COMMIT in SQLite

Thanks guys. Perhaps docs could be updated in the _prepare_v2 section, to mention the 'best practices' lifecycle of a statement in regards to commit On 9 October 2013 04:08, Stephan Beal wrote: > On Tue, Oct 8, 2013 at 9:58 PM, Petite Abeille >wrote: > > > > > On Oct 8, 2013, at 8:10 PM, Ste

Re: [sqlite] COMMIT in SQLite

On Tue, Oct 8, 2013 at 9:58 PM, Petite Abeille wrote: > > On Oct 8, 2013, at 8:10 PM, Stephan Beal wrote: > > > (link to the original post not included because the archives are only > > visible to list members): > > Hmm? > > http://news.gmane.org/gmane.comp.version-control.fossil-scm.user Sorry

Re: [sqlite] COMMIT in SQLite

On Oct 8, 2013, at 8:10 PM, Stephan Beal wrote: > (link to the original post not included because the archives are only > visible to list members): Hmm? http://news.gmane.org/gmane.comp.version-control.fossil-scm.user ___ sqlite-users mailing list s

Re: [sqlite] COMMIT in SQLite

On Tue, Oct 8, 2013 at 8:01 PM, Dan Kennedy wrote: > On 10/08/2013 07:39 PM, Clemens Ladisch wrote: > >> Paul Harris wrote: >> >>> Many years ago, Igor mentioned that you should always reset/finalize any >>> prepared statements before calling COMMIT. >>> >>> I am wondering, is this still true? >>

Re: [sqlite] COMMIT in SQLite

On 10/08/2013 07:39 PM, Clemens Ladisch wrote: Paul Harris wrote: Many years ago, Igor mentioned that you should always reset/finalize any prepared statements before calling COMMIT. I am wondering, is this still true? Yes. Do I have to reset before I commit? And where is the requirement wri

Re: [sqlite] COMMIT in SQLite

Paul Harris wrote: > Many years ago, Igor mentioned that you should always reset/finalize any > prepared statements before calling COMMIT. > > I am wondering, is this still true? Yes. There was a change in 3.7.11, but not with COMMIT itself: | * Pending statements no longer block ROLLBACK. Instea

Re: [sqlite] COMMIT in SQLite

Hello all, Many years ago, Igor mentioned that you should always reset/finalize any prepared statements before calling COMMIT. I am wondering, is this still true? I have prepared SELECTs and INSERTs which have been step()'d, but not reset. the SELECTs tend to be stepped until there is no more da

Re: [sqlite] COMMIT or ROLLBACK failure

Igor Korot wrote: > I need to check if the COMMIT is successful. > But what should I do if it fails? If the database is currently locked, you should try again later. (But this would be better handled with a busy handler.) If there is some I/O error that prevents you from writing, the COMMIT fail

Re: [sqlite] COMMIT or ROLLBACK failure

> On Thu, Sep 19, 2013 at 4:35 PM, Richard Hipp wrote: > > On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot > wrote: > > > Now, AFAIU, I need to check if the COMMIT is successful. > > > But what should I do if it fails? Do I just report the failure to > > > the user? > > > Do I need to call ROLL

Re: [sqlite] COMMIT or ROLLBACK failure

On Thu, Sep 19, 2013 at 4:35 PM, Richard Hipp wrote: > On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot wrote: > > > > > Now, AFAIU, I need to check if the COMMIT is successful. > > But what should I do if it fails? Do I just report the failure to the > user? > > Do I need to call ROLLBACK? And what

Re: [sqlite] COMMIT or ROLLBACK failure

On Thu, Sep 19, 2013 at 5:50 PM, Igor Korot wrote: > > Now, AFAIU, I need to check if the COMMIT is successful. > But what should I do if it fails? Do I just report the failure to the user? > Do I need to call ROLLBACK? And what if it will also fail? > And in "else" branch - do I check for "ROLLB

[sqlite] COMMIT or ROLLBACK failure

Hi, ALL, Consider following piece of code: int res = sqlite3_exec(..., "BEGIN"... ); if( res != SQLITE_OK ) { printf( "Error occured on begin transaction. Please try again." ); return; } // some operations on the database // if operations are successful sqlite3_exec( ..., "COMMIT

[sqlite] commit fails when journal_mode = memory

Hello, The next problem occurs since switching to sqlite 3.8; it was never observed in 3.7.17 or earlier. I have an application in which I insert a lot of data in a transaction, started by executing "begin transaction", after having executed "PRAGMA synchronous = 0" and "PRAGMA journal_mode = M

Re: [sqlite] sqlite Commit C API

On 21 May 2012, at 12:22pm, "Black, Michael (IS)" wrote: > I'll have to check the BIOS settings on my box and see if turning off write > caching makes more sense on that particular test. That won't do any harm, but you should be aware that your hardware lies. Some current hard disk drives d

Re: [sqlite] sqlite Commit C API

lf of Igor Tandetnik [itandet...@mvps.org] Sent: Sunday, May 20, 2012 8:47 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] sqlite Commit C API Black, Michael (IS) wrote: > Hmmm...our math is a bit different... > > A 1,000 RPM disk would take 1ms to spin around once No it woul

Re: [sqlite] sqlite Commit C API

On Sun, May 20, 2012 at 01:26:48PM +, Black, Michael (IS) scratched on the wall: > Hmmm...our math is a bit different... Yeah, your math is wrong... 8-) > A 1,000 RPM disk would take 1ms to spin around once A 1,000 RPS disk would, but not a 1,000 RPM disk. > I believe my original poi

Re: [sqlite] sqlite Commit C API

Black, Michael (IS) wrote: > Hmmm...our math is a bit different... > > A 1,000 RPM disk would take 1ms to spin around once No it wouldn't. > (there are 1000ms in a second, correct?) Yes, but RPM stands for a revolution-per-*minute*. You are off by a factor of 60. -- Igor Tandetnik _

Re: [sqlite] sqlite Commit C API

sqlite.org] on behalf of Jay A. Kreibich [j...@kreibi.ch] Sent: Sunday, May 20, 2012 7:53 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] sqlite Commit C API On Sun, May 20, 2012 at 12:04:33PM +, Black, Michael (IS) scratched on the wall: > Another more indirect

Re: [sqlite] sqlite Commit C API

On Sun, May 20, 2012 at 12:04:33PM +, Black, Michael (IS) scratched on the wall: > Another more indirect way to test is this utility: > > http://kerneltrap.org/mailarchive/linux-ext4/2009/3/22/5215824 > > Which...if your fsync doesn't work at all will return something really > close to zero

Re: [sqlite] sqlite Commit C API

If you can run perl on your ARM host try this utility to see if fsync() actually works -- this is a real end-to-end test that you pull the plug on and it will let you know if your disk file is where it's supposed to be and how many errors you had. http://brad.livejournal.com/2116715.html Ano

Re: [sqlite] sqlite Commit C API

> On Thu, May 17, 2012 at 7:04 PM, Rajesh Kumar wrote: > int fsync( int fd ) { return 0; } > > fsync will expect an Integer pointer right. But sqlite pointer is of type > sqlite3*. So how can fsync works on sqlite. What should I pass to fsync??? > > You don't need to call fsync(). Sqlite calls it

Re: [sqlite] sqlite Commit C API

On Thu, May 17, 2012 at 04:47:29PM +0100, Simon Slavin wrote: > > On 17 May 2012, at 4:34pm, Rajesh Kumar wrote: > > > No am not using any PRAGMAs. I just cross compiled sqlite source to > > ARM architecture. Can't I forcefully do commit on my transaction > > If you are correctly using _ope

Re: [sqlite] sqlite Commit C API

Subject: Re: [sqlite] sqlite Commit C API On 17 May 2012, at 4:49pm, Jay A. Kreibich wrote: > I wouldn't be all that shocked to find out an embedded system has an > fsync() call that looks like this: > > int fsync( int fd ) { return 0; } We are best software circle ! Our

Re: [sqlite] sqlite Commit C API

On 17 May 2012, at 5:04pm, Rajesh Kumar wrote: > int fsync( int fd ) { return 0; } > > fsync will expect an Integer pointer right. But sqlite pointer is of type > sqlite3*. So how can fsync works on sqlite. What should I pass to fsync??? Sorry Rajesh. It was a programmer's joke. Ignore that.

Re: [sqlite] sqlite Commit C API

int fsync( int fd ) { return 0; } fsync will expect an Integer pointer right. But sqlite pointer is of type sqlite3*. So how can fsync works on sqlite. What should I pass to fsync??? On Thu, May 17, 2012 at 9:19 PM, Jay A. Kreibich wrote: > int fsync( int fd ) { return 0; } -- * Thanks &

Re: [sqlite] sqlite Commit C API

On 17 May 2012, at 4:49pm, Jay A. Kreibich wrote: > I wouldn't be all that shocked to find out an embedded system has an > fsync() call that looks like this: > > int fsync( int fd ) { return 0; } We are best software circle ! Our programmers write many functions a day ! Our functions run

Re: [sqlite] sqlite Commit C API

On Thu, May 17, 2012 at 08:58:39PM +0530, Rajesh Kumar scratched on the wall: > Hi Richard, > > Thanks for the reply. fsync() is related to system call, which is > working fine. I believe you're missing Richard's point. I'm sure fsync() is returning a success error code. That doesn't m

Re: [sqlite] sqlite Commit C API

On 17 May 2012, at 4:34pm, Rajesh Kumar wrote: > No am not using any PRAGMAs. I just cross compiled sqlite source to > ARM architecture. Can't I forcefully do commit on my transaction If you are correctly using _open() and _close(), and haven't disabled synchrony with PRAGMAs or compilatio

Re: [sqlite] sqlite Commit C API

So if I use _exec() after sqlit3_exec() API, what ever the I did on database will be saved, right?? On Thu, May 17, 2012 at 9:03 PM, Simon Slavin wrote: > > On 17 May 2012, at 4:28pm, Rajesh Kumar wrote: > > >Thanks for the reply. fsync() is related to system call, which is > > working

Re: [sqlite] sqlite Commit C API

No am not using any PRAGMAs. I just cross compiled sqlite source to ARM architecture. Can't I forcefully do commit on my transaction On Thu, May 17, 2012 at 8:55 PM, Simon Slavin wrote: > > On 17 May 2012, at 4:18pm, Rajesh Kumar wrote: > > > Am using sqlite database on my ARM processor bas

Re: [sqlite] sqlite Commit C API

On 17 May 2012, at 4:28pm, Rajesh Kumar wrote: >Thanks for the reply. fsync() is related to system call, which is > working fine. But I am looking for a C API that can do the commit thing, > which is same as commit on command promt. The COMMIT; on a command prompt does the same thing as ex

Re: [sqlite] sqlite Commit C API

Hi Richard, Thanks for the reply. fsync() is related to system call, which is working fine. But I am looking for a C API that can do the commit thing, which is same as commit on command promt. On Thu, May 17, 2012 at 8:52 PM, Richard Hipp wrote: > On Thu, May 17, 2012 at 11:18 AM, Rajesh K

Re: [sqlite] sqlite Commit C API

On Thu, May 17, 2012 at 11:22:44AM -0400, Richard Hipp scratched on the wall: > On Thu, May 17, 2012 at 11:18 AM, Rajesh Kumar wrote: > > > Hi all, > > > >Am using sqlite database on my ARM processor based Embedded system . > > Whenever power failures or due to uneven shutdown of device, my d

Re: [sqlite] sqlite Commit C API

On 17 May 2012, at 4:18pm, Rajesh Kumar wrote: > Am using sqlite database on my ARM processor based Embedded system . > Whenever power failures or due to uneven shutdown of device, my database > is being corrupted which leading to data lost. Am using C API interface for > accessing database. Ar

Re: [sqlite] sqlite Commit C API

On Thu, May 17, 2012 at 11:18 AM, Rajesh Kumar wrote: > Hi all, > >Am using sqlite database on my ARM processor based Embedded system . > Whenever power failures or due to uneven shutdown of device, my database > is being corrupted which leading to data lost. That should never happen. It s

[sqlite] sqlite Commit C API

Hi all, Am using sqlite database on my ARM processor based Embedded system . Whenever power failures or due to uneven shutdown of device, my database is being corrupted which leading to data lost. Am using C API interface for accessing database. Is there any C API that I can use to commit my D

Re: [sqlite] Commit method not available

Thanks! - Original Message - From: "Joe Mistachkin" To: "'General Discussion of SQLite Database'" Sent: Sunday, February 12, 2012 1:40 PM Subject: Re: [sqlite] Commit method not available Kyle Cashion wrote: What am I missing? You'll want

Re: [sqlite] Commit method not available

Kyle Cashion wrote: > > What am I missing? > You'll want to capture the return value of the SQLiteConnection.BeginTransaction method (which is the actual transaction) and then use the Commit and/or Rollback methods of that [SQLiteTransaction] object. -- Joe Mistachkin __

[sqlite] Commit method not available

I'm using the drivers from sqlite-netFx40-setup-bundle-x86-2010-1.0.79.0.exe in VS2010, .NET 4. Connection.BeginTransaction is available as a method, but Connection.Commit is not, nor is Connection.Rollback. What am I missing? ___ sqlite-users mailing

Re: [sqlite] Commit from thread?

On 1/13/2011 11:49 AM, Black, Michael (IS) wrote: > I have a shared library being called by a transaction-oriented Java > system. I'm doing periodic commits (every 5 seconds) to speed up > processing. However, I want to commit even when not polled by the > Java system. There could be long period

[sqlite] Commit from thread?

I think the answer to this is "no" based on http://www.sqlite.org/faq.html#q6 But a confirmation or other solution would be nice... I have a shared library being called by a transaction-oriented Java system. I'm doing periodic commits (every 5 seconds) to speed up processing. However, I want

Re: [sqlite] commit writer when other transaction is active?

> Is it correct behavior of sqlite that COMMIT will not proceed unless > no other transactions are present in the system? No, SQLite never behaves like that. Probably you executed in the second thread some SELECT query (besides INSERT) and it wasn't finished yet by the time of COMMIT. In this situ

[sqlite] commit writer when other transaction is active?

Hi, I observe the following behavior: Two threads processing the same transactions on a simple employee table: employee(id,name,city,salary). THREAD 1 BEGIN TRANSACTION; insert into employees values (1025,'Bush','New York',111); COMMIT TRANSACTION; THREAD 2 BEGIN TRANSACTION

Re: [sqlite] commit transaction/savepoints on program crash

[New text /below/ any text you want to quote, please.] On 9 Aug 2010, at 9:09pm, Josh wrote: > Thanks for the answers, but I guess I should have made my question more > clear. I knew that you can commit every sql statement individually, then > the question would be, how can I roll them back? >

Re: [sqlite] commit transaction/savepoints on program crash

Thanks for the answers, but I guess I should have made my question more clear. I knew that you can commit every sql statement individually, then the question would be, how can I roll them back? In other words I'd like something like savepoint and rollback to savepoint, while not loosing the tra

Re: [sqlite] commit transaction/savepoints on program crash

On 9 Aug 2010, at 5:05pm, Josh wrote: > I'm new to the list and had a question. I know the default behavior for > savepoints (or any transactions) is that if they have not been committed, if > the program crashes, they are lost. Is there any way to have them committed > by > default? Basicall

Re: [sqlite] commit transaction/savepoints on program crash

> Is there any way to have them committed by > default? Basically I *only* want the transaction rolled back in case of an > explicit rollback statement, not due to program crash/power failure, etc. Does > anyone know of a way of doing this? You can avoid transaction begin/commit statements, so tha

[sqlite] commit transaction/savepoints on program crash

Hello all, I'm new to the list and had a question. I know the default behavior for savepoints (or any transactions) is that if they have not been committed, if the program crashes, they are lost. Is there any way to have them committed by default? Basically I *only* want the transaction rolled

Re: [sqlite] Commit failing due to DB locked.

Hi Thanks for responding, I've added my comments in below. On Dec 9, 2009, at 11:30 AM, Kees Nuyt wrote: > On Tue, 08 Dec 2009 22:07:34 +0100, John Clayton > wrote: > >> Hi >> >> I've got two processes opening up two sqlite databases. Assuming the >> databases are called A and B respectively, th

Re: [sqlite] Commit failing due to DB locked.

On Tue, 08 Dec 2009 22:07:34 +0100, John Clayton wrote: >Hi > > I've got two processes opening up two sqlite databases. Assuming the > databases are called A and B respectively, then both processes do this: > using A, BEGIN IMMEDIATE > using B, BEGIN IMMEDIATE > ... do some s

[sqlite] Commit failing due to DB locked.

Hi I've got two processes opening up two sqlite databases. Assuming the databases are called A and B respectively, then both processes do this: using A, BEGIN IMMEDIATE using B, BEGIN IMMEDIATE ... do some stuff possibly involving A&B ... using B, COMMIT

Re: [sqlite] commit time

ober 22, 2009 10:51 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time thanks for the discussion. i'll keep my eyes open for lock contention. i'm going to start w/ the simple approach first and see how it goes. thanks tom -Original Message- Fr

Re: [sqlite] commit time

nshaw Sent: Wednesday, October 21, 2009 11:00 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time Dangerous and disturbing this puzzle is. Only a bug could have locked those connections. If I discover anything useful I'll report it separately (no need to hijack this

Re: [sqlite] commit time

e.org] On Behalf Of Dan Kennedy Sent: Thursday, October 22, 2009 1:27 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time On Oct 22, 2009, at 11:37 AM, John Crenshaw wrote: > An open cursor will block. I've watched it. It was a major problem, > and >

Re: [sqlite] commit time

ad_uncommitted mode. Dan. > > John > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy > Sent: Thursday, October 22, 2009 12:06 AM > To: General Discussion of SQLite Database > Subject: Re: [

Re: [sqlite] commit time

n...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, October 22, 2009 12:06 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time On Oct 22, 2009, at 5:21 AM, Tom Broadbent wrote: > if thread 1 opens a read cursor in read uncom

Re: [sqlite] commit time

ral Discussion of SQLite Database > Subject: Re: [sqlite] commit time > > Good, a single write thread saves you all the hassle involved with > yielding. Unfortunately, even without multiple writers blocking is > still > possible. If thread 1 opens a cursor, and thread 2 tries

Re: [sqlite] commit time

f SQLite Database Subject: Re: [sqlite] commit time if thread 1 opens a read cursor in read uncommitted mode it can block a write lock? i thought the read happens w/o a lock? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Beh

Re: [sqlite] commit time

:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time Good, a single write thread saves you all the hassle involved with yielding. Unfortunately, even without multiple writers blocking is still possible. If thread 1 opens a cursor, and thread 2 tries to write before that

Re: [sqlite] commit time

Discussion of SQLite Database Subject: Re: [sqlite] commit time very good. i don't anticipate multiple writers so this should be pretty simple. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw Sent: Wednesday, Octob

Re: [sqlite] commit time

QLite Database Subject: Re: [sqlite] commit time Yes, you have to call sqlite3_enable_shared_cache before opening any database connections, then execute "PRAGMA read_uncommitted = true;" on each connection. Blocking can still happen in some situations, but you can handle it as I described in my

Re: [sqlite] commit time

AM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time My understanding is that the shared cache allows table level locking for multiple threads in a single process, and can do so efficiently because the threads all share the same memory space, but if multiple processes attem

Re: [sqlite] commit time

dbent Sent: Wednesday, October 21, 2009 12:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time reading up on shared cache mode and found this: "The locking protocol used to arbitrate between multiple shared-caches or regular database users is described _elsewhe

Re: [sqlite] commit time

al Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent Sent: Wednesday, October 21, 2009 12:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time it sounds like this is the feature you recommend using: &

Re: [sqlite] commit time

users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw [johncrens...@priacta.com] Sent: Tuesday, October 20, 2009 7:18 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time Sounds like a great candidate for shared cache with PRAGMA read_uncommitted =

Re: [sqlite] commit time

lite-users-boun...@sqlite.org] On Behalf Of John Crenshaw [johncrens...@priacta.com] Sent: Tuesday, October 20, 2009 7:18 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] commit time Sounds like a great candidate for shared cache with PRAGMA read_uncommitted = true. If other thread

Re: [sqlite] commit time

ginal Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent Sent: Tuesday, October 20, 2009 8:05 PM To: sqlite-users@sqlite.org Subject: [sqlite] commit time i have a simple join table containing two ids from two other tables. i have an

[sqlite] commit time

i have a simple join table containing two ids from two other tables. i have an index on each of the ids in the join table. CREATE TABLE ContentWordItem (word_id INT, item_id INT); CREATE INDEX idx_ContentWordItem_word ON ContentWordItem(word_id); // index to perform fast queries by word_i

Re: [sqlite] commit fails with no active transaction

"Blasfamous Tshirts" wrote in message news:ca0e99280905110459m5d9d9e36q15e8188b762ce...@mail.gmail.com > 1. If there is a commit already in progress and I start another > begin/commit > block on this connection >From a different thread, I presume? SQLite uses a simple mutex to serialize all API

Re: [sqlite] commit fails with no active transaction

Thanks Igor for the quick response; bless your soul. So I will fire away a few more questions surrounding this :) 1. If there is a commit already in progress and I start another begin/commit block on this connection, will this begin or commit fail? If so, with what error? 2. If yes to (1) then is

Re: [sqlite] commit fails with no active transaction

"Blasfamous Tshirts" wrote in message news:ca0e99280905110444x26c5499fn51cf794932a5d...@mail.gmail.com > Under what scenarios can commit fail with this error "no active > transaction"? > > I have a BEGIN just before a for loop and a COMMIT after that, and it > fails with this error. Something ins

[sqlite] commit fails with no active transaction

Under what scenarios can commit fail with this error "no active transaction"? I have a BEGIN just before a for loop and a COMMIT after that, and it fails with this error. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bi

Re: [sqlite] "COMMIT"

"Joanne Pham" wrote in message news:760656.34138...@web90308.mail.mud.yahoo.com > I have read the sqlite document and document stated that: > The SQL command "COMMIT" does not actually commit the changes to > disk. It just turns autocommit back on. You omitted the next sentence: "Then, at the con

[sqlite] "COMMIT"

Hi All, I have read the sqlite document and document stated that:     The SQL command "COMMIT" does not actually commit the changes to disk. It just turns autocommit back on. The question is the default of database open connection is "autocommit" and if my function has :             sqlite3_exec

Re: [sqlite] Commit frequency and performance

On Wed, Feb 04, 2009 at 10:10:15PM -0700, Gerry Snyder scratched on the wall: > pri...@gmail.com wrote: > > Yes, there are 3 indexes being created > > > > I'll post again after figuring out which of the changes improved the > > performance. Thanks for the clues! > > Since you are able to do

Re: [sqlite] Commit frequency and performance

pri...@gmail.com wrote: > Yes, there are 3 indexes being created > > I'll post again after figuring out which of the changes improved the > performance. Thanks for the clues! > Since you are able to do some experimenting, try entering the data without the indices, and then create them. G

Re: [sqlite] Commit frequency and performance

Yes, there are 3 indexes being created. For 2 indexes, the data is in order. For the 3rd index, the data is not in order. I can understand how that will cause extra seeking. It's a good point. I just ran another test after downloading the latest SQLite version, changed the page size to 4K on t

  1   2   >