Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Keith Medcalf]

If they are both using the same connection, yes.  Transaction state is an 
attribute of the connection, not the statement or thread.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of pisymbol .
>Sent: Thursday, 28 September, 2017 13:02
>To: SQLite mailing list
>Subject: Re: [sqlite] FULLMUTEX and exclusive transactions between
>threads
>
>On Thu, Sep 28, 2017 at 2:10 PM, Simon Slavin 
>wrote:
>
>>
>>
>> On 28 Sep 2017, at 6:57pm, Keith Medcalf 
>wrote:
>>
>> > The mutex is used to prevent multiple concurrent entry (ie,
>calling an
>> sqlite3_* function) using the same connection from multiple
>threads.  (ie,
>> serialization).  It does not provide isolation of any sort. THat is
>to say
>> that you can prepare a statement on a connection and then step it
>on
>> another and finzalize on a third.  What you cannot do (and what
>> serialization prevents) is have two threads make calls at the same
>time on
>> the same connection object.
>>
>
>So you can still have issues with thread 1 issuing a "BEGIN" and then
>thread 2 issuing another "BEGIN" before thread 1 finalizes the
>transaction
>causing failure. Correct?
>
>-aps
>___
>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] FULLMUTEX and exclusive transactions between threads

[Jens Alfke]

> On Sep 28, 2017, at 10:41 AM, pisymbol .  wrote:
> 
> If you are using two different connections, then they both have to be
> opened with full mutex and then SQLite will serial accordingly. Is that
> right?

SQLite connections are completely independent of each other; they share no 
data* or file descriptors. There are no thread-safety issues between SQLite 
calls made on different connections. If you open multiple connections, you 
don’t have to use any of SQLite’s compile-time or runtime mutex support ... as 
long as those connections are used on separate threads. (I.e. no two threads 
ever make a SQLite call with the same connection at the same time.)

—Jens

* unless you do something unusual like using shared-cache mode
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[pisymbol .]

On Thu, Sep 28, 2017 at 3:11 PM, Igor Korot  wrote:

> Not if you use connection-per-thread model.
>
>
Yes, right. That I understand and am in the midst of doing that right now.

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Igor Korot]

Not if you use connection-per-thread model.

On Thu, Sep 28, 2017 at 3:07 PM, Simon Slavin  wrote:
>
>
> On 28 Sep 2017, at 8:01pm, pisymbol .  wrote:
>
>> So you can still have issues with thread 1 issuing a "BEGIN" and then
>> thread 2 issuing another "BEGIN" before thread 1 finalizes the transaction
>> causing failure. Correct?
>
> Don’t know.  Hope someone else does.
>
> Simon.
> ___
> 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] FULLMUTEX and exclusive transactions between threads

[Simon Slavin]

On 28 Sep 2017, at 8:01pm, pisymbol .  wrote:

> So you can still have issues with thread 1 issuing a "BEGIN" and then
> thread 2 issuing another "BEGIN" before thread 1 finalizes the transaction
> causing failure. Correct?

Don’t know.  Hope someone else does.

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[pisymbol .]

On Thu, Sep 28, 2017 at 2:10 PM, Simon Slavin  wrote:

>
>
> On 28 Sep 2017, at 6:57pm, Keith Medcalf  wrote:
>
> > The mutex is used to prevent multiple concurrent entry (ie, calling an
> sqlite3_* function) using the same connection from multiple threads.  (ie,
> serialization).  It does not provide isolation of any sort. THat is to say
> that you can prepare a statement on a connection and then step it on
> another and finzalize on a third.  What you cannot do (and what
> serialization prevents) is have two threads make calls at the same time on
> the same connection object.
>

So you can still have issues with thread 1 issuing a "BEGIN" and then
thread 2 issuing another "BEGIN" before thread 1 finalizes the transaction
causing failure. Correct?

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Simon Slavin]

On 28 Sep 2017, at 6:57pm, Keith Medcalf  wrote:

> The mutex is used to prevent multiple concurrent entry (ie, calling an 
> sqlite3_* function) using the same connection from multiple threads.  (ie, 
> serialization).  It does not provide isolation of any sort. THat is to say 
> that you can prepare a statement on a connection and then step it on another 
> and finzalize on a third.  What you cannot do (and what serialization 
> prevents) is have two threads make calls at the same time on the same 
> connection object.

