Re: [sqlite] Literature on the information theory behind SQL(lite)?

[Jay A. Kreibich]

On Tue, Jul 09, 2013 at 11:22:35PM +0200, Stephan Beal scratched on the wall:
> On Tue, Jul 9, 2013 at 7:24 PM, Paolo Bolzoni  > wrote:
> 
> > I am sorry the part that look Greek to you is actually fairly
> > important base of the theory behind SQL, the relational algebra.
> 
> Bad news for me ;), but you've given me a new search term: relational
> algebra.

  I really wanted to put a chapter on the Relational Model into "Using
  SQLite", but there just wasn't room.  The book went way over its
  page budget as it was.


  To jump start your searches a bit...  Modern "relational databases" are
  called that because SQL is based off something called the Relational
  Model.  The Relational Model is a formal mathematical system that
  combines Set Theory with information management.  It is a "formal
  system" in the sense that it has rigid mathematical proofs, theorems,
  and all that kind of stuff.  It was first defined in a 1969 IBM paper
  written by E.F. Codd.

  There are two mathematical systems that can be used to define and prove
  the self-consistency of the Relational Model.  One system is called
  "Relational Algebra" and the other "Relational Calculus."  The two
  systems start from slightly different base assumptions, and allow
  slightly different types of proofs, but you can more or less prove
  all of the Relational Model using either system.

  Unless you actually want postulate theorems or do proofs, the details
  of Relational Algebra and Relational Calculus aren't that important.
  If you trust that some very smart people did their math correctly,
  you can just trust that the proofs work out.  What is useful and
  important is understanding the Relational Model itself, which gives
  you a much better idea of the fundamental operations behind SQL
  statements, as well as stuff like the Normal Forms and the theory
  behind them.

  I would argue, strongly, that DBAs and database developers that have
  a solid understanding of the Relational Model are much better at what
  they do.  SQL is not purely Relational-- in fact, there are a lot of
  differences-- but having a strong grounding in the Relational Model
  will make you a better SQL developer.

  In a sense, the difference between the Relational Model and SQL is
  like the difference between Object Oriented Programming theory, and
  C++.  If you learn C++, especially from a strictly syntax standpoint,
  you'll pick up a bit on object oriented programming, but you won't
  really *know* OOP.  Similarly, even if you're an expert C++ developer,
  if C++ is you're only OOP language, you still don't really get what
  clean OOP is all about (because C++ sure as heck isn't that, even if
  it is a darn useful language).  So it is with SQL-- darn useful, but
  not quite what the theory is about, and a very foggy glass to try to
  learn the theory through.

  If you want to learn more about the theory and concepts behind SQL, I
  would strongly recommend these two books:

SQL and Relational Theory (2nd Ed) by C.J. Date
http://shop.oreilly.com/product/0636920022879.do

Relational Theory for Computer Professionals by C.J. Date
http://shop.oreilly.com/product/0636920029649.do

  They'll get into some details about the theory, but not to the point
  of actual algebra or Greek math symbols to define the theory.  I
  found them extremely useful in better understanding what I was trying
  to express in SQL, and how to write better, more direct SQL (and
  better database design).

  C.J. Date was one of the people that worked with E.F. Codd to refine
  the whole concept of modern database.  He has some very strong views,
  some of which I don't agree with, but he's a very good writer and
  presenter.  He's also a very, very theory heavy guy that tends to put
  elegance and theory before any practical concern-- which is great for
  research and teaching, but less useful for getting stuff done by a
  deadline.  Still, in the last eight years or so he has visibly
  shifted his stance from something of a "SQL sux and must die" point
  of view to a more relaxed "since you're going to work in SQL anyways,
  you may as well do it right."  And *that* I can agree with.

   -j

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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_column_count vs sqlite_data_count

[Peter Aronson]

It seems like a very subtle difference, but I think sqlite3_column_count will 
return the number of columns returned by a prepared statement regardless of 
whether there is data available to get with sqlite3_column_* functions, whereas 
sqlite3_data_count requires that there be a current result set row available 
due to sqlite3_step most recently returning SQLITE_ROW.
 
Peter

From: Simon Slavin 
>To: General Discussion of SQLite Database  
>Sent: Tuesday, July 9, 2013 5:11 PM
>Subject: Re: [sqlite] Reference to an undefined field
>
>
>By the way, does anyone understand the difference between 
>sqlite3_column_count() and sqlite3_data_count() ?  I mean, I can read the 
>definitions, but why are they both provided ?  Is one more useful than another 
>sometimes ?
>
>Simon.
>___
>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] Reference to an undefined field

[Simon Slavin]

On 9 Jul 2013, at 11:54pm, Igor Korot  wrote:

> Shouldn't the engine warn you?
> What I mean is: developer don't know that he fail with the query results
> until further execution or running the program under debugger and explore
> the data.
> I'm not saying it's wrong, I'm just trying to understand the reasoning...

It would indeed be useful to have the developer warned.  But look what it would 
take in this situation.  The call is

int sqlite3_column_int(sqlite3_stmt*, int iCol)

The normal way the API warns of errors is by returning a value other than 
SQLITE_OK.  But this call is unusual: instead of returning a result code it 
returns the value of the field specified.  So to conform with the normal API 
pattern the call would need to be changed to be more bulky and annoying to use 
which everyone would hate.

So the alternative way of warning of an error is an assert.  But SQLite doesn’t 
use this method of warning (except, IIRC, when a compilation option for 
debugging is set).  And an asset is not trappable by the program calling the 
API.  Which would be nontrappable by the programmer, which is not the way 
SQLite normally does things.  So again, you have somthing which wouldn’t 
conform to the usual SQLite way of doing things.

So there’s no good way to deliver what would be this useful warning without 
changing expectations about the SQLite API.  The only elegant thing to do is to 
rely on the programmer calling sqlite3_column_count() in the weird and unusual 
situation where they don’t know how many columns to expect.

