Re: [sqlite] Consistency, rollback and such
On Wed, Sep 21, 2016 at 12:02 AM, Richard Hipp wrote: > If you do a "BEGIN;" followed by a "SELECT..." then the transaction > starts before the SELECT is run. So it is serializable. > > But if you do just a "BEGIN", then some other process might jump in > line ahead of you and make some other changes to the database. Your > transaction will not be able to see those changes, due to isolation. > But they will be in the database file. Then when you got to COMMIT, > SQLite will see that your transaction is based on an historical and > out-of-date version of the database and hence will refuse to do the > commit. You'll have to ROLLBACK and try again. > > When you do "BEGIN IMMEDIATE" that reserves your spot in line and > ensures that no other transactions will commit in front of you. Does the above applies to WAL only, or the default DELETE journal mode as well? I thought only WAL did MVCC. Or am I misreading the "based on an historical and out-of-date version of the database" comment? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Consistency, rollback and such
Thanks. That seems clear, and I think I understand it, and the conflicts that I was referring to result in exceptions thrown at commit() time. That makes sense. I don't think I'll be doing any rollbacks -- the logic is hard enough as it stands. I'll just wrap it all in IMMEDIATE transactions. They're quick, but conflicts are fairly likely, so it's probably the right solution. On Tue, Sep 20, 2016 at 3:02 PM, Richard Hipp wrote: > On 9/20/16, Kevin O'Gorman wrote: > > Surely, Mr. Hipp is an authority, but I'm slightly puzzled by this > answer. > > And it doesn't answer the part of the question about what happens if I do > > it wrong, and transactions conflict. Based on what I now think is true, > if > > I don't do something, transactions begin with the first modification. > They > > may be prevented from executing UPDATEs simultaneously, but could still > > result in non-serializable execution of the whole, and inconsistent or > > erroneous data in the database, because each would be based on the SELECT > > statements before either had written. Or do they result in exceptions? > > If you do a "BEGIN;" followed by a "SELECT..." then the transaction > starts before the SELECT is run. So it is serializable. > > But if you do just a "BEGIN", then some other process might jump in > line ahead of you and make some other changes to the database. Your > transaction will not be able to see those changes, due to isolation. > But they will be in the database file. Then when you got to COMMIT, > SQLite will see that your transaction is based on an historical and > out-of-date version of the database and hence will refuse to do the > commit. You'll have to ROLLBACK and try again. > > When you do "BEGIN IMMEDIATE" that reserves your spot in line and > ensures that no other transactions will commit in front of you. > > > -- > 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 > -- #define QUESTION ((bb) || (!bb)) /* Shakespeare */ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Consistency, rollback and such
On 9/20/16, Kevin O'Gorman wrote: > Surely, Mr. Hipp is an authority, but I'm slightly puzzled by this answer. > And it doesn't answer the part of the question about what happens if I do > it wrong, and transactions conflict. Based on what I now think is true, if > I don't do something, transactions begin with the first modification. They > may be prevented from executing UPDATEs simultaneously, but could still > result in non-serializable execution of the whole, and inconsistent or > erroneous data in the database, because each would be based on the SELECT > statements before either had written. Or do they result in exceptions? If you do a "BEGIN;" followed by a "SELECT..." then the transaction starts before the SELECT is run. So it is serializable. But if you do just a "BEGIN", then some other process might jump in line ahead of you and make some other changes to the database. Your transaction will not be able to see those changes, due to isolation. But they will be in the database file. Then when you got to COMMIT, SQLite will see that your transaction is based on an historical and out-of-date version of the database and hence will refuse to do the commit. You'll have to ROLLBACK and try again. When you do "BEGIN IMMEDIATE" that reserves your spot in line and ensures that no other transactions will commit in front of you. -- 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] Consistency, rollback and such
If by explicitly, you mean starting my own transactions, then I guess I'll
be doing it as you are. I do not even want to think about savepoints and
figuring out what has to be re-done. My transactions, while numerous, will
be pretty simple and mostly quite quick, so there's not much advantage to
redoing over just delaying. Besides, I'm not sure I know how to detect a
conflict that would has caused or should cause a rollback.
On Tue, Sep 20, 2016 at 12:21 PM, David Raymond
wrote:
> Your understanding of the isolation_level parameter there is correct. It
> only effects how the transactions are created. If you're going
> to be doing a few selects before you update then doing the
> "begin someSortOf transaction;" is what you want.
>
> After having some trouble getting savepoints to work correctly I've gotten
> into the habit of always doing isolation_level = None, and doing everything
> explicitly, but as long as you know what's going on then you're good.
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Kevin O'Gorman
> Sent: Tuesday, September 20, 2016 12:35 PM
> To: sqlite-users
> Subject: [sqlite] Consistency, rollback and such
>
> I'm also wondering if setting
> conn = sqlite3.connect("mydb", isolation_level=IMMEDIATE)
> does what I need. Reading the docs, it would appear this does not start a
> transaction until the UPDATE, and I think I want the transactions to start
> before the first SELECT. Should I instead do
> c = conn.cursor()
> c.execuite("BEGIN TRANSACTION IMMEDIATE")
>
> and is IMMEDIATE the right thing, or do I need EXCLUSIVE.
>
>
> --
> #define QUESTION ((bb) || (!bb)) /* Shakespeare */
> ___
> 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
>
--
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Consistency, rollback and such
Surely, Mr. Hipp is an authority, but I'm slightly puzzled by this answer.
And it doesn't answer the part of the question about what happens if I do
it wrong, and transactions conflict. Based on what I now think is true, if
I don't do something, transactions begin with the first modification. They
may be prevented from executing UPDATEs simultaneously, but could still
result in non-serializable execution of the whole, and inconsistent or
erroneous data in the database, because each would be based on the SELECT
statements before either had written. Or do they result in exceptions?
On Tue, Sep 20, 2016 at 10:09 AM, Richard Hipp wrote:
> On 9/20/16, Kevin O'Gorman wrote:
> > c.execuite("BEGIN TRANSACTION IMMEDIATE")
> >
> > and is IMMEDIATE the right thing, or do I need EXCLUSIVE.
>
> IMMEDIATE is the right thing. That lets other readers continue and
> new readers to start, but blocks all other writers.
>
Please confirm or refute my understanding that this would let transactions
without the EXCLUSIVE to begin, but not EXCLUSIVE ones even if they begin
with reading.
> EXCLUSIVE would block everybody - readers and writers - which is more
> than you need.
>
If my understanding above is correct, then this would indeed be more than
required. But of course, it would work so long as the SELECTs are wrapped
along with their resultant UPDATEs.
>
> --
> 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
>
--
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Consistency, rollback and such
Your understanding of the isolation_level parameter there is correct. It only
effects how the transactions are created. If you're going to be
doing a few selects before you update then doing the "begin
someSortOf transaction;" is what you want.
After having some trouble getting savepoints to work correctly I've gotten into
the habit of always doing isolation_level = None, and doing everything
explicitly, but as long as you know what's going on then you're good.
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Kevin O'Gorman
Sent: Tuesday, September 20, 2016 12:35 PM
To: sqlite-users
Subject: [sqlite] Consistency, rollback and such
I'm also wondering if setting
conn = sqlite3.connect("mydb", isolation_level=IMMEDIATE)
does what I need. Reading the docs, it would appear this does not start a
transaction until the UPDATE, and I think I want the transactions to start
before the first SELECT. Should I instead do
c = conn.cursor()
c.execuite("BEGIN TRANSACTION IMMEDIATE")
and is IMMEDIATE the right thing, or do I need EXCLUSIVE.
--
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
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] Consistency, rollback and such
On 9/20/16, Kevin O'Gorman wrote:
> c.execuite("BEGIN TRANSACTION IMMEDIATE")
>
> and is IMMEDIATE the right thing, or do I need EXCLUSIVE.
IMMEDIATE is the right thing. That lets other readers continue and
new readers to start, but blocks all other writers.
EXCLUSIVE would block everybody - readers and writers - which is more
than you need.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Consistency, rollback and such
I think I understand the basics of SQL and ACID properties, but I'm new to
SQLite and not really experienced in any of these. So I'm having some
trouble figuring out the detailed consequences of IMMEDIATE, EXCLUSIVE and
DEFERRED and the autocommit mode of python's sqlite3.
I expect my transactions to be fairly short, and to average three or less
per second, so conflict is likely, but not likely to overload whatever
mechanisms are involved. However, it will be very common for a transaction
to begin with SELECT queries, and to compose an UPDATE based on what it has
and what it finds. It will be quite possible, even frequent, for multiple
processes to decide to update the same records.
As background, I'd like to know what happens with each of the kinds of
transaction. Do any of them do rollbacks, and if so what does that look
like (in Python), Do any of them throw exceptions?
I'm guessing I'm going to want one of IMMEDIATE or EXCLUSIVE, but I'm not
sure which one. I'm also wondering if setting
conn = sqlite3.connect("mydb", isolation_level=IMMEDIATE)
does what I need. Reading the docs, it would appear this does not start a
transaction until the UPDATE, and I think I want the transactions to start
before the first SELECT. Should I instead do
c = conn.cursor()
c.execuite("BEGIN TRANSACTION IMMEDIATE")
and is IMMEDIATE the right thing, or do I need EXCLUSIVE.
That's a bunch of questions, so please answer any where you're _sure_ you
know the answer.
--
#define QUESTION ((bb) || (!bb)) /* Shakespeare */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