Ah, that makes more sense.  Believe Keith about this, not what I posted earlier.

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Keith Medcalf]

>> The follow-up to this is what is the purpose of full mutex mode and when
>> should it be used over no mutex mode?

>The mutex is useful when SQLite can tell that your commands are
>actually part of two separate efforts.  i.e. when you’re using two
>different connections.

The mutex is used to prevent multiple concurrent entry (ie, calling an 
sqlite3_* function) using the same connection from multiple threads.  (ie, 
serialization).  It does not provide isolation of any sort.  THat is to say 
that you can prepare a statement on a connection and then step it on another 
and finzalize on a third.  What you cannot do (and what serialization prevents) 
is have two threads make calls at the same time on the same connection object.

You would use no mutex mode if YOUR CODE ensures that there will never be an 
attempt at concurrent entry on a connection across threads.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>
>
>Simon.
>___
>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] FULLMUTEX and exclusive transactions between threads

[Simon Slavin]

On 28 Sep 2017, at 6:41pm, pisymbol .  wrote:

> If you are using two different connections, then they both have to be
> opened with full mutex and then SQLite will serial accordingly. Is that
> right?

You’re right at the edge of my competency but I believe that is correct.  My 
understanding is that both connections have to be told to respect the mutex.  
However this should be the default setting on _open(), since SQLite tends to 
have defaults which give the safest results.

The closest I know in the documentation is



"If the SQLITE_OPEN_NOMUTEX flag is set, then the database connection opens in 
the multi-thread threading mode as long as the single-thread mode has not been 
set at compile-time or start-time. If the SQLITE_OPEN_FULLMUTEX flag is set 
then the database connection opens in the serialized threading mode unless 
single-thread was previously selected at compile-time or start-time."

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Keith Medcalf]

Transactions are an attribute of the connection and the threads are irrelevant.

So once you BEGIN a transaction on a connection, that connection is inside a 
transaction for any and all threads that may happen to use that connection.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of pisymbol .
>Sent: Thursday, 28 September, 2017 11:16
>To: SQLite mailing list
>Subject: Re: [sqlite] FULLMUTEX and exclusive transactions between
>threads
>
>On Thu, Sep 28, 2017 at 11:55 AM, Simon Slavin 
>wrote:
>
>>
>>
>> On 28 Sep 2017, at 3:31pm, pisymbol .  wrote:
>>
>> > Specificially, if thread 1 and 2 both have a handle to sqlite3
>with full
>> > mutex, then both could start a transaction simultaneously, one
>will win
>> the
>> > other will wait, [snip]
>>
>> By "serialised" the documentation means that no two SQL commands
>will be
>> allowed to take place at the same time.  But SQL can’t separate
>your two
>> different threads into two different sources of commands.  As far
>as its
>> concerned, anything which uses your single connection is part fo
>the same
>> command-stream.  So if one part of your program executes BEGIN then
>> everything from any source from then onwards is part of the same
>> transaction until some part of your program executes "COMMIT".
>>
>> If you want your program to execute two different transactions at
>the same
>> time (and handle the bookkeeping necessary to make one wait for the
>other
>> to complete) you must use two different connections.  And if you
>have three
>> independent parts of your program which may all depend on
>transaction
>> isolation, you need three connections
>>
>
>So even with full mutex, if I have thread 1 issue a "BEGIN" and it
>starts
>processing, and another thread 2 issues a "BEGIN" somewhere in
>between
>that, the other thread will just flat out fail because a transaction
>has
>already started for thread 1. Is that correct?
>
>MAN, the doc is very unclear about this.
>
>
>> > I can't find where a single thread
>> > of execution could issue "BEGIN" twice in my code though I did
>find that
>> I
>> > was calling sqlite3_lastrow_insert() twice in a row by accident
>but I
>> don't
>> > know how that could cause the above.)
>>
>> Do you use _prepare(), _step(), _finalize() for a multi-row SELECT
>> anywhere ?  What probably happened is that one thread executed
>_step() for
>> a multi-row SELECT.  That means it is in the middle of a SELECT
>command, so
>> it wouldn’t have released its lock on the database.Only when a
>_step()
>> command has reached the end of the rows and returned SQLITE_DONE
>(or an
>> error) is the SQL command over, at which point SQLite can end the
>> transaction.
>>
>
>Before I answer that, can you please confirm the above?
>
>-aps
>___
>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] FULLMUTEX and exclusive transactions between threads