By the way, does anyone understand the difference between 
sqlite3_column_count() and sqlite3_data_count() ?  I mean, I can read the 
definitions, but why are they both provided ?  Is one more useful than another 
sometimes ?

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

Re: [sqlite] Reference to an undefined field

[Igor Korot]

Igor,

On Tue, Jul 9, 2013 at 3:42 PM, Igor Tandetnik  wrote:

> On 7/9/2013 6:37 PM, Igor Korot wrote:
>
>> Hi, ALL,
>> Consider following code:
>>
>> std::string query = "SELECT a FROM foo;";
>>
>> sqlite3_prepare_v2( handle, query, -1, , 0 );
>> sqlite3_step( stmt );
>> int id = sqlite_column_int( stmt, 0 );
>> int code = sqlite3_column_int( stmt, 1 );
>>
>> Shouldn't the engine assert in this case?
>>
>
> http://www.sqlite.org/c3ref/**column_blob.html
> If the SQL statement does not currently point to a valid row, or if the
> column index is out of range, the result is undefined.
>

Shouldn't the engine warn you?
What I mean is: developer don't know that he fail with the query results
until further execution or running the program under debugger and explore
the data.
I'm not saying it's wrong, I'm just trying to understand the reasoning...

Thank you.


>
> --
> Igor Tandetnik
>
> __**_
> 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] Reference to an undefined field

[Igor Tandetnik]

On 7/9/2013 6:37 PM, Igor Korot wrote:

Hi, ALL,
Consider following code:

std::string query = "SELECT a FROM foo;";

sqlite3_prepare_v2( handle, query, -1, , 0 );
sqlite3_step( stmt );
int id = sqlite_column_int( stmt, 0 );
int code = sqlite3_column_int( stmt, 1 );

Shouldn't the engine assert in this case?


http://www.sqlite.org/c3ref/column_blob.html
If the SQL statement does not currently point to a valid row, or if the 
column index is out of range, the result is undefined.


--
Igor Tandetnik

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

[sqlite] Reference to an undefined field

[Igor Korot]

Hi, ALL,
Consider following code:

std::string query = "SELECT a FROM foo;";

sqlite3_prepare_v2( handle, query, -1, , 0 );
sqlite3_step( stmt );
int id = sqlite_column_int( stmt, 0 );
int code = sqlite3_column_int( stmt, 1 );

Shouldn't the engine assert in this case?

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

Re: [sqlite] Literature on the information theory behind SQL(lite)?

[Stephan Beal]

On Tue, Jul 9, 2013 at 7:24 PM, Paolo Bolzoni  wrote:

> I am sorry the part that look Greek to you is actually fairly
> important base of the theory behind SQL, the relational algebra.
>

Bad news for me ;), but you've given me a new search term: relational
algebra.

Thanks :)

-- 
- 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 Use of Indexes

[Simon Slavin]

On 9 Jul 2013, at 6:06pm, peter korinis  wrote:

> So, to implement your suggestion of crafting better indices, here’s my 
> approach:
> 1.   First action is joining the 2 tables on claim_no. {Therefore 
> claim_no should be first row in index for both tables}
> 2.   Find state and county in claims table
> 3.   Find HCPSCD in line table
> So my 2 combined indices would be:
>CREATE INDEX idx_Claim_State_Cnty ON CLAIMS (CLAIM_NO, 
> STATE_CD, CNTY_CD)
>CREATE INDEX idx_Line_hcpscd ON LINE (CLAIM_NO, HCPSCD)

Those would be good indexes.  You can see how good by using EXPLAIN QUERY PLAN 
for your SELECT.  Or better still, actually try them out and time the results.

Might be interesting to then reverse the order of the columns in each index and 
try those.  See if they’re better or worse.  Or create lots of indexes, then 
use EXPLAIN QUERY PLAN and find out which indexes SQLite decided to use, then 
delete the others.

I am deliberately not giving you an absolute answer because you have a good 
large set of data for testing and you obviously understand the idea now.  
You’ll learn more by trying out several alternatives yourself.

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

Re: [sqlite] Literature on the information theory behind SQL(lite)?

[Paolo Bolzoni]

I am sorry the part that look Greek to you is actually fairly
important base of the theory behind SQL, the relational algebra.

A possible answer to your question is the classic book: Database
Systems, The complete book of Ullman et. all.
It is comprehensive, so it should satisfy all your curiosities.


