subject:"\[sqlite\] SQLITE

Thank you very much, I hope my example which probably many users tap
into unknowingly might help :-)

mit freundlichen Grüßen,
Kira Backes

On Mon, 12 Aug 2019 at 12:58, Richard Hipp  wrote:
>
> The documentation on transactions at
> https://www.sqlite.org/lang_transaction.html was written long, long
> ago, apparently long before WAL mode was available, and is in serious
> need of updating and improvement.  I'm working on that now
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Richard Hipp

The documentation on transactions at
https://www.sqlite.org/lang_transaction.html was written long, long
ago, apparently long before WAL mode was available, and is in serious
need of updating and improvement.  I'm working on that now

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Thank you Rowan,

in the context of *locks* it is kinda documented, but not in the
context of transactions :(

Just have a look at the error description:

https://www.sqlite.org/rescode.html#busy_snapshot

> The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY 
> that occurs on WAL mode databases when a database connection tries to promote 
> a read transaction into a write transaction but finds that another database 
> connection has already written to the database and thus invalidated prior 
> reads.
>
> The following scenario illustrates how an SQLITE_BUSY_SNAPSHOT error might 
> arise:
>
> Process A starts a read transaction on the database and does one or more 
> SELECT statement. Process A keeps the transaction open.
> Process B updates the database, changing values previous read by process A.
> Process A now tries to write to the database. But process A's view of the 
> database content is now obsolete because process B has modified the database 
> file after process A read from it. Hence process A gets an 
> SQLITE_BUSY_SNAPSHOT error.


in my opinion and from what I've read I've never started a READ
transaction. And if I open the transaction documentation it explicitly
says that SELECT statements to not start a read transaction. This is a
different concept from a SHARED lock. If it isn't than it should be
clearly explained and documented that every SHARED lock = READ
transaction.



mit freundlichen Grüßen,
Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Rowan Worth

On Mon, 12 Aug 2019 at 16:55, Kira Backes  wrote:

> > When you do not use explicit transactions, SQLite will automatically
> create implicit transactions.
>
> But the documentation only says that an implicit transaction is
> created for data-changing queries like INSERT:
>
> https://www.sqlite.org/lang_transaction.html
>
> > Any command that changes the database (basically, any SQL command other
> than SELECT) will automatically start a transaction if one is not already
> in effect
>

Yeah I see what you mean... That sentence should not be taken in isolation,
but I agree it's misleading! It's clarified a few paragraphs down (after
noting that a "deferred" transaction is the default mode of operation):

Thus with a deferred transaction, the BEGIN statement itself does nothing
> to the filesystem. Locks are not acquired until the first read or write
> operation. The first read operation against a database creates a SHARED
>  lock and the first
> write operation creates a RESERVED
>  lock.
>

I think the initial statement should read:

"Any command that changes or reads the database will automatically start a
transaction if one is not already in effect"

Because the actual SQL command is irrelevant -- you can still run
INSERT/CREATE or other queries which represent write operations on a
database which is EXCLUSIVELY locked by another process, as long as the
query only involves temporary tables (which is kind of a cop-out because
such queries don't have to touch the database, but it just further
highlights the fact that DB access/modification is the crucial component
and not the SQL command).

Btw your original comment said "as far as I can tell this is not documented
anywhere," but the behaviour is unsurprising after understanding sqlite's
locking model, which is documented here:

https://www.sqlite.org/lockingv3.html

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

> When you do not use explicit transactions, SQLite will automatically create 
> implicit transactions.

But the documentation only says that an implicit transaction is
created for data-changing queries like INSERT:

https://www.sqlite.org/lang_transaction.html

> Any command that changes the database (basically, any SQL command other than 
> SELECT) will automatically start a transaction if one is not already in effect

it more or less explicitly says that a SELECT does not need a
transaction. So it should be documented that a SELECT is also an
implicit transaction or that a SELECT statement is promoted to a READ
transaction when a concurrent INSERT statement happens.

mit freundlichen Grüßen,
Kira Backes
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

2019-08-12 Thread Clemens Ladisch

Kira Backes wrote:
> Our code base does not use transactions at all

When you do not use explicit transactions, SQLite will automatically
create implicit transactions.

 says:
| An implicit transaction (a transaction that is started automatically,
| not a transaction started by BEGIN) is committed automatically when
| the last active statement finishes. A statement finishes when its
| prepared statement is reset or finalized.

> we're supposed to be able to share a connection between threads as
> long as we do not read/write into the same table at the same time

One connection implies one transaction.  So if two statements happen
to be active at the same time in two threads, they will share
a transaction, and might keep the transaction active longer than the
other thread expects.

See .

> After thinking a very long time about this I found the reason: You
> absolutely can not share a WAL connection between threads or risk
> SQLITE_BUSY events.

This is not really related to threads; the same can happen when
a single thread tries to write in a formerly read-only transaction.

> This is not a theoretical case, this can happen *VERY* easily and as
> far as I can tell this is not documented anywhere

It is strongly recommended to use a separate connection per thread.

Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Dear sqlite mailing list,

I had to spend 2 days debugging a SQLITE_BUSY_SNAPSHOT, and reading
the documentation did not help me. Our code base does not use
transactions at all (we have a segmented code base protected by
mutexes for a whole section, so reads/writes do not conflict ever). We
neither had a crashing sqlite connection nor a corrupted database
file. Even reading everything in isolation did not help me, since
we're supposed to be able to share a connection between threads as
long as we do not read/write into the same table at the same time,
which we were able to ensure due to the section mutexes.

After thinking a very long time about this I found the reason: You
absolutely can not share a WAL connection between threads or risk
SQLITE_BUSY events. Yes, you heard right. If connection A runs a
SELECT query in table A, then connection B inserts something into
table B, and then you try in a concurrent thread to INSERT into table
C using connection A you will get SQLITE_BUSY_SNAPSHOT errors until
all SELECT queries are finished on connection A (in my case this took
about a minute because I was reading some cache tables with several
threads, so there was never a second where all queries were
finished...). So I had SQLITE_BUSY_SNAPSHOT errors for a full minutes
even though I never used transactions anywhere... Just because I had
some long running SELECT statements in unrelated tables it made the
connection completely unusable.

This is not a theoretical case, this can happen *VERY* easily and as
far as I can tell this is not documented anywhere (and believe me,
I've ready nearly every single page of the sqlite3 documentation). So
you should really really document this very easy to trigger case.

And if you don't believe me: since I'm a nice girl I've written a unit
test (C++, catch2) for this which reliably reproduces this behavior:


TEST_CASE("Test SQLITE_BUSY_SNAPSHOT", "[sqlite3]")
{


  std::string db_name{"test_sqlite3_busy_snapshot.sqlite3"};
  if (std::filesystem::exists(db_name)) {
REQUIRE(std::filesystem::remove(db_name));
  }



  std::mt19937_64 engine{std::random_device{}()};
  std::uniform_int_distribution u(INT64_MIN, INT64_MAX);

  auto open_db_fn = [&]{
sqlite3* handle;

REQUIRE(sqlite3_open_v2(db_name.c_str(), ,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr) == SQLITE_OK);
REQUIRE(sqlite3_exec(handle, "PRAGMA journal_mode=WAL; PRAGMA
synchronous=NORMAL; PRAGMA secure_delete=FAST;", nullptr, nullptr,
nullptr) == SQLITE_OK);
REQUIRE(sqlite3_busy_timeout(handle, 600'000) == SQLITE_OK);

return handle;
  };



  std::string insert_into_FIRST = "INSERT INTO `FIRST_table` VALUES (?)"s;
  std::string insert_into_SECOND = "INSERT INTO `SECOND_table` VALUES (?)"s;
  std::string insert_into_THIRD = "INSERT INTO `THIRD_table` VALUES (?)"s;



  // initialize db, insert 4 rows into FIRST table
  {
sqlite3* init_handle{open_db_fn()};

REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `FIRST_table` (`a`
LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);
REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `SECOND_table`
(`a` LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);
REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `THIRD_table` (`a`
LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);


sqlite3_stmt* stmt{nullptr};
REQUIRE(sqlite3_prepare_v3(init_handle, insert_into_FIRST.c_str(),
-1, 0, , nullptr) == SQLITE_OK);

for (int i{0}; i != 4; ++i) {
  REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
  REQUIRE(sqlite3_step(stmt) == SQLITE_DONE);
  REQUIRE(sqlite3_reset(stmt) == SQLITE_OK);
}

REQUIRE(sqlite3_finalize(stmt) == SQLITE_OK);

REQUIRE(sqlite3_close(init_handle) == SQLITE_OK);
  }

  // init handle is closed, from here on we have a clean state





  sqlite3* FIRST_handle{open_db_fn()};
  sqlite3* SECOND_handle{open_db_fn()};



  std::atomic_bool shall_continue_read_from_FIRST = true;


  // Continuously read from FIRST table using FIRST handle
  std::thread thread_read_from_FIRST{[&] {
sqlite3_stmt* stmt;
REQUIRE(sqlite3_prepare_v3(FIRST_handle, "SELECT * FROM
`FIRST_table`", -1, SQLITE_PREPARE_PERSISTENT, , nullptr) ==
SQLITE_OK);

while (shall_continue_read_from_FIRST) {
  while (sqlite3_step(stmt) == SQLITE_ROW) {
sqlite3_column_int(stmt, 0);
std::this_thread::sleep_for(std::chrono::milliseconds{5});
  }
  sqlite3_reset(stmt);
}
sqlite3_finalize(stmt);
  }};




  // Insert once into SECOND table (untouched before) using SECOND
handle (unused before)
  {
sqlite3_stmt* stmt;
REQUIRE(sqlite3_prepare_v3(SECOND_handle,
insert_into_SECOND.c_str(), -1, SQLITE_PREPARE_PERSISTENT, ,
nullptr) == SQLITE_OK);
REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
REQUIRE(sqlite3_step(stmt) == SQLITE_DONE);
REQUIRE(sqlite3_reset(stmt) == SQLITE_OK);
sqlite3_finalize(stmt);
  }




  // Insert once into THIRD table (untouched before)

[sqlite] SQLITE_BUSY/SQLITE_LOCKED

2014-07-23 Thread Paul Joyce

Hi,


I just thought I'd point out something that confused me, in the hope of helping 
others.


from the source, where the constants are defined:

#define SQLITE_BUSY 5   /* The database file is locked */

#define SQLITE_LOCKED   6   /* A table in the database is locked */


and from the function that translates these to english messages:

/* SQLITE_BUSY*/ "database is locked",

/* SQLITE_LOCKED  */ "database table is locked",


But the wiki page http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked has 
mixed the two up:

Error Code SQLITE_LOCKED (6): Database Is Locked


I was getting a "database is locked" message in my log file, and spent some 
time trying to understand how I could be getting SQLITE_LOCKED error, when in 
fact I was just getting a SQLITE_BUSY!


?

Regards, Paul
www.we-do-it.com
APAC - India - EMEA - Americas
[http://www.we-do-it.com/images/footerlogo.jpg]
[http://www.we-do-it.com/images/facebook-com.png]
 [http://www.we-do-it.com/images/linkedin-favicon.png] 
  
[http://www.we-do-it.com/images/twitter-com.png] 
  
[http://www.we-do-it.com/images/new-g-plus-icon-32.png] 
  
[http://www.we-do-it.com/images/youtube_favicon.20px.png] 

ISO 9001:2008
Certified for the provision of
IT based Consulting
Training and
Product Development Services
[http://www.we-do-it.com/images/iso9001certified.jpg]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY on rare occasions, single thread, single connection

2013-07-23 Thread RSmith



- You have another open operation or transaction which has not been
committed or finalized or in some way completed.


If I only have one connection, there can't be another uncommitted transaction?
Well there can be, which would cause a BUSY signal. Unless you've had success beginning the new transaction, in which case it can be 
the only open transaction. Do all prior end transaction operations return SQLITE_OK?



Bottom line is this: SQLite can't report a BUSY signal if it isn't actually
BUSY.  Being busy means it is waiting to finish some open DB operation
(which only you could have started) and you have either not yet finished it
or released it of its responsibility, so pay good attention to where you
release/close the queries.

I only have a handful of prepared statements, that are reused by
calling sqlite3_reset in between queries. This should be good, right?
I don't need to finalize them before last use?


Well yes, at least it works for me this way, unless someone else here knows of a quirk with resetting statements which I am unaware 
of. But, what I was trying to highlight,  is that one or more of these other statements HAS to be not reset or not finalized, or 
more simply, HAS to still have responsibility against them towards an open query. Do you check the return results of every 
sqlite3_reset() statement? Do they all return SQLITE_OK?  I once had an "If" statement that sidestepped a reset when an 
sqlite3_step() returned an error, so the error situation remained open, etc.


Bottom line: If only one connection exists, and every created query pointer (prepared statement) was successfully reset or finalized 
returning SQLITE_OK, then it is a physical impossibility for SQLite to return a busy signal (again, unless I am missing something). 
So you need to find the one that fails when reset/finalized.


Also - even if you have multiple connections, like say you are viewing the dataset with a Database management tool while coding, it 
should only ever fail when a table is actually locked, which should only ever happen at the very time you edit it from the other 
connection, and also only if you don't have a timeout pragma set. Setting a timeout and testing it without getting errors, then 
resetting it and getting errors again; is usually an easy test for me to ensure another connection is the culprit:

http://www.sqlite.org/pragma.html#pragma_busy_timeout

 Last but not least, as most coders have experienced at some point - you may have an incorrectly sized buffer actually writing into 
wrong memory locations, usually a silly glitch like sizing a MCBS character array to the length rather than the bytesize or 
whatever, which means one inconspicuous bit of your code is corrupting the memory of another process. There are tools to check this 
with in most dev environments, and it's not a likely fault, but if you have tested everything and it is correct, and things 
happening start looking like "magic", then it's time for finding a memory bug.


Have a great day!

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY on rare occasions, single thread, single connection

2013-07-23 Thread Jesper Baekdahl

Thank you all very much for your answers..

On Mon, Jul 22, 2013 at 9:07 AM, RSmith  wrote:

> - You have another open operation or transaction which has not been
> committed or finalized or in some way completed.

If I only have one connection, there can't be another uncommitted transaction?

> Bottom line is this: SQLite can't report a BUSY signal if it isn't actually
> BUSY.  Being busy means it is waiting to finish some open DB operation
> (which only you could have started) and you have either not yet finished it
> or released it of its responsibility, so pay good attention to where you
> release/close the queries.

I only have a handful of prepared statements, that are reused by
calling sqlite3_reset in between queries. This should be good, right?
I don't need to finalize them before last use?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY on rare occasions, single thread, single connection

2013-07-22 Thread Simon Slavin

On 22 Jul 2013, at 7:28am, Jesper Baekdahl  wrote:

> there should not be anything holding a
> lock.

Please check the return values of all the sqlite3_ API calls before the one 
which is getting SQLITE_BUSY, even those which are apparently working.  For 
instance, do not do

sqlite3_finalize(S)

do at the very least

assert(sqlite3_finalize(S) == SQLITE_OK)

maybe better still

if (sqlite3_finalize(S) != SQLITE_OK) {
do some error-handling here
}

One you have tracked down the error, you should know that

   if( res == SQLITE_BUSY )
   usleep(1000);

is not a good idea.  SQLite has its own busy-handling code with increasing 
backoff and other cool things. Set your timeout with

and if you're still getting SQLITE_BUSY somewhere, report it to the user and 
quit gracefully.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY on rare occasions, single thread, single connection

2013-07-22 Thread Stephan Beal

On Mon, Jul 22, 2013 at 3:48 PM, Simon Slavin  wrote:

> assert(sqlite3_finalize(S) == SQLITE_OK)
>

Nononono - in non-debug builds assert() is compiled out, so the above
disappears.

(Been there, done that!)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY on rare occasions, single thread, single connection

2013-07-22 Thread RSmith

Hi Jesper, the code you posted is not the offending code, it's just the symptom of a problem happening somewhere else. We will need
to see the other operations also done by the DB code to know what goes on with it, but from some experiences on the list the
following is likely:

- You have another open operation or transaction which has not been committed
or finalized or in some way completed.
- The insert_statement contains a binding linked to a function that is not completing (though this should only ever break in a
multi-threaded case)
- There is a dependency on a binding or code within the DB operations to another read operation which can't succeed while the insert
query is executing.

The fact that it does not happen often does make tracing hard, but it also is a great clue to look for code executing with values
which either change a lot or have other dependencies with fluctuating results or execution timeframes.

Bottom line is this: SQLite can't report a BUSY signal if it isn't actually BUSY. Being busy means it is waiting to finish some
open DB operation (which only you could have started) and you have either not yet finished it or released it of its responsibility,
so pay good attention to where you release/close the queries. If you still can't eradicate the problem, post the code for the whole
transaction please.

On 2013/07/22 08:28, Jesper Baekdahl wrote:

Hey everybody.

I'm getting SQLITE_BUSY on sqlite_step on insert statement, that is
part of an explicit transaction of multiple statements. There is only
1 thread and 1 connection, so there should not be anything holding a
lock.
It does not happen very often, so I'm having a hard time debugging it.
The code is something like this:
do {
res = sqlite3_step(insert_statement);
if( res == SQLITE_BUSY )
usleep(1000);
} while ( res == SQLITE_BUSY);

And it will just hang there forever..

Looking foward to some advice.

Regards,

Jesper

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY on rare occasions, single thread, single connection

2013-07-22 Thread Jesper Baekdahl

Hey everybody.

I'm getting SQLITE_BUSY on sqlite_step on insert statement, that is
part of an explicit transaction of multiple statements. There is only
1 thread and 1 connection, so there should not be anything holding a
lock.
It does not happen very often, so I'm having a hard time debugging it.
The code is something like this:
do {
res = sqlite3_step(insert_statement);
if( res == SQLITE_BUSY )
usleep(1000);
} while ( res == SQLITE_BUSY);

And it will just hang there forever..

Looking foward to some advice.

Regards,

Jesper
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY exceptions

2012-05-08 Thread Simon Slavin

On 9 May 2012, at 12:15am, Cyndy Koobs  wrote:

> I am using SQLite with hibernate 4.1.0 (multithreaded but not heavily 
> loaded). I am frequently getting SQLITE_BUSY exceptions.

Have you set a SQLite timeout ?  The default is zero, which means that any 
contention will immediately trigger _BUSY.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY exceptions

2012-05-08 Thread Cyndy Koobs

I am using SQLite with hibernate 4.1.0 (multithreaded but not heavily loaded). 
I am frequently getting SQLITE_BUSY exceptions. When the database is created, 
is the journal mode default WAL?  What is the default size of the journal? What 
is the default threading mode when compiled?

If I want/need to change any of these settings, do you know if/how that is 
possible with hibernate?

Thanks for any/all insight

--
Cyndy Koobs
Senior Software Engineer

Merge Healthcare
900 Walnut Ridge Drive
Hartland, WI 53209

P: 262.369.3184
E: cyndy.ko...@merge.com

www.merge.com

any image. anywhere. any time.

PRIVACY STATEMENT: This message and all attachments are a private
communication and may be confidential or protected by privilege. If you
are not the intended recipient, you are hereby notified that any disclosure, 
copying, distribution or use of the information contained in or attached to 
this message is strictly prohibited. Thank you


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

On 26 Nov 2011, at 4:00pm, Jean-Christophe Deschamps wrote:

>> So I make up for it by using an 'exec' which executes only the first 
>> command, and by hashing the command so that tampering with it would make it 
>> fail the hash.
> 
> AFAIK sqlite3_exec will hapilly prepare and run more than one statements in a 
> row.  I use it all the time.

But several APIs offer ones which won't.  For instance

http://www.php.net/manual/en/sqlite3.query.php

will only execute one command.  So even if someone cracks my (fairly secure but 
not paranoid) system and does a 'little Bobby Tables' on me, the second command 
won't be executed.

>> I hadn't known [_exec] removal was even being considered.
> 
> Not by the dev team AFAIK but the idea has been expressed several times that 
> it was an obsolote, useless API we should get rid of.

Arghh !I use _prepare for apps which do a lot of processing, but quite 
a lot of my SQLite use is on computers I personally control and in situations 
where _exec is as safe as anything.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY




So I make up for it by using an 'exec' which executes only the first 
command, and by hashing the command so that tampering with it would 
make it fail the hash.


AFAIK sqlite3_exec will hapilly prepare and run more than one 
statements in a row.  I use it all the time.


But there's no reason you'd have to use exec just because you're 
passing a string.  You can still use _prepare, _step, _finalize if you 
find it convenient, you just use it on a string which has the 
completed command in.


Yes it was confusing on my part saying so without details.  In my 
wrapper I offer _SQLite_Exec wrapping sqlite3_exec for simple "dry" 
statements like setting pragmas and such.
For actual queries (i.e. returning results), I use internally _prepare, 
_step and optionally _finalize, all wrapped into higher-level 
interface.  For my advanced users these APIs are also available as 
separate functions (but not any _bind).  But the fact is that most 
users here have hard time getting how SQL works at all, so keeping it 
simple is good enough.
I even have wrappers like _SQLite_GetTable2D for returning a whole 
ready-to-use result array.



I hadn't known its removal was even being considered.


Not by the dev team AFAIK but the idea has been expressed several times 
that it was an obsolote, useless API we should get rid of.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

2011-11-26 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/11/11 02:07, joel.bertr...@external.gdfsuez.com wrote:
> If sqlite3_step() returns BUSY_TIMEOUT, I try to restart statement :
> 
> While((result = sqlite3_step(stmt)) == SQLITE_BUSY) { Usleep(_random
> [0s, 1s[_); }

Is there any particular reason you do this?  Just set a busy timeout and
SQLite has an internal function that does a series of usleeps so you won't
get busy errors in the first place until the overall timeout elapses.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk7RBf4ACgkQmOOfHg372QSc3gCePh1Ur8x1r6+8k2GKfQFTnZsD
bBgAoNdkXWhqwx9rvk767oB/p0WqtaZJ
=MF6R
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY



And it's convenient to be able to do the prepare during startup.  I've 
just never had to worry about this situation.  It means you have to do 
special handling for the first _step, but it's a reasonably compact 
way of handling the potential problem.


I can't use prepared statements in practice with the (interpreted) 
scripting language I use most (AutoIt) due to its DLL invokation being 
so slow: having to first prepare then perform multiple _Bind calls and 
having to sort out variable type for each _Bind proves way slower than 
an all-in-one "dumb" _Exec.  That's possibly true for more scripting 
languages.


That's why _Exec should definitely be kept part of the SQLite API.

Anyway, I expect things in this language could change in some future 
and prepared statements become affordable in this context. 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

On 26 Nov 2011, at 2:29pm, Jean-Christophe Deschamps wrote:

> Hi Simon,
> 
>> Ah, so it's the first _step which does the locking, rather than the _prepare.
> 
> That makes sense to me.  If I had a serious application using, say, a hundred 
> of different prepared queries, I'd rather batch-prepare them all up front 
> then use them when needed without asking question.  Compared to having to 
> check before every use whether to prepare or not right in the core of the app 
> code.  If prepare were to lock thing up, it would defeat such use. 

Sure.  And it's convenient to be able to do the prepare during startup.  I've 
just never had to worry about this situation.  It means you have to do special 
handling for the first _step, but it's a reasonably compact way of handling the 
potential problem.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY


Hi Simon,

Ah, so it's the first _step which does the locking, rather than the 
_prepare.


That makes sense to me.  If I had a serious application using, say, a 
hundred of different prepared queries, I'd rather batch-prepare them 
all up front then use them when needed without asking 
question.  Compared to having to check before every use whether to 
prepare or not right in the core of the app code.  If prepare were to 
lock thing up, it would defeat such use. 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

On 26 Nov 2011, at 2:15pm, Pavel Ivanov wrote:

> On Sat, Nov 26, 2011 at 9:03 AM, Simon Slavin  wrote:
>> On 26 Nov 2011, at 2:00pm, Pavel Ivanov wrote:
>> 
>>> When sqlite3_step returned SQLITE_BUSY you have to call sqlite3_reset
>>> before calling sqlite3_step again.
>> 
>> If your statement is a SELECT returning 50 rows, and you've already read 20 
>> of them by the time you get the _BUSY, do you get the first 20 again after 
>> doing the _reset ?
> 
> This is impossible. On any SELECT statement you either get SQLITE_BUSY
> before the first row or you don't get it at all.

Ah, so it's the first _step which does the locking, rather than the _prepare.  
Okay, that makes the programming easier than if you might have to reset at any 
_step.  Thanks.  Also to J-C.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

2011-11-26 Thread Pavel Ivanov

On Sat, Nov 26, 2011 at 9:03 AM, Simon Slavin  wrote:
> On 26 Nov 2011, at 2:00pm, Pavel Ivanov wrote:
>
>> When sqlite3_step returned SQLITE_BUSY you have to call sqlite3_reset
>> before calling sqlite3_step again.
>
> If your statement is a SELECT returning 50 rows, and you've already read 20 
> of them by the time you get the _BUSY, do you get the first 20 again after 
> doing the _reset ?

This is impossible. On any SELECT statement you either get SQLITE_BUSY
before the first row or you don't get it at all.


Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY



If your statement is a SELECT returning 50 rows, and you've already 
read 20 of them by the time you get the _BUSY, do you get the first 20 
again after doing the _reset ?


I don't believe we can get _BUSY in the middle of a SELECT.  Am I wrong? 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

On 26 Nov 2011, at 2:00pm, Pavel Ivanov wrote:

> When sqlite3_step returned SQLITE_BUSY you have to call sqlite3_reset
> before calling sqlite3_step again.

If your statement is a SELECT returning 50 rows, and you've already read 20 of 
them by the time you get the _BUSY, do you get the first 20 again after doing 
the _reset ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

2011-11-26 Thread Pavel Ivanov

> While((result = sqlite3_step(stmt)) == SQLITE_BUSY)
> {
>                Usleep(_random [0s, 1s[_);
> }

When sqlite3_step returned SQLITE_BUSY you have to call sqlite3_reset
before calling sqlite3_step again. So your loop should look like this:


While((result = sqlite3_step(stmt)) == SQLITE_BUSY)
{
               sqlite3_reset(stmt);
               Usleep(_random [0s, 1s[_);
}


Pavel


On Fri, Nov 25, 2011 at 5:07 AM,   wrote:
>                Hello,
>
>                I'm writing a multithreaded application (sqlite 3.7.9 on linux 
> i386).
> First thread exports some parts of a database, deletes exported records and 
> starts a vacuum. Second thread tries to write in database.
> When my application tries to write data when the other thread is running 
> vacuum, sqlite3_step() returns SQL_BUSY. I have set busy_timeout to 1000.
>
>                If sqlite3_step() returns BUSY_TIMEOUT, I try to restart 
> statement :
>
> While((result = sqlite3_step(stmt)) == SQLITE_BUSY)
> {
>                Usleep(_random [0s, 1s[_);
> }
>
> But this loop always enters in a deadlock : if sqlite3_step() returns 
> SQLITE_BUSY, and if I try to restart the same statement, it always will 
> returns SQLITE_BUSY even if there is no other concurrent transaction. If I 
> write :
>
> Int I = 0;
> While((result = sqlite3_step(stmt)) == SQLITE_BUSY)
> {
>                Usleep(_random [0s, 1s[_);
>                If (++I > 10) break;
> }
>
> I have forgotten current statement (and data) but following SQL query returns 
> SQLITE_OK. Why does sqlite3_step() always returns SQLITE_BUSY on a statement 
> when it has returned SQLITE_BUSY the first time ?
>
> Regards,
>
> JB
>
>
> GDF SUEZ Mail Disclaimer: http://www.gdfsuez.com/disclaimer/disclaimer-fr.html
>
> ___
> 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

[sqlite] SQLITE_BUSY

2009-12-01 Thread Mike Johnston

I have two threads in a Linux process using sqlite 3.6.12 in shared cache mode.

One thread opens the database file in read only mode (sqlite3_open_v2()), sets 
to read uncommitted and only ever performs selects from the database.

The other thread inserts, updates and deletes rows from the database.  On 
occasion the return code of SQLITE_BUSY is returned from sqlite3_step().  Is 
this possible with the other thread in read only mode?   I have a busy handler 
installed but I would like to understand what's going on here.

TIA



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY

2009-10-26 Thread Pavel Ivanov

General suggestion to you, Mark, would be change your database engine,
use something else than SQLite.

Application that continuously writing to the database thousands of
rows (what size database can reach with it, I wonder?) from one
process and tries to read from another process is DOA with SQLite.
It's even written at http://www.sqlite.org/whentouse.html (see "High
Concurrency" part at the end). You can get some luck with such kind of
application combined with SQLite if you work from the single process
with shared cache turned on (and maybe even read_uncommitted would
also have to be turned on). But if you want to do it from different
processes you better do it with MySQL or alike.

Pavel

On Mon, Oct 26, 2009 at 4:21 AM, Mark Flipphi
 wrote:
> Hello,
>
> We have a sqlite database that is used to strore measurement data.
>
> One application is on a server and is continuous storing data to the
> database.
> We use :
> BEGIN
> INSERT
> INSERT
> 
> COMMIT
> The commit is called when there are more then 100.000 inserts or 1
> second  has elapsed.
>
> Now an other application is running on a desktop pc and opens the
> database from the server harddisk (shared drive)
> We need to be able to acces the data in the database, but the database
> is almost always locked.
> This application is reading the data and needs to be able to
> occasionally store some fields.
>
> I think it has something to do with the BEGIN that locks the database.
>
> Are there any suggestion on how to solve this ?
>
> Server is Windows 2008 R2, Desktop is Windows Vista
>
> With kind regards,
>
> Mark Flipphi
>
> ___
> 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

Re: [sqlite] SQLITE_BUSY

2009-10-26 Thread Simon Slavin

On 26 Oct 2009, at 8:21am, Mark Flipphi wrote:

> Now an other application is running on a desktop pc and opens the
> database from the server harddisk (shared drive)

Please verify for us that it's using a standard Windows shared  
folder.  This tells us how the sharing is achieved.

> We need to be able to acces the data in the database, but the database
> is almost always locked.
> This application is reading the data and needs to be able to
> occasionally store some fields.
>
> I think it has something to do with the BEGIN that locks the database.

Take a look at

http://www.sqlite.org/lang_transaction.html

and see which of the three BEGINs would suit you best for each of the  
two programs: the one that does the writing and the one that does  
mostly reading.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY

2009-10-26 Thread Mark Flipphi

Hello,

We have a sqlite database that is used to strore measurement data.

One application is on a server and is continuous storing data to the 
database.
We use :
BEGIN
INSERT
INSERT

COMMIT
The commit is called when there are more then 100.000 inserts or 1 
second  has elapsed.

Now an other application is running on a desktop pc and opens the 
database from the server harddisk (shared drive)
We need to be able to acces the data in the database, but the database 
is almost always locked.
This application is reading the data and needs to be able to 
occasionally store some fields.

I think it has something to do with the BEGIN that locks the database.

Are there any suggestion on how to solve this ?

Server is Windows 2008 R2, Desktop is Windows Vista

With kind regards,

Mark Flipphi

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY on a remote server host

2009-08-27 Thread Otto Palmu

Hello,

as a beginning SQlite user, I'm trying to build a simple request counter for
a web site on top of SQlite (version 3.6.6.2). I started off with the
following simple and unfinished test code, which works fine on my home
computer (Ubuntu 9.04), but on our hosted server I always get an error code
of SQLITE_BUSY on sqlite3_prepare_v2(). I've seen others with similar
problems by googling, and the problem seems to be connected with mounted
filesystems. As I have no control over the host's environment there is
little I can do about the filesystem SQlite will run on. So the question is,
if the problem is with the filesystem, is there any way around it?

 

 10  main() {

 11 

 12 char  input[500];

 13 char  *errormsg;

 14 int exec_code;

 15 sqlite3 *db;

 16 sqlite3_stmt *query;

 17 readstmnt(input);//reads a line of
input from user

 18 exec_code=sqlite3_open_v2("test_db",

 19 ,

 20 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,

 21 NULL);

 22 printf("Eksekuutio koodi sqlite3_open_v2:sta oli %d\n", exec_code);

 23

 25 exec_code = sqlite3_prepare_v2(db, input, QUERY_LENGTH, ,
NULL);

 26 if(exec_code)

 27 printf("Sqlite3_prepare_v2 meni pieleen koodilla %d\n",
exec_code);

 28

 31 exec_code = sqlite3_step(query);

 32 printf("query suoritettu, koodilla %d\n", exec_code);

  }

 

Thanks in advance,

-op

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY question

2008-07-09 Thread George Ryan

Oops, I RTFMed poorly. Thanks for the help. :-)

Dennis Cote wrote:
> George Ryan wrote:
>   
>> Hello. I'm a first-time sqlite user, and I have a question. I'm using 
>> the C/C++ API as shown in the code below, and the sqlite3_step() 
>> function always seems to return SQLITE_BUSY for me. I'm not sure what I 
>> am doing wrong. I'm running on Ubuntu 8.04, and I don't have any 
>> multiple threads or clients or virus software running, so I can't see 
>> why the database would be locked. The database file gets created okay, 
>> but it's empty.
>>
>> Any suggestions?
>>
>>   int rc = sqlite3_open_v2( "simple.db", _, SQLITE_OPEN_CREATE, 0 );
>> 
>
>  From the documentation at http://www.sqlite.org/c3ref/open.html you can 
> see that you are using an illegal flags argument to sqlite3_open_v2. You 
> need to or in the SQLITE_OPEN_READWRITE flag as well.
>
>  int rc = sqlite3_open_v2( "simple.db", _,
>  SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0 );
>
> HTH
> Dennis Cote
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 
George Ryan
Senior Software Architect
Akoostix Inc., Nova Scotia Canada
902-404-PING | www.akoostix.com

"Simplicity and elegance are unpopular because they require hard work and 
discipline to achieve and education to be appreciated."
- Edsger W. Dijkstra

The information contained in this e-mail may contain confidential information 
intended for a specific individual and purpose. The information is private and 
is legally protected by law. If you are not the intended recipient, you are 
hereby notified that any disclosure, copying, distribution or the taking of any 
action in reliance on the comments of this information is strictly prohibited. 
If you have received this communication in error, please notify the sender 
immediately by telephone or return e-mail.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY question

2008-07-09 Thread George Ryan

Nope, it's a local ext3 filesystem.

D. Richard Hipp wrote:
> On Jul 9, 2008, at 11:00 AM, George Ryan wrote:
>
>   
>> Hello. I'm a first-time sqlite user, and I have a question. I'm using
>> the C/C++ API as shown in the code below, and the sqlite3_step()
>> function always seems to return SQLITE_BUSY for me. I'm not sure  
>> what I
>> am doing wrong. I'm running on Ubuntu 8.04, and I don't have any
>> multiple threads or clients or virus software running, so I can't see
>> why the database would be locked. The database file gets created okay,
>> but it's empty.
>>
>> Any suggestions?
>>
>> 
>
> Are you using an NFS filesystem?
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 
George Ryan
Senior Software Architect
Akoostix Inc., Nova Scotia Canada
902-404-PING | www.akoostix.com

"Simplicity and elegance are unpopular because they require hard work and 
discipline to achieve and education to be appreciated."
- Edsger W. Dijkstra

The information contained in this e-mail may contain confidential information 
intended for a specific individual and purpose. The information is private and 
is legally protected by law. If you are not the intended recipient, you are 
hereby notified that any disclosure, copying, distribution or the taking of any 
action in reliance on the comments of this information is strictly prohibited. 
If you have received this communication in error, please notify the sender 
immediately by telephone or return e-mail.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY question

2008-07-09 Thread Dennis Cote

George Ryan wrote:
> Hello. I'm a first-time sqlite user, and I have a question. I'm using 
> the C/C++ API as shown in the code below, and the sqlite3_step() 
> function always seems to return SQLITE_BUSY for me. I'm not sure what I 
> am doing wrong. I'm running on Ubuntu 8.04, and I don't have any 
> multiple threads or clients or virus software running, so I can't see 
> why the database would be locked. The database file gets created okay, 
> but it's empty.
> 
> Any suggestions?
> 
>   int rc = sqlite3_open_v2( "simple.db", _, SQLITE_OPEN_CREATE, 0 );

 From the documentation at http://www.sqlite.org/c3ref/open.html you can 
see that you are using an illegal flags argument to sqlite3_open_v2. You 
need to or in the SQLITE_OPEN_READWRITE flag as well.

 int rc = sqlite3_open_v2( "simple.db", _,
 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0 );

HTH
Dennis Cote


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY question

2008-07-09 Thread D. Richard Hipp


On Jul 9, 2008, at 11:00 AM, George Ryan wrote:

> Hello. I'm a first-time sqlite user, and I have a question. I'm using
> the C/C++ API as shown in the code below, and the sqlite3_step()
> function always seems to return SQLITE_BUSY for me. I'm not sure  
> what I
> am doing wrong. I'm running on Ubuntu 8.04, and I don't have any
> multiple threads or clients or virus software running, so I can't see
> why the database would be locked. The database file gets created okay,
> but it's empty.
>
> Any suggestions?
>

Are you using an NFS filesystem?

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY question

2008-07-09 Thread George Ryan

Hello. I'm a first-time sqlite user, and I have a question. I'm using 
the C/C++ API as shown in the code below, and the sqlite3_step() 
function always seems to return SQLITE_BUSY for me. I'm not sure what I 
am doing wrong. I'm running on Ubuntu 8.04, and I don't have any 
multiple threads or clients or virus software running, so I can't see 
why the database would be locked. The database file gets created okay, 
but it's empty.

Any suggestions?



#include 
#include 
using namespace std;

int main()
{
  sqlite3* db_;
  int rc = sqlite3_open_v2( "simple.db", _, SQLITE_OPEN_CREATE, 0 );
  if ( rc ) {
cout << "failed to connect" << endl;
sqlite3_close( db_ );
return EXIT_FAILURE;
  }

  sqlite3_stmt* stmt;
  std::string query( "create table if not exists version(major int, 
minor int)" );

  rc = sqlite3_prepare_v2( db_,  query.c_str(), query.size(), , 0 );

  if ( rc ) {
cout << "failed to create statement" << endl;
sqlite3_close( db_ );
return EXIT_FAILURE;
  }
 
  rc = sqlite3_step( stmt );

  cout << "step returned " << rc << endl;
 
  sqlite3_finalize( stmt );
  sqlite3_close( db_ );
}

-- 
George Ryan
Senior Software Architect
Akoostix Inc., Nova Scotia Canada
902-404-PING | www.akoostix.com




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY frequent error

2008-06-15 Thread arbalest06


Hello!

I have an application that will try to access an sqlite database for some
database operations such as selecting, deleting, inserting, and/or updating
records. This application spawns some children that will also do the same
transactions to the database. I have developed a locking mechanism that will
lock the database file in a blocking mode to avoid sqlite_busy error. This
mechanism actually works as expected. However, while the  application is
actually running, the database transactions returns sqlite_busy quite
frequently in such a way that no transaction in all process can be
processed. The database contains really a great number of records. Would
this be a cause while an exclusive lock will be taken by sqlite itself?

I have read something that gave me a hint that the amount of records in the
database will actually let sqlite get an exclusive lock. The url is:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg28643.html so you
guys can check out for yourselves.

If anyone can help me go around this problem, please do so.

Thank you and God bless!

Best regards,

arbalest06
-- 
View this message in context: 
http://www.nabble.com/SQLITE_BUSY-frequent-error-tp17847439p17847439.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY returned from sqlite3_finalize

2008-05-09 Thread brethal


On Fri, 9 May 2008 09:09:03 -0500, "Jay A. Kreibich" <[EMAIL PROTECTED]>
said:
> On Thu, May 08, 2008 at 11:39:17AM -0700, [EMAIL PROTECTED] scratched
> on the wall:
> > If I run the following code I get some unexpected results:
> > 
> >sqlite3* db1_p = 0;
> >const int open1Res = sqlite3_open16(L"test.db", _p);
> >sqlite3* db2_p = 0;
> >const int open2Res = sqlite3_open16(L"test.db", _p);
> > 
> >sqlite3_stmt* stmt1_p;
> >const int prep1Res = sqlite3_prepare16_v2(db1_p, L"SELECT
> >* FROM test", -1, _p, 0);
> >sqlite3_stmt* stmt2_p;
> >const int prep2Res =
> >sqlite3_prepare16_v2(db2_p, L"INSERT INTO test VALUES
> >(1, 1.1, 'q')", -2, _p, 0);
> > 
> >const int step1Res = sqlite3_step(stmt1_p);
> >const int step2Res = sqlite3_step(stmt2_p);
> > 
> >const int finalize2Res1 = sqlite3_finalize(stmt2_p);
> >const int finalize2Res2 = sqlite3_finalize(stmt2_p);
> >
> > const int finalize1Res = sqlite3_finalize(stmt1_p);
> > const int finalize2Res3 = sqlite3_finalize(stmt2_p);
> > 
> > Everything works as expected up to the first call to sqlite3_finalize. 
> > I Get SQLITE_OK form the opens and prepares, SQLITE_ROW from the first
> > step statement and SQLITE_BUSY form the second step statement.  Then on
> > the first call to finalize I get SQLITE_BUSY.  I'm not sure if this
> > means that the statement could not be finalized due to a database lock
> > or that sqlite3_finalize is returning the result of executing the
> > statement. The documentation confuses me a bit on this point.
> 
>   The documentation seems pretty clear to me:
> 
>   "If the most recent call to sqlite3_step(S) for the
>   prepared statement S returned an error, then
>   sqlite3_finalize(S) returns that same error."
> 
>   http://www.sqlite.org/c3ref/finalize.html
> 
>   So yes, sqlite3_finalize() is returning the result of executing the
>   statement.

That is much clearer, the 3.4 documentation is not as clear on that
point.

> > I'm leaning towards the latter since the subsequent calls to
> > sqlite3_finalize for stmt2_p result in SQLITE_MISUSE.  So I'm wondering
> > if database locks can prevent sqlite3_finalize from running
> > successfully, it seems strange if they do.
> 
>   sqlite3_finalize() always does its job.  It can return different
>   error codes depending on what it had to do to finalize the statement,
>   but it will always finalize the statement.
> 
>   You're getting a MISUSE on the subsequent calls to _finalize() because
>   the statement is not valid after the first call to _finalize().
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "'People who live in bamboo houses should not throw pandas.' Jesus said
> that."
>- "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Thanks, that's what I figured was going on but wanted to be sure.

-brett
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY returned from sqlite3_finalize

2008-05-09 Thread Jay A. Kreibich

On Thu, May 08, 2008 at 11:39:17AM -0700, [EMAIL PROTECTED] scratched on the 
wall:
> If I run the following code I get some unexpected results:
> 
>sqlite3* db1_p = 0;
>const int open1Res = sqlite3_open16(L"test.db", _p);
>sqlite3* db2_p = 0;
>const int open2Res = sqlite3_open16(L"test.db", _p);
> 
>sqlite3_stmt* stmt1_p;
>const int prep1Res = sqlite3_prepare16_v2(db1_p, L"SELECT
>* FROM test", -1, _p, 0);
>sqlite3_stmt* stmt2_p;
>const int prep2Res =
>sqlite3_prepare16_v2(db2_p, L"INSERT INTO test VALUES
>(1, 1.1, 'q')", -2, _p, 0);
> 
>const int step1Res = sqlite3_step(stmt1_p);
>const int step2Res = sqlite3_step(stmt2_p);
> 
>const int finalize2Res1 = sqlite3_finalize(stmt2_p);
>const int finalize2Res2 = sqlite3_finalize(stmt2_p);
>
> const int finalize1Res = sqlite3_finalize(stmt1_p);
> const int finalize2Res3 = sqlite3_finalize(stmt2_p);
> 
> Everything works as expected up to the first call to sqlite3_finalize. 
> I Get SQLITE_OK form the opens and prepares, SQLITE_ROW from the first
> step statement and SQLITE_BUSY form the second step statement.  Then on
> the first call to finalize I get SQLITE_BUSY.  I'm not sure if this
> means that the statement could not be finalized due to a database lock
> or that sqlite3_finalize is returning the result of executing the
> statement. The documentation confuses me a bit on this point.

  The documentation seems pretty clear to me:

"If the most recent call to sqlite3_step(S) for the
prepared statement S returned an error, then
sqlite3_finalize(S) returns that same error."

http://www.sqlite.org/c3ref/finalize.html

  So yes, sqlite3_finalize() is returning the result of executing the
  statement.

> I'm leaning towards the latter since the subsequent calls to
> sqlite3_finalize for stmt2_p result in SQLITE_MISUSE.  So I'm wondering
> if database locks can prevent sqlite3_finalize from running
> successfully, it seems strange if they do.

  sqlite3_finalize() always does its job.  It can return different
  error codes depending on what it had to do to finalize the statement,
  but it will always finalize the statement.

  You're getting a MISUSE on the subsequent calls to _finalize() because
  the statement is not valid after the first call to _finalize().

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE_BUSY returned from sqlite3_finalize

2008-05-08 Thread brethal

If I run the following code I get some unexpected results:

   sqlite3* db1_p = 0;
   const int open1Res = sqlite3_open16(L"test.db", _p);
   sqlite3* db2_p = 0;
   const int open2Res = sqlite3_open16(L"test.db", _p);

   sqlite3_stmt* stmt1_p;
   const int prep1Res = sqlite3_prepare16_v2(db1_p, L"SELECT
   * FROM test", -1, _p, 0);
   sqlite3_stmt* stmt2_p;
   const int prep2Res =
   sqlite3_prepare16_v2(db2_p, L"INSERT INTO test VALUES
   (1, 1.1, 'q')", -2, _p, 0);

   const int step1Res = sqlite3_step(stmt1_p);
   const int step2Res = sqlite3_step(stmt2_p);

   const int finalize2Res1 = sqlite3_finalize(stmt2_p);
   const int finalize2Res2 = sqlite3_finalize(stmt2_p);

const int finalize1Res = sqlite3_finalize(stmt1_p);
const int finalize2Res3 = sqlite3_finalize(stmt2_p);

Everything works as expected up to the first call to sqlite3_finalize. 
I Get SQLITE_OK form the opens and prepares, SQLITE_ROW from the first
step statement and SQLITE_BUSY form the second step statement.  Then on
the first call to finalize I get SQLITE_BUSY.  I'm not sure if this
means that the statement could not be finalized due to a database lock
or that sqlite3_finalize is returning the result of executing the
statement. The documentation confuses me a bit on this point.  I'm
leaning towards the latter since the subsequent calls to
sqlite3_finalize for stmt2_p result in SQLITE_MISUSE.  So I'm wondering
if database locks can prevent sqlite3_finalize from running
successfully, it seems strange if they do.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY retry

2007-11-28 Thread RaghavendraK 70574

Overall sqlite is losing its credits.
Now when i run sqlite in multithread & multi process 
mode system experiences a infinite loop in the
busy handler. Also there is no log which tell
which process/thread has acquired the lock,hence needing complete system halt 
and restart. This observed in 3.4.0,3.4.2, 3.5.1,3.5.2
Also as stated elsewhere sqlite 3.5.2 now support 
passing one connecion acorss threads is not valid.

regards
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: John Stanton <[EMAIL PROTECTED]>
Date: Thursday, November 29, 2007 1:34 am
Subject: Re: [sqlite] SQLITE_BUSY  retry

> You could use a BEGIN IMMEDIATE to lock the DB before you launch 
> the 
> transaction and loop on SQLITE_BUSY or use the plain BEGIN which 
> will 
> allow reads during the transaction and not lock the DB until you 
> issue a 
> COMMIT (the END).  Just loop on the BUSY on the END SQL statement 
> until 
> the user who has the DB locked releases it.
> 
> A technique we use to get a minimum latency but reasonably 
> efficient 
> busy wait is to issue a yield call each time an SQLITE_BUSY is 
> encountered so that the time slice is dropped and other processes 
> can 
> run.  A alternative is to issue a short delay or sleep.
> 
> Joanne Pham wrote:
> > Hi All,
> > Here my statements to insert rows into the database
> >   Open the database connection
> >   BEGIN
> > insert ...using sqlite3_step
> > insert ...using sqlite3_step
> >   END
> > So at the time I issued "END" transaction I got the error message 
> SQLITE_BUSY so I need to issue the "END" transaction again or What 
> should I do in this case to handle SQLITE_BUSY.
> > Thanks a lot in advance for the help or advice.
> > JP
> > 
> > 
> > 
> > - Original Message 
> > From: Joanne Pham <[EMAIL PROTECTED]>
> > To: sqlite-users@sqlite.org
> > Sent: Wednesday, November 28, 2007 11:27:52 AM
> > Subject: [sqlite] SQLITE_BUSY retry
> > 
> > Hi All,
> > I have used "BEGIN" and "END" Transaction to insert the data to 
> SQLite database.
> > BEGIN
> > insert ...
> > insert ...
> >   END
> > 
> > When I issued the "END" operation the error message return back 
> is "SQLITE_BUSY". 
> > What should I do if I want to handle SQLITE_BUSY /retry the 
> transaction. Should I execute "END" transaction again.
> > How to handle the SQLITE_BUSY?
> > Thanks,
> > JP
> > 
> > 
> >   
> >
>  Get easy, one-click access to your favorites. 
> > Make Yahoo! your homepage.
> > http://www.yahoo.com/r/hs
> > 
> > 
> >   
> >
>  Be a better sports nut!  Let your teams follow you 
> > with Yahoo Mobile. Try it now.  
> http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLITE_BUSY retry

2007-11-28 Thread John Stanton


Joanne Pham wrote:

Hi John,
Thanks a lot for your response.
Make sure I am understanding your answer related to SQLITE_BUSY. So I need to 
change my code from
Open the database connection
 BEGIN
  insert ...using sqlite3_step
 insert ...using sqlite3_step
  END

to 



Open the database connection
 BEGIN
  insert ...using sqlite3_step
 insert ...using sqlite3_step
...
 do {
  rc = END (transaction or commit)
while (rc == SQLITE_BUSY)

So I just loop on if the return statement is SQLITE_BUSY.
Is that correct John?
Thanks a ton,
JP


Correct.  You might add a count to drop out if there is a deadlock.

e.g.

  use sqlite3_prepare_v2

  var count = MAX_SPINS;
  while (1) {
 rc = sqlite3_step();
 if (rc == SQLITE_BUSY) yield();
 else {
   if ((rc != SQLITE_OK) || (count <= 0)) report_error();
   break;
 }
 count--;
  }




- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 28, 2007 12:04:34 PM
Subject: Re: [sqlite] SQLITE_BUSY retry

You could use a BEGIN IMMEDIATE to lock the DB before you launch the 
transaction and loop on SQLITE_BUSY or use the plain BEGIN which will 
allow reads during the transaction and not lock the DB until you issue a 
COMMIT (the END).  Just loop on the BUSY on the END SQL statement until 
the user who has the DB locked releases it.


A technique we use to get a minimum latency but reasonably efficient 
busy wait is to issue a yield call each time an SQLITE_BUSY is 
encountered so that the time slice is dropped and other processes can 
run.  A alternative is to issue a short delay or sleep.


Joanne Pham wrote:

Hi All,
Here my statements to insert rows into the database
 Open the database connection
 BEGIN
   insert ...using sqlite3_step
   insert ...using sqlite3_step
 END
So at the time I issued "END" transaction I got the error message SQLITE_BUSY so I need 
to issue the "END" transaction again or What should I do in this case to handle 
SQLITE_BUSY.
Thanks a lot in advance for the help or advice.
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 28, 2007 11:27:52 AM
Subject: [sqlite] SQLITE_BUSY retry

Hi All,
I have used "BEGIN" and "END" Transaction to insert the data to SQLite database.
   BEGIN
   insert ...
   insert ...
 END

When I issued the "END" operation the error message return back is "SQLITE_BUSY". 
What should I do if I want to handle SQLITE_BUSY /retry the transaction. Should I execute "END" transaction again.

How to handle the SQLITE_BUSY?
Thanks,
JP


 

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.

http://www.yahoo.com/r/hs


 

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.   http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ



-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  http://overview.mail.yahoo.com/



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLITE_BUSY retry

2007-11-28 Thread Joanne Pham

Hi John,
Thanks a lot for your response.
Make sure I am understanding your answer related to SQLITE_BUSY. So I need to 
change my code from
Open the database connection
 BEGIN
  insert ...using sqlite3_step
 insert ...using sqlite3_step
  END

to 

Open the database connection
 BEGIN
  insert ...using sqlite3_step
 insert ...using sqlite3_step
...
 do {
  rc = END (transaction or commit)
while (rc == SQLITE_BUSY)

So I just loop on if the return statement is SQLITE_BUSY.
Is that correct John?
Thanks a ton,
JP

- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 28, 2007 12:04:34 PM
Subject: Re: [sqlite] SQLITE_BUSY retry

You could use a BEGIN IMMEDIATE to lock the DB before you launch the 
transaction and loop on SQLITE_BUSY or use the plain BEGIN which will 
allow reads during the transaction and not lock the DB until you issue a 
COMMIT (the END).  Just loop on the BUSY on the END SQL statement until 
the user who has the DB locked releases it.

A technique we use to get a minimum latency but reasonably efficient 
busy wait is to issue a yield call each time an SQLITE_BUSY is 
encountered so that the time slice is dropped and other processes can 
run.  A alternative is to issue a short delay or sleep.

Joanne Pham wrote:
> Hi All,
> Here my statements to insert rows into the database
>  Open the database connection
>  BEGIN
>insert ...using sqlite3_step
>insert ...using sqlite3_step
>  END
> So at the time I issued "END" transaction I got the error message SQLITE_BUSY 
> so I need to issue the "END" transaction again or What should I do in this 
> case to handle SQLITE_BUSY.
> Thanks a lot in advance for the help or advice.
> JP
> 
> 
> 
> - Original Message 
> From: Joanne Pham <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Wednesday, November 28, 2007 11:27:52 AM
> Subject: [sqlite] SQLITE_BUSY retry
> 
> Hi All,
> I have used "BEGIN" and "END" Transaction to insert the data to SQLite 
> database.
>BEGIN
>insert ...
>insert ...
>  END
> 
> When I issued the "END" operation the error message return back is 
> "SQLITE_BUSY". 
> What should I do if I want to handle SQLITE_BUSY /retry the transaction. 
> Should I execute "END" transaction again.
> How to handle the SQLITE_BUSY?
> Thanks,
> JP
> 
> 
>  
> 
> Get easy, one-click access to your favorites. 
> Make Yahoo! your homepage.
> http://www.yahoo.com/r/hs
> 
> 
>  
> 
> Be a better sports nut!  Let your teams follow you 
> with Yahoo Mobile. Try it now.   
> http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Be a better pen pal. 
Text or chat with friends inside Yahoo! Mail. See how.  
http://overview.mail.yahoo.com/

Re: [sqlite] SQLITE_BUSY retry

2007-11-28 Thread John Stanton

You could use a BEGIN IMMEDIATE to lock the DB before you launch the 
transaction and loop on SQLITE_BUSY or use the plain BEGIN which will 
allow reads during the transaction and not lock the DB until you issue a 
COMMIT (the END).  Just loop on the BUSY on the END SQL statement until 
the user who has the DB locked releases it.


A technique we use to get a minimum latency but reasonably efficient 
busy wait is to issue a yield call each time an SQLITE_BUSY is 
encountered so that the time slice is dropped and other processes can 
run.  A alternative is to issue a short delay or sleep.


Joanne Pham wrote:

Hi All,
Here my statements to insert rows into the database
  Open the database connection
  BEGIN
insert ...using sqlite3_step
insert ...using sqlite3_step
  END
So at the time I issued "END" transaction I got the error message SQLITE_BUSY so I need 
to issue the "END" transaction again or What should I do in this case to handle 
SQLITE_BUSY.
Thanks a lot in advance for the help or advice.
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 28, 2007 11:27:52 AM
Subject: [sqlite] SQLITE_BUSY retry

Hi All,
I have used "BEGIN" and "END" Transaction to insert the data to SQLite database.
BEGIN
insert ...
insert ...
  END

When I issued the "END" operation the error message return back is "SQLITE_BUSY". 
What should I do if I want to handle SQLITE_BUSY /retry the transaction. Should I execute "END" transaction again.

How to handle the SQLITE_BUSY?
Thanks,
JP


  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.

http://www.yahoo.com/r/hs


  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLITE_BUSY retry

2007-11-28 Thread Joanne Pham

Hi All,
Here my statements to insert rows into the database
  Open the database connection
  BEGIN
insert ...using sqlite3_step
insert ...using sqlite3_step
  END
So at the time I issued "END" transaction I got the error message SQLITE_BUSY 
so I need to issue the "END" transaction again or What should I do in this case 
to handle SQLITE_BUSY.
Thanks a lot in advance for the help or advice.
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 28, 2007 11:27:52 AM
Subject: [sqlite] SQLITE_BUSY retry

Hi All,
I have used "BEGIN" and "END" Transaction to insert the data to SQLite database.
BEGIN
insert ...
insert ...
  END

When I issued the "END" operation the error message return back is 
"SQLITE_BUSY". 
What should I do if I want to handle SQLITE_BUSY /retry the transaction. Should 
I execute "END" transaction again.
How to handle the SQLITE_BUSY?
Thanks,
JP


  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs


  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

[sqlite] SQLITE_BUSY retry

2007-11-28 Thread Joanne Pham

Hi All,
I have used "BEGIN" and "END" Transaction to insert the data to SQLite database.
BEGIN
insert ...
insert ...
   END

When I issued the "END" operation the error message return back is 
"SQLITE_BUSY". 
What should I do if I want to handle SQLITE_BUSY /retry the transaction. Should 
I execute "END" transaction again.
 How to handle the SQLITE_BUSY?
Thanks,
JP


  

Get easy, one-click access to your favorites. 
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs

Re: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-16 Thread John Stanton

I came across some OS's over the years which implemented file locks as a 
single global lock.  Yours may do that.


Mark Brown wrote:

Hi John-

There is a .lock file for each database.  From my understanding, that should
prohibit 2 connections from using the same database at the same time.
However, that is not the situation I am wondering about.  I am specifically
wondering if database activity on a connection to DB 1 would have any effect
on database activity on a different connection to DB2.

Thanks,
Mark



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-16 Thread Ken

 
  "As recommended, BEGIN IMMEDIATE should prevent thread2 from even
starting a transaction if thread1 did so first, however I think this
will only work correctly if the same connection handle is used in both,
else they still may not know about eachother."
   
   
   
  Simply not true... If you have different connection handles to the same db.. 
Then it would be wise to use BEGIN EXCLUSIVE. The reason is that sqlite will 
acquire an EXCLUSIVE lock, in the file when you use begin EXLCUSIVE. Or it will 
return a sqlite error sqlite_busy, simply retry...
   
  BEGIN IMMEDIATE will acquire a reserved lock. Other uses may still be reading 
and this lock type must escalate to a PENDING and then to an EXCLUSIVE. 
   
  While the reserved lock is enabled other users (threads) may perform reads. 
But they may not perform begin immediate/exlusive etc...
   
  Once an exclusive lock is acquired no other user (thread) may access the DB..
   
  Example:
  THREAD1 THREAD2
sqlite3_prepare
  sqlite3_step
  (Step through query) BEGIN EXCLUSIVE -- Loop here on sqlite BUSY.
INSERTS   -- You should not get any qlite 
busy here!
COMMIT-- Nor should you get sqlite busy 
here!!!
   
  The problem you were facing is that whith a begin immediate sqlite acquires a 
"RESERVED" lock. This is an intent to write lock. You could still actually get 
sqlite busy errors during your inserts or commit operations. But the thread 
wrting thread should eventually be able to acquire the lock and continue as 
long as the reading threads close off their locks before the writer times out.
   
  HTH.
  Ken

RE: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-16 Thread Andre du Plessis

If they are different files then you should not have any of these
problems.

-Original Message-
From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] 
Sent: 16 August 2007 11:21 AM
To: sqlite-users@sqlite.org
Subject: Re: RE: [sqlite] SQLITE_BUSY error in multi-threaded
environment

hi,

Am not clear.
Suppose i have 2 databases and a process spwans 2 threads and each
thread
opne the db will it result in any problem? They are independent files.


thx
ragha


**
 This email and its attachments contain confidential information from
HUAWEI, which is intended only for the person or entity whose address is
listed above. Any use of the information contained herein in any way
(including, but not limited to, total or partial disclosure,
reproduction, or dissemination) by persons other than the intended
recipient(s) is prohibited. If you receive this e-mail in error, please
notify the sender by phone or email immediately and delete it!
 

*

- Original Message -
From: Andre du Plessis <[EMAIL PROTECTED]>
Date: Thursday, August 16, 2007 4:36 pm
Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

> Ok well I guess I forgot to mention this is what has made me want to
> pull my hair out a few times :) the fact that you have to worry about
> both scenarios for two different reasons, if multiple threads are
> working with the same connection handle, then SQL will have a better
> understanding of the state of your connection and inform you of busy
> errors better. If you are using different DB handles what will 
> happen is
> that SQLite may not care that some other thread is busy with another
> handle and all will work fine until one point, the connection handle
> needs to commit data and enter exclusive mode, it has to get an
> exclusive lock on the DB File  and no matter that other 
> connections have
> their own handles if they have any locks on the db, sqlite will go 
> intobusy handler mode and eventually timeout,
> depending on how long you wait. If a query keeps a read cursor 
> open for
> some reason inevitably this will result in a database is locked error.
> The problem to watch out for is a deadlock, example
> 
> THREAD1 THREAD2
> BEGINBEGIN 
> INSERT SOME  INSERT SOME
> COMMIT (busy handler)COMMIT (busy handler)
> As you can see thread1 waits for thread2, they will deadlock, and 
> unlessyou have a limit in your busy handler you will wait forever.
> 
> As recommended, BEGIN IMMEDIATE should prevent thread2 from even
> starting a transaction if thread1 did so first, however I think this
> will only work correctly if the same connection handle is used in 
> both,else they still may not know about eachother.
> 
> So yes there is two ways to do this, one is that make sure your busy
> handler works properly and then let your applications just try and 
> thenfail on busy throw the exception and let the application try 
> again until
> all locks are gone,
> Or two use a global mutex (IF your application runs in more than one
> process space)
> Or 3 (use a global critical section - this will be faster) if your
> application is just in one process space.
> Make sure that inserts/queries finish their business including 
> begin and
> commit transaction in the critical
> 
> If your application ONLY does queries for example you should have NO
> problem,
> 
> Additionally if you are using the same DB handle across threads EVERY
> CALL to the library no matter what should be (serialized) locked 
> in a
> critical section.
> 
> Ive used these principles  that is running fine now, so I will 
> stick to
> this design
> 
> Hope this helps
> 
> -Original Message-
> From: Mark Brown [mailto:[EMAIL PROTECTED] 
> Sent: 15 August 2007 04:34 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
> 
> Hi Andre-
> 
> After rereading your post, I wanted to confirm something.  In your
> example
> below, are thread1 and thread2 connected to the same database, or
> different
> databases?  In my scenario, the threads are connected to different
> databases, so I'm not sure if it is the same situation.
> 
> Thanks,
> Mark
> 
> 
> > -Original Message-
> > From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
> > Sent: Wednesday, August 15, 2007 5:05 AM
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded 
> environment> 
> > 
> > Being a newbie to SQLite I've had the same problems working 
> > wi

Re: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-16 Thread RaghavendraK 70574

hi,

Am not clear.
Suppose i have 2 databases and a process spwans 2 threads and each thread
opne the db will it result in any problem? They are independent files.


thx
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: Andre du Plessis <[EMAIL PROTECTED]>
Date: Thursday, August 16, 2007 4:36 pm
Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

> Ok well I guess I forgot to mention this is what has made me want to
> pull my hair out a few times :) the fact that you have to worry about
> both scenarios for two different reasons, if multiple threads are
> working with the same connection handle, then SQL will have a better
> understanding of the state of your connection and inform you of busy
> errors better. If you are using different DB handles what will 
> happen is
> that SQLite may not care that some other thread is busy with another
> handle and all will work fine until one point, the connection handle
> needs to commit data and enter exclusive mode, it has to get an
> exclusive lock on the DB File  and no matter that other 
> connections have
> their own handles if they have any locks on the db, sqlite will go 
> intobusy handler mode and eventually timeout,
> depending on how long you wait. If a query keeps a read cursor 
> open for
> some reason inevitably this will result in a database is locked error.
> The problem to watch out for is a deadlock, example
> 
> THREAD1 THREAD2
> BEGINBEGIN 
> INSERT SOME  INSERT SOME
> COMMIT (busy handler)COMMIT (busy handler)
> As you can see thread1 waits for thread2, they will deadlock, and 
> unlessyou have a limit in your busy handler you will wait forever.
> 
> As recommended, BEGIN IMMEDIATE should prevent thread2 from even
> starting a transaction if thread1 did so first, however I think this
> will only work correctly if the same connection handle is used in 
> both,else they still may not know about eachother.
> 
> So yes there is two ways to do this, one is that make sure your busy
> handler works properly and then let your applications just try and 
> thenfail on busy throw the exception and let the application try 
> again until
> all locks are gone,
> Or two use a global mutex (IF your application runs in more than one
> process space)
> Or 3 (use a global critical section - this will be faster) if your
> application is just in one process space.
> Make sure that inserts/queries finish their business including 
> begin and
> commit transaction in the critical
> 
> If your application ONLY does queries for example you should have NO
> problem,
> 
> Additionally if you are using the same DB handle across threads EVERY
> CALL to the library no matter what should be (serialized) locked 
> in a
> critical section.
> 
> Ive used these principles  that is running fine now, so I will 
> stick to
> this design
> 
> Hope this helps
> 
> -Original Message-
> From: Mark Brown [mailto:[EMAIL PROTECTED] 
> Sent: 15 August 2007 04:34 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
> 
> Hi Andre-
> 
> After rereading your post, I wanted to confirm something.  In your
> example
> below, are thread1 and thread2 connected to the same database, or
> different
> databases?  In my scenario, the threads are connected to different
> databases, so I'm not sure if it is the same situation.
> 
> Thanks,
> Mark
> 
> 
> > -Original Message-
> > From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
> > Sent: Wednesday, August 15, 2007 5:05 AM
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded 
> environment> 
> > 
> > Being a newbie to SQLite I've had the same problems working 
> > with SQLite
> > so maybe I can help, 
> > It does not matter how well your database is synchronized, a common
> > pitfall I had was that I would have a query object with an open 
> cursor> which prevents any other statement from committ

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-16 Thread Andre du Plessis

Ok well I guess I forgot to mention this is what has made me want to
pull my hair out a few times :) the fact that you have to worry about
both scenarios for two different reasons, if multiple threads are
working with the same connection handle, then SQL will have a better
understanding of the state of your connection and inform you of busy
errors better. If you are using different DB handles what will happen is
that SQLite may not care that some other thread is busy with another
handle and all will work fine until one point, the connection handle
needs to commit data and enter exclusive mode, it has to get an
exclusive lock on the DB File  and no matter that other connections have
their own handles if they have any locks on the db, sqlite will go into
busy handler mode and eventually timeout,
depending on how long you wait. If a query keeps a read cursor open for
some reason inevitably this will result in a database is locked error.
The problem to watch out for is a deadlock, example

THREAD1 THREAD2
BEGINBEGIN 
INSERT SOME  INSERT SOME
COMMIT (busy handler)COMMIT (busy handler)
As you can see thread1 waits for thread2, they will deadlock, and unless
you have a limit in your busy handler you will wait forever.

As recommended, BEGIN IMMEDIATE should prevent thread2 from even
starting a transaction if thread1 did so first, however I think this
will only work correctly if the same connection handle is used in both,
else they still may not know about eachother.

So yes there is two ways to do this, one is that make sure your busy
handler works properly and then let your applications just try and then
fail on busy throw the exception and let the application try again until
all locks are gone,
Or two use a global mutex (IF your application runs in more than one
process space)
Or 3 (use a global critical section - this will be faster) if your
application is just in one process space.
Make sure that inserts/queries finish their business including begin and
commit transaction in the critical

If your application ONLY does queries for example you should have NO
problem,

Additionally if you are using the same DB handle across threads EVERY
CALL to the library no matter what should be (serialized) locked in a
critical section.

Ive used these principles  that is running fine now, so I will stick to
this design

Hope this helps

-Original Message-
From: Mark Brown [mailto:[EMAIL PROTECTED] 
Sent: 15 August 2007 04:34 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

Hi Andre-

After rereading your post, I wanted to confirm something.  In your
example
below, are thread1 and thread2 connected to the same database, or
different
databases?  In my scenario, the threads are connected to different
databases, so I'm not sure if it is the same situation.

Thanks,
Mark


> -Original Message-
> From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, August 15, 2007 5:05 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
> 
> 
> Being a newbie to SQLite I've had the same problems working 
> with SQLite
> so maybe I can help, 
> It does not matter how well your database is synchronized, a common
> pitfall I had was that I would have a query object with an open cursor
> which prevents any other statement from committing to the database.
> 
> So for example:
> THREAD1 THREAD2
> LOCK
> QUERY   
> UNLOCK  LOCK
> (Step through query)BEGIN TRANSACTION
> INSERTS
> COMMIT <- SQLite busy error here 
> UNLOCK 
>  




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-15 Thread Ken

If you have only one thread accessing the file. Then you shouldn't need to do 
any type of locking per se. I would leave the file locks.

I would not induce your own mutex. Sqlites locking should be adequate.
I have a system where there are two threads sharing a single db and each thread 
createing and release access to various DB's all without using any mutexes for 
sqlite synchronization.

Is this a single database file with each thread having its own connection? If 
that is the case then sure you should expect and handle the sqlite_busy.

Did you configure/compile with:configure --enable_threadsafe ?

Mark Brown <[EMAIL PROTECTED]> wrote: No, not a soft link.  :)

Based on other posts I have read about threading performance and SQLite, it
seems like most people like to use a single thread.  I'm going to change our
application to use a system-wide mutex for thread synchronization and see if
that improves our results.  I'm still thinking our problems may be low-level
file i/o bugs with our OS, so perhaps taking out the file-based .lock scheme
will help.

Thanks,
Mark

> -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, August 15, 2007 12:39 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
> 
> 
> It should not.
> 
> As long as those two connections are not used across threads 
> and point to truely different databases.
> 
> They wouldn't be a soft link would they? I
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

No, not a soft link.  :)

Based on other posts I have read about threading performance and SQLite, it
seems like most people like to use a single thread.  I'm going to change our
application to use a system-wide mutex for thread synchronization and see if
that improves our results.  I'm still thinking our problems may be low-level
file i/o bugs with our OS, so perhaps taking out the file-based .lock scheme
will help.

Thanks,
Mark


> -Original Message-
> From: Ken [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, August 15, 2007 12:39 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
> 
> 
> It should not.
> 
> As long as those two connections are not used across threads 
> and point to truely different databases.
> 
> They wouldn't be a soft link would they? I
> 
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-15 Thread Ken

It should not.

As long as those two connections are not used across threads and point to 
truely different databases.

They wouldn't be a soft link would they? I



Mark Brown <[EMAIL PROTECTED]> wrote: Hi John-

There is a .lock file for each database.  From my understanding, that should
prohibit 2 connections from using the same database at the same time.
However, that is not the situation I am wondering about.  I am specifically
wondering if database activity on a connection to DB 1 would have any effect
on database activity on a different connection to DB2.

Thanks,
Mark



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-15 Thread Joe Wilson

--- Mark Brown <[EMAIL PROTECTED]> wrote:
> There is a .lock file for each database.  From my understanding, that should
> prohibit 2 connections from using the same database at the same time.
> However, that is not the situation I am wondering about.  I am specifically
> wondering if database activity on a connection to DB 1 would have any effect
> on database activity on a different connection to DB2.

Try your sqlite concurrency test under UNIX/Linux on a local filesystem
to see if it produces the same serialized access.

Based on my limited knowledge of sqlite, I think separate connections to
different databases should not impede each other.

Would the SQLite developers care to give the definitive statement on this?

Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for 
today's economy) at Yahoo! Games.
http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow  

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

Hi John-

There is a .lock file for each database.  From my understanding, that should
prohibit 2 connections from using the same database at the same time.
However, that is not the situation I am wondering about.  I am specifically
wondering if database activity on a connection to DB 1 would have any effect
on database activity on a different connection to DB2.

Thanks,
Mark



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLITE_BUSY error in multi-threaded environment

2007-08-15 Thread John Stanton

My guess is that you will find your problem in the way file locking is 
implemented on your system.  Is there a global file lock rather than 
locks associated with each file?  A simple test program will resolve the 
issue.


Mark Brown wrote:

Hi Andre-

After rereading your post, I wanted to confirm something.  In your example
below, are thread1 and thread2 connected to the same database, or different
databases?  In my scenario, the threads are connected to different
databases, so I'm not sure if it is the same situation.

Thanks,
Mark




-Original Message-
From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 15, 2007 5:05 AM

To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment


Being a newbie to SQLite I've had the same problems working 
with SQLite
so maybe I can help, 
It does not matter how well your database is synchronized, a common

pitfall I had was that I would have a query object with an open cursor
which prevents any other statement from committing to the database.

So for example:
THREAD1 THREAD2
LOCK
QUERY   
UNLOCK  LOCK

(Step through query)BEGIN TRANSACTION
   INSERTS
   COMMIT <- SQLite busy error here 
   UNLOCK 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment

Hi Andre-

After rereading your post, I wanted to confirm something.  In your example
below, are thread1 and thread2 connected to the same database, or different
databases?  In my scenario, the threads are connected to different
databases, so I'm not sure if it is the same situation.

Thanks,
Mark


> -Original Message-
> From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, August 15, 2007 5:05 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
> 
> 
> Being a newbie to SQLite I've had the same problems working 
> with SQLite
> so maybe I can help, 
> It does not matter how well your database is synchronized, a common
> pitfall I had was that I would have a query object with an open cursor
> which prevents any other statement from committing to the database.
> 
> So for example:
> THREAD1 THREAD2
> LOCK
> QUERY   
> UNLOCK  LOCK
> (Step through query)BEGIN TRANSACTION
> INSERTS
> COMMIT <- SQLite busy error here 
> UNLOCK 
>  



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] SQLITE_BUSY error in multi-threaded environment