[pisymbol .]

On Thu, Sep 28, 2017 at 1:30 PM, Simon Slavin  wrote:

>
>
> On 28 Sep 2017, at 6:16pm, pisymbol .  wrote:
>
> > So even with full mutex, if I have thread 1 issue a "BEGIN" and it starts
> > processing, and another thread 2 issues a "BEGIN" somewhere in between
> > that, the other thread will just flat out fail because a transaction has
> > already started for thread 1. Is that correct?
>
> Correct.  Connections are the way into SQLite.  Because both threads are
> using the same connection, SQLite thinks they’re part of the same effort.
> So as far as SQLite is concerned, the two threads are supplying SQL
> commands for the same transaction.
>
> As far as SQLite is concerned, you have one connection which executed
> BEGIN and then executed BEGIN again without having ended the first
> transaction.
>

Then I understand why my error occurred. This is a bummer. I will have to
readjust.


>
> On 28 Sep 2017, at 6:23pm, pisymbol .  wrote:
>
> > The follow-up to this is what is the purpose of full mutex mode and when
> > should it be used over no mutex mode?
>
> The mutex is useful when SQLite can tell that your commands are actually
> part of two separate efforts.  i.e. when you’re using two different
> connections.
>
>
If you are using two different connections, then they both have to be
opened with full mutex and then SQLite will serial accordingly. Is that
right?

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Simon Slavin]

On 28 Sep 2017, at 6:16pm, pisymbol .  wrote:

> So even with full mutex, if I have thread 1 issue a "BEGIN" and it starts
> processing, and another thread 2 issues a "BEGIN" somewhere in between
> that, the other thread will just flat out fail because a transaction has
> already started for thread 1. Is that correct?

Correct.  Connections are the way into SQLite.  Because both threads are using 
the same connection, SQLite thinks they’re part of the same effort.  So as far 
as SQLite is concerned, the two threads are supplying SQL commands for the same 
transaction.

As far as SQLite is concerned, you have one connection which executed BEGIN and 
then executed BEGIN again without having ended the first transaction.

On 28 Sep 2017, at 6:23pm, pisymbol .  wrote:

> The follow-up to this is what is the purpose of full mutex mode and when
> should it be used over no mutex mode?

The mutex is useful when SQLite can tell that your commands are actually part 
of two separate efforts.  i.e. when you’re using two different connections.


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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[pisymbol .]

On Thu, Sep 28, 2017 at 1:16 PM, pisymbol .  wrote:

>
>
> On Thu, Sep 28, 2017 at 11:55 AM, Simon Slavin 
> wrote:
>
>>
>>
>> On 28 Sep 2017, at 3:31pm, pisymbol .  wrote:
>>
>> > Specificially, if thread 1 and 2 both have a handle to sqlite3 with full
>> > mutex, then both could start a transaction simultaneously, one will win
>> the
>> > other will wait, [snip]
>>
>> By "serialised" the documentation means that no two SQL commands will be
>> allowed to take place at the same time.  But SQL can’t separate your two
>> different threads into two different sources of commands.  As far as its
>> concerned, anything which uses your single connection is part fo the same
>> command-stream.  So if one part of your program executes BEGIN then
>> everything from any source from then onwards is part of the same
>> transaction until some part of your program executes "COMMIT".
>>
>> If you want your program to execute two different transactions at the
>> same time (and handle the bookkeeping necessary to make one wait for the
>> other to complete) you must use two different connections.  And if you have
>> three independent parts of your program which may all depend on transaction
>> isolation, you need three connections
>>
>
> So even with full mutex, if I have thread 1 issue a "BEGIN" and it starts
> processing, and another thread 2 issues a "BEGIN" somewhere in between
> that, the other thread will just flat out fail because a transaction has
> already started for thread 1. Is that correct?
>
> MAN, the doc is very unclear about this.
>
>
The follow-up to this is what is the purpose of full mutex mode and when
should it be used over no mutex mode?

I was using no mutex for a while until I thought full mutex might simplify
things a lot (pass one connection pointer around a few threads etc.). I
guess it would if I used auto-commit mode instead, right?

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[pisymbol .]