On Tue, Jul 9, 2013 at 7:17 PM, Stephan Beal  wrote:
> Hi, all,
>
> i am looking for literature which describes the data/information
> theory/formalisms behind sqlite and similar projects. Google has so far led
> me to the extremes of "introduction to SQL" (don't need it) and articles
> which start using Greek symbols in the 3rd paragraph (and which point my
> brain shuts down). Can anyone suggest materials somewhere between these two
> extremes? i'm not so much interested in implementation details as i am
> about learning the theory behind the implementation details (after which
> the implementation details will make more sense to me).
>
> :-?
>
> --
> - 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Literature on the information theory behind SQL(lite)?

[Stephan Beal]

Hi, all,

i am looking for literature which describes the data/information
theory/formalisms behind sqlite and similar projects. Google has so far led
me to the extremes of "introduction to SQL" (don't need it) and articles
which start using Greek symbols in the 3rd paragraph (and which point my
brain shuts down). Can anyone suggest materials somewhere between these two
extremes? i'm not so much interested in implementation details as i am
about learning the theory behind the implementation details (after which
the implementation details will make more sense to me).

:-?

-- 
- 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 Use of Indexes

[peter korinis]

Thank you Simon for responding to my questions. Your phonebook (FName/LName)
analogy clearly explained why 2 indices per table per select won't work.

Let me provide a bit more info and a possible attempt to implement your
suggestions for better indices.

 

My 'bread and butter' query counts the frequency of HCPSCD occurrences for
each county of interest in each state of interest. [Occasionally I want
counts for entire state.] 

I generally have 6 groups of  1~15 related HCPSCD codes and want counts from
1-12 counties of a state. 

 

CLAIMS table columns: claim_no, state_cd, cnty_cd are columns of interest;
40 other columns rarely used.

LINE table columns: claim_no, hcpscd, plus 25 more columns (these are
repeating fields per claim)

 

[FYI: DB contains 44M claim numbers with 1~12 HCPSCD codes per claim,
several thousand HCPSCD codes of which I am interested in 44 in 6 groups, 50
states with 9~125 counties per state of which I am usually interested in
~10.]

 

I currently do a query like this .

SELECT COUNT(HCPSCD) CNT, HCPSCD, STATE_CD, CNTY_CD FROM CLAIMS , LINE

WHERE CLAIMS.CLAIM_NO == LINE.CLAIM_NO AND

HCPSCD IN
('78451','78452','78453','78454','78469','78472','78473','78481','78483','78
494','78496','78499')   -- this is first group of hcpscd codes always
queried

AND

STATE_CD = '21'

AND

CNTY_CD IN ('220', '345', '570')

GROUP BY CNTY_CD

UNION {repeat above SELECT with second group of hcpscd codes
for same state and county . and so on 4 more times}

 

So, to implement your suggestion of crafting better indices, here's my
approach:

1.   First action is joining the 2 tables on claim_no. {Therefore
claim_no should be first row in index for both tables}

2.   Find state and county in claims table

3.   Find HCPSCD in line table

So my 2 combined indices would be:

CREATE INDEX idx_Claim_State_Cnty ON CLAIMS (CLAIM_NO,
STATE_CD, CNTY_CD)

CREATE INDEX idx_Line_hcpscd ON LINE (CLAIM_NO, HCPSCD)

 

Is this what you were suggesting? Will these indices produce improved
performance from the single column indices I was using?

Thanks so much.

Peter

===

> 1.   Can SQLite use > 1 index per table per SELECT? Seems like using

> both indices for each table would be better than the single index per
table

> as chosen by the plan.

 

No, you?re right: one index per table, per SELECT.  Pretend you have a phone
book with two indexes: surname and first name.  You have to look up someone
called Miri Kallas.  You can use either of the indexes to look up one name
but once you?ve done that the other index is useless to you.

 

You have made up some indexes which are useful but not the most useful.
Drop those indexes and try to figure out one compound index on each table
which would be best for the SELECT you asked about.  Remember that this

 

CREATE INDEX it1 ON t1 (c1)

CREATE INDEX it2 ON t1 (c2)

 

does not do the same thing as

 

CREATE INDEX it12 ON t1 (c1, c2)

 

Can?t do it for you because I can?t tell which of your columns are from
which table.

 

> 2.   Is using something like "SELECT . FROM LINE INDEXED BY claim_no

> AND INDEXED BY hcpscd" possible? What is the correct syntax to include 2

> INDEXED BY this way?

 

If you have to tell SQLite which index to use you?re doing it wrong.  Make
up a good index and SQLite will decide to use it.  Especially if you?ve done
ANALYZE.

 

> 3.   Is there a better way to write this query, for example, would

> rearranging the order of AND criteria in WHERE clause improve performance?

 

The query optimizer is meant to do all that for you.  However, I suspect
that you may understand your query better if you get rid of some of your
'IN' clauses.  If you imagine doing 24 (= 2 * 12) different SELECTs, one for
each State and HCPSCD, what would your SELECT look like then ?  With good
indexes it should be possible to make each of those SELECTs execute
ridiculously fast.

 

Once you?ve figured out how to do that and make it run fast, /then/ you
might want to recombine the query for each State, though perhaps not put
both States in the same query.

 

> 4.   How do I interpret the 'order' and 'from' in the query plan

> results?

 

It?s showing you what each of your indexes is being used for.  And what is
shows is the neither index is being used for both selecting records and
arranging the order of results.

 

Simon.

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

Re: [sqlite] Unlock Notify problems

[Dan Kennedy]

On 07/09/2013 11:10 PM, Owen Haynes wrote:

Here is some information I have gathered.

We have a writer thread connection called A and a reader called B, another
reader called C.


A is writing
B is waiting on A with pBlockingConnection = A, and pUnlockConnection A
A is Now done,
A is waiting on B with pBlockingConnection = B and pUnlockConnection B
B is now done but never goes to check to see if any connections are waiting
to be unblocked, sqlite3ConnectionUnlocked never gets called. I have a
print out every time it gets called with the connection which calls it.
C then wants to read and has a pBlockingConnection = B and pUnlockConnection =
B.

And all the connections are then locked up.

This information was gathered from pUnlockConnection and pBlockingConnection
and by examining the blocked list. All writes are done using "BEGIN
IMMEDIATE TRANSACTION"


Never called...

What is the value of B->autoCommit?

Also, how do we know that B is actually "done"?

If it has no explicitly opened transaction (i.e. if B->autoCommit==1),
then the sqlite3ConnectionUnlocked() function should be called every
time B halts an SQL statement (which happens either in the
sqlite3_reset() or sqlite3_finalize() call, or sometimes from within
sqlite3_step() if it returns SQLITE_DONE or an error).

Is B calling sqlite3_reset() or sqlite3_finalize() on its statement
handles when it is finished with them?

Dan.







Owen


On 9 July 2013 12:51, Dan Kennedy  wrote:


On 07/09/2013 06:41 PM, Owen Haynes wrote:


I have been using a debugger to look at pBlockedConnection  and
pUnlockConnection
also added some extra print statements to the code, sqlite is also
compiled
with debug enabled, and no asserts seem to get triggered.



How do you know A has finished? Does "PRAGMA lock_status"
indicate that it is holding no locks at all?

So in "deadlock 1", when you have:


Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection B

Then the following are all true?

   B->pUnlockConnection = A;
   B->pBlockingConnection = A;
   C->pUnlockConnection = B;
   C->pBlockingConnection = B;

Are both B and C in the linked list that starts at global
variable sqlite3BlockedList (and connected by pNextBlocked)?

Dan.







Owen




On 9 July 2013 11:20, Dan Kennedy  wrote:

  On 07/09/2013 04:08 PM, Dan Kennedy wrote:

  On 06/20/2013 03:20 PM, Owen Haynes wrote:

  Hello,

I am currently having some problems with the unlock notify and getting
in a
state of deadlock.

I am using code based on 
http://www.sqlite.org/unlock_notify.html


**,

with
the latest sqlite.

The setup is as follows:
- WAL is on
- Multi Threaded is on
- Temp store is memory
- Database is stored in /dev/shm
- Synchronous is set as normal
- Multi-threading is on
- connection is set as SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE

Unlock notify does work I can see my log messages printed when the
callback
is called but sometimes I can end up in the following dead lock
situation.

Each connection is in its own thread

*Deadlock 1*
Connection A is using database
Connection B is waiting on Connection A
Connection C is waiting on Connection B

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection B

  How do you know this is what is happening? Are you looking

at the sqlite3.pBlockedConnection and sqlite3.pUnlockConnection
pointers in a debugger? Or some other method?

Also, if this is easy to reproduce, try running with SQLITE_DEBUG
defined. This will enable some complex asserts that check for
various problems in the unlock-notify code.

Dan.






  *Deadlock 2*

Connection A is using database
Connection B is waiting on Connection A
Connection C is waiting on Connection A

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection A

For some reason sometimes the notify callback does not get issued and
end
up with 2 threads waiting for the callback back to be issued.

Connection B is a writer thread the others only read.

Any ideas?

  Are you handling the case where the xNotify callback is invoked by

sqlite3_unlock_notify() before it returns?

Dan.


  ___

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

Re: [sqlite] Unlock Notify problems

[Owen Haynes]

Here is some information I have gathered.

We have a writer thread connection called A and a reader called B, another
reader called C.


A is writing
B is waiting on A with pBlockingConnection = A, and pUnlockConnection A
A is Now done,
A is waiting on B with pBlockingConnection = B and pUnlockConnection B
B is now done but never goes to check to see if any connections are waiting
to be unblocked, sqlite3ConnectionUnlocked never gets called. I have a
print out every time it gets called with the connection which calls it.
C then wants to read and has a pBlockingConnection = B and pUnlockConnection =
B.

And all the connections are then locked up.

This information was gathered from pUnlockConnection and pBlockingConnection
and by examining the blocked list. All writes are done using "BEGIN
IMMEDIATE TRANSACTION"

Owen


On 9 July 2013 12:51, Dan Kennedy  wrote:

> On 07/09/2013 06:41 PM, Owen Haynes wrote:
>
>> I have been using a debugger to look at pBlockedConnection  and
>> pUnlockConnection
>> also added some extra print statements to the code, sqlite is also
>> compiled
>> with debug enabled, and no asserts seem to get triggered.
>>
>
>
> How do you know A has finished? Does "PRAGMA lock_status"
> indicate that it is holding no locks at all?
>
> So in "deadlock 1", when you have:
>
>
> Connection A is has finished
> Connection B is waiting on Connection A
> Connection C is waiting on Connection B
>
> Then the following are all true?
>
>   B->pUnlockConnection = A;
>   B->pBlockingConnection = A;
>   C->pUnlockConnection = B;
>   C->pBlockingConnection = B;
>
> Are both B and C in the linked list that starts at global
> variable sqlite3BlockedList (and connected by pNextBlocked)?
>
> Dan.
>
>
>
>
>
>
>> Owen
>>
>>
>>
>>
>> On 9 July 2013 11:20, Dan Kennedy  wrote:
>>
>>  On 07/09/2013 04:08 PM, Dan Kennedy wrote:
>>>
>>>  On 06/20/2013 03:20 PM, Owen Haynes wrote:

  Hello,
>
> I am currently having some problems with the unlock notify and getting
> in a
> state of deadlock.
>
> I am using code based on 
> http://www.sqlite.org/unlock_notify.html
> 
> >**,
>
> with
> the latest sqlite.
>
> The setup is as follows:
>- WAL is on
>- Multi Threaded is on
>- Temp store is memory
>- Database is stored in /dev/shm
>- Synchronous is set as normal
>- Multi-threading is on
>- connection is set as SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
> SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE
>
> Unlock notify does work I can see my log messages printed when the
> callback
> is called but sometimes I can end up in the following dead lock
> situation.
>
> Each connection is in its own thread
>
> *Deadlock 1*
> Connection A is using database
> Connection B is waiting on Connection A
> Connection C is waiting on Connection B
>
> Connection A is has finished
> Connection B is waiting on Connection A
> Connection C is waiting on Connection B
>
>  How do you know this is what is happening? Are you looking
>>> at the sqlite3.pBlockedConnection and sqlite3.pUnlockConnection
>>> pointers in a debugger? Or some other method?
>>>
>>> Also, if this is easy to reproduce, try running with SQLITE_DEBUG
>>> defined. This will enable some complex asserts that check for
>>> various problems in the unlock-notify code.
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>>
>>>
>>>  *Deadlock 2*
> Connection A is using database
> Connection B is waiting on Connection A
> Connection C is waiting on Connection A
>
> Connection A is has finished
> Connection B is waiting on Connection A
> Connection C is waiting on Connection A
>
> For some reason sometimes the notify callback does not get issued and
> end
> up with 2 threads waiting for the callback back to be issued.
>
> Connection B is a writer thread the others only read.
>
> Any ideas?
>
>  Are you handling the case where the xNotify callback is invoked by
 sqlite3_unlock_notify() before it returns?

 Dan.


  ___
>>> 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] Convert Sqlserver script to Sqlite script

[veeresh kumar]

Hi,
 Is there any tool which would convert a sql server script to sqlite script? 

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

Re: [sqlite] Unstable ON DELETE actions

[Roland Wilczek]

> Igor wrote:
> So don't create two foreign keys that come into conflict.

I would never do, but the users of my tool could make that mistake.
And then my tool must do, what SQLite does.

Problem: SQLite's behaviour is hard to predict and tends to surprise you.
Thats the reason for my questions: Can I hope for better documentation or 
fewer surprises?

-- 
mit freundlichen Grüßen

- Roland Wilczek
Certified ScrumMaster (CSM)

Nachtigallenstraße 11
53179 Bonn

Tel.: 0228 / 336 70 40 9
Mobil: 0171 / 72 36 849
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unstable ON DELETE actions

[Clemens Ladisch]

I wrote:
> Roland Wilczek wrote:
>> CREATE TABLE track (artist,
>> FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE
>> FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
>
> This particular statement creates a table with a single foreign key
> constraint.

Sorry, I was wrong:
sqlite> pragma foreign_key_list(track);
0|0|artist|artist|id|NO ACTION|RESTRICT|NONE
1|0|artist|artist|id|NO ACTION|CASCADE|NONE


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

Re: [sqlite] Unstable ON DELETE actions

[Roland Wilczek]

> Clemens wrote:
> > 
> > CREATE TABLE track (artist,
> > FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE
> > FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
> 
> SQLite allows pretty much anything inside a CREATE TABLE statement, and
> ignores anything it doesn't recognize.
> 
> This particular statement creates a table with a single foreign key
> constraint.  Table constraints must be separated with commas.

Just separated the constraints with comma instead of new line. 
No test changed it's behaviour.
-- 
kind regards

- Roland Wilczek
Certified ScrumMaster (CSM)

Nachtigallenstraße 11
53179 Bonn

Tel.: 0228 / 336 70 40 9
Mobil: 0171 / 72 36 849
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unstable ON DELETE actions

[Clemens Ladisch]

Roland Wilczek wrote:
> - If two foreign keys come into conflict, SQLite silently ignores one of them
>   instead of raising an error.
>
> CREATE TABLE track (artist,
> FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE
> FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);

SQLite allows pretty much anything inside a CREATE TABLE statement, and
ignores anything it doesn't recognize.

This particular statement creates a table with a single foreign key
constraint.  Table constraints must be separated with commas.


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

Re: [sqlite] Unstable ON DELETE actions

[Igor Tandetnik]

On 7/9/2013 8:50 AM, Roland Wilczek wrote:

- The order of execution of ON DELETE action is not well documented.


As far as I can tell, it's unspecified, subject to change without 
notice, and should be treated as unpredictable. If you want a 
deterministic order, create a single ON DELETE trigger that executes 
several statements in the desired order.



- If two foreign keys come into conflict, SQLite silently ignores one of them
   instead of raising an error.


So don't create two foreign keys that come into conflict.
--
Igor Tandetnik

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

[sqlite] Unstable ON DELETE actions

[Roland Wilczek]

Hi *,

I am sorry to introduce myself to this list with such a long posting.
And I am even more sorry to post such critical content! Forgive me.

I am developing an ORM-tool, which as a part of it's tasks, emulates
ON DELETE actions of the underlying RDBMS.
Accidentally I came across some wierdnesses in SQLite.
After studying the documentation without satisfying results, I started to
write a bunch of automated exploration tests.

My conclusion is:
- The order of execution of ON DELETE action is not well documented.
- It often relies on the order of declaration in the DDL.
- The DDL's impact on the order of execution may vary.
- If two foreign keys come into conflict, SQLite silently ignores one of them
  instead of raising an error.

Even with a more detailled documentation, I find the implementation
of ON DELETE actions in a way "unstable" and "risky".

Unstable, for it depends on the manifestation of a DDL.
The order of foreign key declarations within an DDL however can easily be
changed, which sometimes results in an surprising change of SQLite's 
behaviour.
Think of tools auto-generating DDL from some user-defined metadata.

Risky, for the implicit skipping of actions risks the user's data (especially, 
but not limited to, when ON DELETE RESTRICT is skipped).

My questions are: 
Have I actually to try to emulate SQLite's current behaviour? 
If so: Is there hope for more detailled documentation?
Or is there hope for another implementation?

Here is the SQL-output of my tests.
It contains some, outlining the problems I see.

The SQLite version is 3.7.16.

/*
 * Set 1.
 *
 * A table "track" declares two different foreign keys to a table "artist".
 *
 * In Set 1a, those foreign keys are built using the same column.
 * In Set 1b, each foreign key is built using a different column.
 *
 * Obviously, order of foreign key declarations often matters.
 *
 * The sometimes puzzling results could be avoided, if SQLite would prevent
 * the declaration of conflicting foreign keys from one table to another.
 */

/*
 * Set 1a.
 * ON DELETE conflicts with two foreign keys built using the same column.
 *
 * CASCADE  vs. RESTRICT: RESTRICT wins
 * RESTRICT vs. CASCADE : CASCADE  wins
 * RESTRICT vs. SET NULL: SET NULL wins
 * SET NULL vs. RESTRICT: RESTRICT wins
 * SET NULL vs. CASCADE : CASCADE  wins
 * CASCADE  vs. SET NULL: SET NULL wins
 *
 * Obviously, the order of declaration is decisive. The first declaration
 * is ignored; the second one matters.
 */

-- CASCADE vs. RESTRICT: RESTRICT wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE track (artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
INSERT INTO track VALUES(1);
DELETE FROM artist  /* Error: foreign key constraint failed */;
SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */;
SELECT COUNT(*) AS result FROM track WHERE artist = 1; /* Result: 1 */;

-- RESTRICT vs. CASCADE: CASCADE wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE track (artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE);
INSERT INTO track VALUES(1);
DELETE FROM artist;
SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */;
SELECT COUNT(*) AS result FROM track ; /* Result: 0 */;

-- RESTRICT vs. SET NULL: SET NULL wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE track (artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL);
INSERT INTO track VALUES(1);
DELETE FROM artist;
SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */;
SELECT COUNT(*) AS result FROM track WHERE artist IS NULL; /* Result: 1 */;

-- SET NULL vs. RESTRICT: RESTRICT wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE track (artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE RESTRICT);
INSERT INTO track VALUES(1);
DELETE FROM artist  /* Error: foreign key constraint failed */;
SELECT COUNT(*) AS result FROM artist ; /* Result: 1 */;
SELECT COUNT(*) AS result FROM track WHERE artist = 1; /* Result: 1 */;

-- SET NULL vs. CASCADE: CASCADE wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist VALUES(1);
CREATE TABLE track (artist,
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE SET NULL
FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE);
INSERT INTO track VALUES(1);
DELETE FROM artist;
SELECT COUNT(*) AS result FROM artist ; /* Result: 0 */;
SELECT COUNT(*) AS result FROM track ; /* Result: 0 */;

-- CASCADE vs. SET NULL: SET NULL wins
PRAGMA foreign_keys = ON;
CREATE TABLE artist (id PRIMARY KEY);
INSERT INTO artist 

Re: [sqlite] Android : UNIQUE makes my DB go crazy

[Clemens Ladisch]

Sorin Grecu wrote:
> I'm having an issue with my app.

Already solved:

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

[sqlite] Android : UNIQUE makes my DB go crazy

[Sorin Grecu]

I'm having an issue with my app.
What my app does is this : gets some data from a couple of edittexts(3 per 
row,created dynamically) and puts them in a database.

What i want the database to do is this : take the `product name`,the `quantity` 
and the `price` and put them in the table.The name should be `UNIQUE`(it will 
be used to power an autocomplete,it needs to be unique not to have duplicates 
in the AC list).The price in the database must be the last price inserted for 
that product(for example,if `Cheese` at 3$ is inserted and after that `Cheese` 
at 2.5$ in the database we will find 2.5$).The quantity has to be summed up(if 
i enter Cheese in quantity 3 and then again Cheese in quantity 4 in the 
database we will find 7).



Now,my issue : Lets say i enter this in my shopping list :

     1.  Hhhh42.5
     2. Dddd31
     3. Eeee2   2
     4. Aaaa    5 3.5

In my database I will find this :

    4.      Aaaa42.5
    2.    Dddd 31
    3.    Eeee22
    1.    Hhhh53.5


So,the issue is that it arranges the product name column alphabetically but the 
other columns remain in the same order,the one i entered in the edittexts.
I did some tests,if i remove the `UNIQUE` from the product name column,it will 
enter it as it should but of course,it will create duplicates,which i don't 
need.I don't get it,what's wrong ? why does `UNIQUE` trigger this ? 

Here's my code :

My table creation :

    public class SQLiteCountryAssistant extends SQLiteOpenHelper {
private static final String DB_NAME = "usingsqlite.db";
private static final int DB_VERSION_NUMBER = 1;
private static final String DB_TABLE_NAME = "countries";
private static final String DB_COLUMN_1_NAME = "country_name";
private static final String DB_COLUMN_2_NAME = "country_counter";
private static final String DB_COLUMN_3_NAME = "country_price";

private static final String DB_CREATE_SCRIPT = "create table "
+ DB_TABLE_NAME
+ " (_id INTEGER PRIMARY KEY,country_name text unique, country_quantity REAL 
DEFAULT '0',country_price REAL);) ";
private SQLiteDatabase sqliteDBInstance = null;

public SQLiteCountryAssistant(Context context) {
super(context, DB_NAME, null, DB_VERSION_NUMBER);
}

    @Override
public void onCreate(SQLiteDatabase sqliteDBInstance) {
Log.i("onCreate", "Creating the database...");
sqliteDBInstance.execSQL(DB_CREATE_SCRIPT);
}

My insert method :


    public void insertCountry(String countryName, String countryPrice,
String countryQuantity) {

sqliteDBInstance.execSQL("INSERT OR IGNORE INTO " + DB_TABLE_NAME
+ "(country_name, country_quantity, country_price) VALUES('"
+ countryName + "','0', '" + countryPrice + "')");
sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME
+ " SET country_name='" + countryName
+ "', country_quantity=country_quantity+'" + countryQuantity
+ "' WHERE country_name='" + countryName + "';");
sqliteDBInstance.execSQL("UPDATE " + DB_TABLE_NAME
+ " SET country_name='" + countryName + "', country_price='"
+ countryPrice + "' WHERE country_name='" + countryName + "';");
}


And this is how i call the insert method :


for (int g = 0; g < allcant.size() - 1; g++) {
if (prod[g] != "0.0") {
sqlliteCountryAssistant.insertCountry(prod[g],pret[g],cant[g]);
}

Also,please excuse my messy code,i've started learning android with no 
programming background like a month ago.I just got my bachelors degree in 
Sociology so yea,i'm an absolute beginner.If there is way to do it better then 
i did and i'm pretty sure there is,please,show me the way,heh.

Thanks and have a good day !
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] adding some more details on the documentation

[Karim DRIDI]

Hello,

I had some  difficulties using sqlite3_create_function in that example : 
https://gist.github.com/kdridi/621a12f9a7d6ac12309a
Using sqlite v3.7.17 and -std=c++11

After looking around, i finally understood that the sixth, seventh and eighth 
parameters, xFunc, xStep and xFinal must be
- xFunc, NULL, NULL
- NULL, xStep, xFinal
- NULL, NULL, NULL

However, my example returns an SQLITE_MISUSE code and according to the 
documentation located at http://www.sqlite.org/c3ref/create_function.html i 
thought that i had an encoding error with the second parameter.

It would be useful if the documentation mention that error code in the sixth, 
seventh and eighth parameters paragraph.

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

Re: [sqlite] Unlock Notify problems

[Dan Kennedy]

On 07/09/2013 06:41 PM, Owen Haynes wrote:

I have been using a debugger to look at pBlockedConnection  and
pUnlockConnection
also added some extra print statements to the code, sqlite is also compiled
with debug enabled, and no asserts seem to get triggered.



How do you know A has finished? Does "PRAGMA lock_status"
indicate that it is holding no locks at all?

So in "deadlock 1", when you have:

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection B

Then the following are all true?

  B->pUnlockConnection = A;
  B->pBlockingConnection = A;
  C->pUnlockConnection = B;
  C->pBlockingConnection = B;

Are both B and C in the linked list that starts at global
variable sqlite3BlockedList (and connected by pNextBlocked)?

Dan.







Owen




On 9 July 2013 11:20, Dan Kennedy  wrote:


On 07/09/2013 04:08 PM, Dan Kennedy wrote:


On 06/20/2013 03:20 PM, Owen Haynes wrote:


Hello,

I am currently having some problems with the unlock notify and getting
in a
state of deadlock.

I am using code based on 
http://www.sqlite.org/unlock_**notify.html,
with
the latest sqlite.

The setup is as follows:
   - WAL is on
   - Multi Threaded is on
   - Temp store is memory
   - Database is stored in /dev/shm
   - Synchronous is set as normal
   - Multi-threading is on
   - connection is set as SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE

Unlock notify does work I can see my log messages printed when the
callback
is called but sometimes I can end up in the following dead lock
situation.

Each connection is in its own thread

*Deadlock 1*
Connection A is using database
Connection B is waiting on Connection A
Connection C is waiting on Connection B

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection B


How do you know this is what is happening? Are you looking
at the sqlite3.pBlockedConnection and sqlite3.pUnlockConnection
pointers in a debugger? Or some other method?

Also, if this is easy to reproduce, try running with SQLITE_DEBUG
defined. This will enable some complex asserts that check for
various problems in the unlock-notify code.

Dan.







*Deadlock 2*
Connection A is using database
Connection B is waiting on Connection A
Connection C is waiting on Connection A

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection A

For some reason sometimes the notify callback does not get issued and end
up with 2 threads waiting for the callback back to be issued.

Connection B is a writer thread the others only read.

Any ideas?


Are you handling the case where the xNotify callback is invoked by
sqlite3_unlock_notify() before it returns?

Dan.



__**_
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unlock Notify problems

[Owen Haynes]

I have been using a debugger to look at pBlockedConnection  and
pUnlockConnection
also added some extra print statements to the code, sqlite is also compiled
with debug enabled, and no asserts seem to get triggered.

Owen




On 9 July 2013 11:20, Dan Kennedy  wrote:

> On 07/09/2013 04:08 PM, Dan Kennedy wrote:
>
>> On 06/20/2013 03:20 PM, Owen Haynes wrote:
>>
>>> Hello,
>>>
>>> I am currently having some problems with the unlock notify and getting
>>> in a
>>> state of deadlock.
>>>
>>> I am using code based on 
>>> http://www.sqlite.org/unlock_**notify.html,
>>> with
>>> the latest sqlite.
>>>
>>> The setup is as follows:
>>>   - WAL is on
>>>   - Multi Threaded is on
>>>   - Temp store is memory
>>>   - Database is stored in /dev/shm
>>>   - Synchronous is set as normal
>>>   - Multi-threading is on
>>>   - connection is set as SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
>>> SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE
>>>
>>> Unlock notify does work I can see my log messages printed when the
>>> callback
>>> is called but sometimes I can end up in the following dead lock
>>> situation.
>>>
>>> Each connection is in its own thread
>>>
>>> *Deadlock 1*
>>> Connection A is using database
>>> Connection B is waiting on Connection A
>>> Connection C is waiting on Connection B
>>>
>>> Connection A is has finished
>>> Connection B is waiting on Connection A
>>> Connection C is waiting on Connection B
>>>
>>
> How do you know this is what is happening? Are you looking
> at the sqlite3.pBlockedConnection and sqlite3.pUnlockConnection
> pointers in a debugger? Or some other method?
>
> Also, if this is easy to reproduce, try running with SQLITE_DEBUG
> defined. This will enable some complex asserts that check for
> various problems in the unlock-notify code.
>
> Dan.
>
>
>
>
>
>
>>> *Deadlock 2*
>>> Connection A is using database
>>> Connection B is waiting on Connection A
>>> Connection C is waiting on Connection A
>>>
>>> Connection A is has finished
>>> Connection B is waiting on Connection A
>>> Connection C is waiting on Connection A
>>>
>>> For some reason sometimes the notify callback does not get issued and end
>>> up with 2 threads waiting for the callback back to be issued.
>>>
>>> Connection B is a writer thread the others only read.
>>>
>>> Any ideas?
>>>
>>
>> Are you handling the case where the xNotify callback is invoked by
>> sqlite3_unlock_notify() before it returns?
>>
>> Dan.
>>
>>
> __**_
> 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] Unlock Notify problems

[Dan Kennedy]

On 07/09/2013 04:08 PM, Dan Kennedy wrote:

On 06/20/2013 03:20 PM, Owen Haynes wrote:

Hello,

I am currently having some problems with the unlock notify and 
getting in a

state of deadlock.

I am using code based on http://www.sqlite.org/unlock_notify.html, with
the latest sqlite.

The setup is as follows:
  - WAL is on
  - Multi Threaded is on
  - Temp store is memory
  - Database is stored in /dev/shm
  - Synchronous is set as normal
  - Multi-threading is on
  - connection is set as SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE

Unlock notify does work I can see my log messages printed when the 
callback
is called but sometimes I can end up in the following dead lock 
situation.


Each connection is in its own thread

*Deadlock 1*
Connection A is using database
Connection B is waiting on Connection A
Connection C is waiting on Connection B

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection B


How do you know this is what is happening? Are you looking
at the sqlite3.pBlockedConnection and sqlite3.pUnlockConnection
pointers in a debugger? Or some other method?

Also, if this is easy to reproduce, try running with SQLITE_DEBUG
defined. This will enable some complex asserts that check for
various problems in the unlock-notify code.

Dan.






*Deadlock 2*
Connection A is using database
Connection B is waiting on Connection A
Connection C is waiting on Connection A

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection A

For some reason sometimes the notify callback does not get issued and 
end

up with 2 threads waiting for the callback back to be issued.

Connection B is a writer thread the others only read.

Any ideas?


Are you handling the case where the xNotify callback is invoked by
sqlite3_unlock_notify() before it returns?

Dan.



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

Re: [sqlite] Unlock Notify problems

[Owen Haynes]

Yes I am covering the case where xNotify is called before sqlite3_unlock_notify
is returned.

My struct which I pass into sqlite3_unlock_notify has a fired flag, this
would then get set to true if xNotify is called in sqlite3_unlock_notify.
This flag is then checked after the sqlite3_unlock_notify call to see if it
has changed from false.

I do near enough a carbon copy of the example code on
http://www.sqlite.org/unlock_notify.html.


Owen



On 9 July 2013 10:08, Dan Kennedy  wrote:

> On 06/20/2013 03:20 PM, Owen Haynes wrote:
>
>> Hello,
>>
>> I am currently having some problems with the unlock notify and getting in
>> a
>> state of deadlock.
>>
>> I am using code based on 
>> http://www.sqlite.org/unlock_**notify.html,
>> with
>> the latest sqlite.
>>
>> The setup is as follows:
>>   - WAL is on
>>   - Multi Threaded is on
>>   - Temp store is memory
>>   - Database is stored in /dev/shm
>>   - Synchronous is set as normal
>>   - Multi-threading is on
>>   - connection is set as SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
>> SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE
>>
>> Unlock notify does work I can see my log messages printed when the
>> callback
>> is called but sometimes I can end up in the following dead lock situation.
>>
>> Each connection is in its own thread
>>
>> *Deadlock 1*
>>
>> Connection A is using database
>> Connection B is waiting on Connection A
>> Connection C is waiting on Connection B
>>
>> Connection A is has finished
>> Connection B is waiting on Connection A
>> Connection C is waiting on Connection B
>>
>> *Deadlock 2*
>>
>> Connection A is using database
>> Connection B is waiting on Connection A
>> Connection C is waiting on Connection A
>>
>> Connection A is has finished
>> Connection B is waiting on Connection A
>> Connection C is waiting on Connection A
>>
>> For some reason sometimes the notify callback does not get issued and end
>> up with 2 threads waiting for the callback back to be issued.
>>
>> Connection B is a writer thread the others only read.
>>
>> Any ideas?
>>
>
> Are you handling the case where the xNotify callback is invoked by
> sqlite3_unlock_notify() before it returns?
>
> Dan.
>
> __**_
> 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] Unlock Notify problems

