Re: [sqlite] Transaction behaviour

[Joseph L. Casale]

> SQLite transaction is a property of a database connection - there ain't
> no such thing as a transaction spanning multiple connections.

In hindsight, I completely overlooked that very point. Between then and
now it's all been refactored to do this correctly.

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

Re: [sqlite] Transaction behaviour

[Igor Tandetnik]

On 1/23/2014 5:12 PM, Joseph L. Casale wrote:

The bulk load doesn't have to be done this way, only one process loads data
and even a single connection can be used but that would segment the wrapper.


SQLite transaction is a property of a database connection - there ain't 
no such thing as a transaction spanning multiple connections. So, if you 
want to utilize SQLite transactions as you atomicity mechanism, then all 
changes will have to be made on a single connection. If for whatever 
reason you want to make changes atomically on multiple connections, then 
you face an unenviable task of designing your own atomicity mechanism - 
I don't believe SQLite provides anything to help you with this, and in 
fact will fight against you (only one connection can write to a given 
database file at any given time). Choose your poison.

--
Igor Tandetnik

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

Re: [sqlite] Transaction behaviour

[Joseph L. Casale]

> Start the bulk load with "BEGIN IMMEDIATE;" or "BEGIN
> EXCLUSIVE;" and count errors.
> If there were no errors at the end of the bulk load, issue
> "COMMIT;", else issue "ROLLBACK".
> If the program crashes, sqlite will take care of the rollback
> automatically the next time any process opens the database.
> 
> http://sqlite.org/c3ref/get_autocommit.html can help you decide
> what mode you are in.
> 
> By the way, autocommit is not the best thing to do if you have
> related insert/update/delete statements in an application
> transaction. Related updates should be in the same, atomic,
> transaction.

Hi Kees and Igor,

I appreciate the atomic violation of the current implementation.

The issue lies in the fact the individual rows for each table consume a cursor
and are wrapped in a context manager which starts its own transaction.

The wrapper that abstracts the user from raw sql, connections and cursors
hands out individual connections as the data's consuming access is
multiprocess based and to work around the concurrency issues I have no
choice.

The bulk load doesn't have to be done this way, only one process loads data
and even a single connection can be used but that would segment the wrapper.

So either that or temp tables / other schema changes might be needed in this
case. I'm looking at refactoring properly for the sake of correctness.

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

Re: [sqlite] Transaction behaviour

[Kees Nuyt]

On Thu, 23 Jan 2014 16:30:57 +, "Joseph L. Casale"
 wrote:

>I have a scenario where I am writing a series of entries across several tables
>with relationships using Python and context managers. The sql is abstracted
>away from the user by a class providing all the needed methods. These each
>open implicit transactions, now its been asked that during the bulk loading
>process, we wrap it all up in a transaction so nothing will be committed in
>the event of some problem during the load.
>
>This presents a problem as far as I know, aside from extending the schema
>with a table to indicate state that is updated upon completion, is there 
>anything
>about transactions I am not seeing where I can accomplish leaving the bulk
>load uncommitted in the event of an issue in my case?

Start the bulk load with "BEGIN IMMEDIATE;" or "BEGIN
EXCLUSIVE;" and count errors.
If there were no errors at the end of the bulk load, issue
"COMMIT;", else issue "ROLLBACK".
If the program crashes, sqlite will take care of the rollback
automatically the next time any process opens the database.

http://sqlite.org/c3ref/get_autocommit.html can help you decide
what mode you are in.

By the way, autocommit is not the best thing to do if you have
related insert/update/delete statements in an application
transaction. Related updates should be in the same, atomic,
transaction.

>Thanks,
>jlc 

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Transaction behaviour

[Igor Tandetnik]

On 1/23/2014 11:30 AM, Joseph L. Casale wrote:

is there anything
about transactions I am not seeing where I can accomplish leaving the bulk
load uncommitted in the event of an issue in my case?


I'm not sure I understand the nature of the difficulty. If you don't 
want to COMMIT a transaction, then you ROLLBACK it.


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