On Thu, Sep 28, 2017 at 11:55 AM, Simon Slavin  wrote:

>
>
> On 28 Sep 2017, at 3:31pm, pisymbol .  wrote:
>
> > Specificially, if thread 1 and 2 both have a handle to sqlite3 with full
> > mutex, then both could start a transaction simultaneously, one will win
> the
> > other will wait, [snip]
>
> By "serialised" the documentation means that no two SQL commands will be
> allowed to take place at the same time.  But SQL can’t separate your two
> different threads into two different sources of commands.  As far as its
> concerned, anything which uses your single connection is part fo the same
> command-stream.  So if one part of your program executes BEGIN then
> everything from any source from then onwards is part of the same
> transaction until some part of your program executes "COMMIT".
>
> If you want your program to execute two different transactions at the same
> time (and handle the bookkeeping necessary to make one wait for the other
> to complete) you must use two different connections.  And if you have three
> independent parts of your program which may all depend on transaction
> isolation, you need three connections
>

So even with full mutex, if I have thread 1 issue a "BEGIN" and it starts
processing, and another thread 2 issues a "BEGIN" somewhere in between
that, the other thread will just flat out fail because a transaction has
already started for thread 1. Is that correct?

MAN, the doc is very unclear about this.


> > I can't find where a single thread
> > of execution could issue "BEGIN" twice in my code though I did find that
> I
> > was calling sqlite3_lastrow_insert() twice in a row by accident but I
> don't
> > know how that could cause the above.)
>
> Do you use _prepare(), _step(), _finalize() for a multi-row SELECT
> anywhere ?  What probably happened is that one thread executed _step() for
> a multi-row SELECT.  That means it is in the middle of a SELECT command, so
> it wouldn’t have released its lock on the database.Only when a _step()
> command has reached the end of the rows and returned SQLITE_DONE (or an
> error) is the SQL command over, at which point SQLite can end the
> transaction.
>

Before I answer that, can you please confirm the above?

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

Re: [sqlite] FULLMUTEX and exclusive transactions between threads

[Simon Slavin]

On 28 Sep 2017, at 5:00pm, Igor Korot  wrote:

> But the lock will be released on _finalize(), right?

Yes.  Or on _reset().  Nevertheless, the transaction lasts until that point, so 
the use of BEGIN, or the start of another SQLite one-statement transaction 
cannot happen until then and will yield an error result.

At least I think so.  Happy to be corrected by anyone who actually does 
multi-thread programming.

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

Re: [sqlite] Joining list?

[Jose F. Gimenez]

Hi,

it's a GMail issue, not mailman. GMail stops every returned email from 
mailman which you have sent to it. GMail thinks "if you've sent this 
message, it's already in your sent folder, and you don't want to see it 
again in your inbox folder". It's stupid, but it's how gmail works  :-(


I have also a mailman installation in a server for my own bussines, and 
some of my coleages had to drop their gmail accounts and switched to 
other email providers. That's all.


Regards,

Jose F. Gimenez


El 28/09/2017 a las 13:03, pisymbol . escribió:

On Thu, Sep 28, 2017 at 6:57 AM, Simon Slavin  wrote:


cc: OP

On 27 Sep 2017, at 4:31pm, pisymbol .  wrote:

I hope this arrives and gets moderated so someone can look at it.


Your mesage and replies to it reached the mailing list without probblems.
If you’re not seeing it there’s something wrong with your mail setup.
Check your junk folder and other possibilities.



I never received a confirmation from Mailman either. So it's strange that
my emails are being delivered in the first place (one typically has to
confirm their subscription). But again, thanks for letting me know and
sorry for all the noise!

-aps
___
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] FULLMUTEX and exclusive transactions between threads

[Igor Korot]

Hi, Simon,