[Dan Kennedy]

On 06/20/2013 03:20 PM, Owen Haynes wrote:

Hello,

I am currently having some problems with the unlock notify and getting in a
state of deadlock.

I am using code based on http://www.sqlite.org/unlock_notify.html, with
the latest sqlite.

The setup is as follows:
  - WAL is on
  - Multi Threaded is on
  - Temp store is memory
  - Database is stored in /dev/shm
  - Synchronous is set as normal
  - Multi-threading is on
  - connection is set as SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE

Unlock notify does work I can see my log messages printed when the callback
is called but sometimes I can end up in the following dead lock situation.

Each connection is in its own thread

*Deadlock 1*
Connection A is using database
Connection B is waiting on Connection A
Connection C is waiting on Connection B

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection B

*Deadlock 2*
Connection A is using database
Connection B is waiting on Connection A
Connection C is waiting on Connection A

Connection A is has finished
Connection B is waiting on Connection A
Connection C is waiting on Connection A

For some reason sometimes the notify callback does not get issued and end
up with 2 threads waiting for the callback back to be issued.

Connection B is a writer thread the others only read.

Any ideas?


Are you handling the case where the xNotify callback is invoked by
sqlite3_unlock_notify() before it returns?

Dan.

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

Re: [sqlite] Unlock Notify problems