On Thu, Sep 28, 2017 at 11:55 AM, Simon Slavin  wrote:
>
>
> On 28 Sep 2017, at 3:31pm, pisymbol .  wrote:
>
>> Specificially, if thread 1 and 2 both have a handle to sqlite3 with full
>> mutex, then both could start a transaction simultaneously, one will win the
>> other will wait, [snip]
>
> By "serialised" the documentation means that no two SQL commands will be 
> allowed to take place at the same time.  But SQL can’t separate your two 
> different threads into two different sources of commands.  As far as its 
> concerned, anything which uses your single connection is part fo the same 
> command-stream.  So if one part of your program executes BEGIN then 
> everything from any source from then onwards is part of the same transaction 
> until some part of your program executes "COMMIT".
>
> If you want your program to execute two different transactions at the same 
> time (and handle the bookkeeping necessary to make one wait for the other to 
> complete) you must use two different connections.  And if you have three 
> independent parts of your program which may all depend on transaction 
> isolation, you need three connections
>
>> I can't find where a single thread
>> of execution could issue "BEGIN" twice in my code though I did find that I
>> was calling sqlite3_lastrow_insert() twice in a row by accident but I don't
>> know how that could cause the above.)
>
> Do you use _prepare(), _step(), _finalize() for a multi-row SELECT anywhere ? 
>  What probably happened is that one thread executed _step() for a multi-row 
> SELECT.  That means it is in the middle of a SELECT command, so it wouldn’t 
> have released its lock on the database.Only when a _step() command has 
> reached the end of the rows and returned SQLITE_DONE (or an error) is the SQL 
> command over, at which point SQLite can end the transaction.

But the lock will be released on _finalize(), right?

Thank you.

>
> Simon.
> ___
> 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] FULLMUTEX and exclusive transactions between threads

[Simon Slavin]

On 28 Sep 2017, at 3:31pm, pisymbol .  wrote:

> Specificially, if thread 1 and 2 both have a handle to sqlite3 with full
> mutex, then both could start a transaction simultaneously, one will win the
> other will wait, [snip]

By "serialised" the documentation means that no two SQL commands will be 
allowed to take place at the same time.  But SQL can’t separate your two 
different threads into two different sources of commands.  As far as its 
concerned, anything which uses your single connection is part fo the same 
command-stream.  So if one part of your program executes BEGIN then everything 
from any source from then onwards is part of the same transaction until some 
part of your program executes "COMMIT".

If you want your program to execute two different transactions at the same time 
(and handle the bookkeeping necessary to make one wait for the other to 
complete) you must use two different connections.  And if you have three 
independent parts of your program which may all depend on transaction 
isolation, you need three connections

> I can't find where a single thread
> of execution could issue "BEGIN" twice in my code though I did find that I
> was calling sqlite3_lastrow_insert() twice in a row by accident but I don't
> know how that could cause the above.)

Do you use _prepare(), _step(), _finalize() for a multi-row SELECT anywhere ?  
What probably happened is that one thread executed _step() for a multi-row 
SELECT.  That means it is in the middle of a SELECT command, so it wouldn’t 
have released its lock on the database.Only when a _step() command has 
reached the end of the rows and returned SQLITE_DONE (or an error) is the SQL 
command over, at which point SQLite can end the transaction.

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

[sqlite] FULLMUTEX and exclusive transactions between threads

[pisymbol .]

Hello:

I have an application that makes heavy use of sqlite3 and during load
testing I very, very rarely  (read: it has only happened once so far)
occasionally see the following error message:

"BEGIN EXCLUSIVE error: cannot start a transaction within a transaction"

I understand this error message and usually I cough it up to some
connection issuing a "BEGIN;" statement after a previous transaction has
already started and not been finalized by either a "COMMIT;" or "ROLLBACK;"
statement respectively.

However, if an application shares a single connection to an sqlite3
database and that connection was opened up with SQLITE3_OPEN_FULLMUTEX,
(note the complete flags were
"SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE|SQLITE_OPEN_PRIVATECACHE") aren't
all queries in effect serialized? And as such this error condition above
can never occur between different threads holding the same connection that
was opened with the full mutex flag?

Specificially, if thread 1 and 2 both have a handle to sqlite3 with full
mutex, then both could start a transaction simultaneously, one will win the
other will wait, and life goes on.

I did read this thread:

http://sqlite.1065341.n5.nabble.com/Multiple-reads-and-writes-to-a-single-DB-connection-from-multiple-threads-td75972.html

And based on that I believe I'm right but I want to verify my understanding
of this flag. Of course, now I need to understand how the above even
happened if everything above holds true (I can't find where a single thread
of execution could issue "BEGIN" twice in my code though I did find that I
was calling sqlite3_lastrow_insert() twice in a row by accident but I don't
know how that could cause the above.).

Thanks!

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

Re: [sqlite] Joining list?

[Richard Damon]

On 9/28/17 7:03 AM, pisymbol . wrote:

On Thu, Sep 28, 2017 at 6:57 AM, Simon Slavin  wrote:


cc: OP

On 27 Sep 2017, at 4:31pm, pisymbol .  wrote:

I hope this arrives and gets moderated so someone can look at it.


Your mesage and replies to it reached the mailing list without probblems.
If you’re not seeing it there’s something wrong with your mail setup.
Check your junk folder and other possibilities.



I never received a confirmation from Mailman either. So it's strange that
my emails are being delivered in the first place (one typically has to
confirm their subscription). But again, thanks for letting me know and
sorry for all the noise!

-aps
Not all mailman lists require confirmation. It sounds like this list may 
be setup for just approval.


--
Richard Damon

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

Re: [sqlite] Joining list?

[pisymbol .]

On Thu, Sep 28, 2017 at 6:57 AM, Simon Slavin  wrote:

> cc: OP
>
> On 27 Sep 2017, at 4:31pm, pisymbol .  wrote:
>
> I hope this arrives and gets moderated so someone can look at it.
>
>
> Your mesage and replies to it reached the mailing list without probblems.
> If you’re not seeing it there’s something wrong with your mail setup.
> Check your junk folder and other possibilities.
>
>
I never received a confirmation from Mailman either. So it's strange that
my emails are being delivered in the first place (one typically has to
confirm their subscription). But again, thanks for letting me know and
sorry for all the noise!

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

Re: [sqlite] Joining list?

[Simon Slavin]

cc: OP

On 27 Sep 2017, at 4:31pm, pisymbol .  wrote:

> I hope this arrives and gets moderated so someone can look at it.

Your mesage and replies to it reached the mailing list without probblems.  If 
you’re not seeing it there’s something wrong with your mail setup.  Check your 
junk folder and other possibilities.

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

Re: [sqlite] Joining list?

[Richard Hipp]

On 9/27/17, pisymbol .  wrote:
>
> I am trying to join the sqlite3-users list to ask a technical question but
> I never receive a response from the Mailman (and no it isn't in my Spam
> folder either).
>

I had Mailman set to notify me of subscription requests once per day.
So there was a delay.  I have now switched it to notify me
immediately.

-- 
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] All of a sudden I must commit after every single change and I don't know what happened.

[bensonbear]

Ryan writes:

| just to be clear: It's not that SQLite commits after every
| statement - In SQLite, unless you have explicitly opened a
| transaction (using BEGIN), every statement is (and must be)
| in and of itself a transaction.

Right, I didn't say it committed after every statement, just
after every statement outside of a transaction (by which I 
meant an explicit transaction -- perhaps that was not clear
enough).   So as I understand it the net effect is, when 
outside a(n explicit)  transaction, a  commit is  done after each 
statement.

Simon writes:

| Were you checking the result codes returned for each sqlite_ function call
?

Alas, as I sheepishly admitted earlier, no.   I am not sure one can get
all of the explicit result codes using the python module, but one can at
least
get a more generic exception, and I will add that because it should have
been there all along (lame excuse: quickly done program primarily for
personal use at current).

Keith Medcalf writes:

| Yeah, the default sqlite3 interface (pysqlite2) in python is
| weird.  I switched to APSW a long time ago as it is less
| magical and better designed

Thanks, I had not noticed this other possibility.   I think I 
will stick with sqlite3 module (as it is now called) for now
since it does everything I need, but may switch later once
I start making my program do more and determine
that APSW has net better functionality for that.

| The best way to deal with transactions in the pysqlite2 wrapper is to 
| set isolation=None on the open. 

Right, this is the mode that I mentioned preferring once
I saw what the default mode was, and probably will alter 
my code to employ.  Glad to see others think the default
mode is a little "weird".

Sorry if there are some formatting anomalies here:  I
am posting by the web access at nabble.  I thought
I joined the mailing list itself but apparently it keeps insisting 
I have not. 






--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Joining list?

[pisymbol .]

I hope this arrives and gets moderated so someone can look at it.

I am trying to join the sqlite3-users list to ask a technical question but
I never receive a response from the Mailman (and no it isn't in my Spam
folder either).

I apologize if this is not the right address but I don't see an obvious one
here:

http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Can someone please help?

Thanks!

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