[Owen Haynes]

I am still having this problem, and it happens very frequently.

Some more information

Normally have 7 connections on different threads, 2 of these threads are
writers

Owen
On 20 Jun 2013 09:20, "Owen Haynes"  wrote:

> Hello,
>
> I am currently having some problems with the unlock notify and getting in
> a state of deadlock.
>
> I am using code based on http://www.sqlite.org/unlock_notify.html, with
> the latest sqlite.
>
> The setup is as follows:
>  - WAL is on
>  - Multi Threaded is on
>  - Temp store is memory
>  - Database is stored in /dev/shm
>  - Synchronous is set as normal
>  - Multi-threading is on
>  - connection is set as SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
> SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE
>
> Unlock notify does work I can see my log messages printed when the
> callback is called but sometimes I can end up in the following dead lock
> situation.
>
> Each connection is in its own thread
>
> *Deadlock 1*
> Connection A is using database
> Connection B is waiting on Connection A
> Connection C is waiting on Connection B
>
> Connection A is has finished
> Connection B is waiting on Connection A
> Connection C is waiting on Connection B
>
> *Deadlock 2*
> Connection A is using database
> Connection B is waiting on Connection A
> Connection C is waiting on Connection A
>
> Connection A is has finished
> Connection B is waiting on Connection A
> Connection C is waiting on Connection A
>
> For some reason sometimes the notify callback does not get issued and end
> up with 2 threads waiting for the callback back to be issued.
>
> Connection B is a writer thread the others only read.
>
> Any ideas?
>
> Owen
>
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users