select within transaction

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

select within transaction

Roman Fleysher
Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. If update failed, then c will not be 5 (it will be old value, different from 5) and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback.

Is this what changes() is for?

Thank you,

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select within transaction

Jose Isaias Cabrera-4

Roman Fleysher, on Friday, June 14, 2019 02:22 PM, wrote...
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback.
>
> Is this what changes() is for?
Yes, and no.  From what I understand, and have been using it, if something was written to the DB, it will give you a 1.  Otherwise a 0.  But, it is not the amount of fields, just a write. ie.

sqlite> create table a (a, b, c);
sqlite> create table b (a, d, e);
sqlite> insert into a values (1, 2, 3);
sqlite> insert into a values (2, 3, 4);
sqlite> insert into a values (3, 4, 5);
sqlite> select changes();  -- this is for the last write
1
sqlite> select total_changes(); -- this is for the total amount of writes
3
sqlite> insert into a values (4, 5, 6);
sqlite> select changes();
1
sqlite> select total_changes();
4

I hope this helps.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select within transaction

David Raymond
In reply to this post by Roman Fleysher
How are you sending the commands to the cli?

If you're doing...

sqlite3 myfile.sqlite ".read somefile.sql"

...then you can start the sql file with...

.bail on

...and as soon as it hits an error it will stop there and not continue processing lines. So if you get rid of the "or rollback" then you'll get the error message and won't have to worry about it continuing on to the next lines in the input file despite there having been an error. And since you explicitly started a transaction it will leave the transaction open, and then when the CLI closes it will rollback the uncommitted transaction.


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Roman Fleysher
Sent: Friday, June 14, 2019 2:23 PM
To: General Discussion of SQLite Database <[hidden email]>
Subject: [sqlite] select within transaction

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. If update failed, then c will not be 5 (it will be old value, different from 5) and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback.

Is this what changes() is for?

Thank you,

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select within transaction

Jose Isaias Cabrera-4
In reply to this post by Jose Isaias Cabrera-4

Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote...

> Yes, and no.  From what I understand, and have been using it, if
> something was written to the DB, it will give you a 1.  Otherwise
> a 0.  But, it is not the amount of fields, just a write. ie.

This is wrong information.  It does give you the amount of fields updated. Ie.

sqlite> create table a (a, b, c);
sqlite> insert into a values (1, 2, 3);
sqlite> insert into a values (2, 3, 4);
sqlite> insert into a values (3, 4, 5);
sqlite> select changes();
1
sqlite> select total_changes();
3
sqlite> update a set a=4 where a = 1 or a = 2 or a = 3;
sqlite> select changes(); -- all changes made on the table
3
sqlite> select total_changes();
6
sqlite>

Sorry for the bad data.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select within transaction

Roman Fleysher
In reply to this post by David Raymond
Thank you! I did not know (or forgot) about ".bail on"

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of David Raymond [[hidden email]]
Sent: Friday, June 14, 2019 3:05 PM
To: SQLite mailing list
Subject: Re: [sqlite] select within transaction

How are you sending the commands to the cli?

If you're doing...

sqlite3 myfile.sqlite ".read somefile.sql"

...then you can start the sql file with...

.bail on

...and as soon as it hits an error it will stop there and not continue processing lines. So if you get rid of the "or rollback" then you'll get the error message and won't have to worry about it continuing on to the next lines in the input file despite there having been an error. And since you explicitly started a transaction it will leave the transaction open, and then when the CLI closes it will rollback the uncommitted transaction.


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Roman Fleysher
Sent: Friday, June 14, 2019 2:23 PM
To: General Discussion of SQLite Database <[hidden email]>
Subject: [sqlite] select within transaction

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. If update failed, then c will not be 5 (it will be old value, different from 5) and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback.

Is this what changes() is for?

Thank you,

Roman
_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752&amp;sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3D&amp;reserved=0
_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752&amp;sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3D&amp;reserved=0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select within transaction

Adrian Ho
In reply to this post by Jose Isaias Cabrera-4
On 15/6/19 3:06 AM, Jose Isaias Cabrera wrote:
> Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote...
>
>> Yes, and no.  From what I understand, and have been using it, if
>> something was written to the DB, it will give you a 1.  Otherwise
>> a 0.  But, it is not the amount of fields, just a write. ie.
> This is wrong information.  It does give you the amount of fields updated. Ie.

changes() returns the number of *rows* modified, not fields. See
https://sqlite.org/c3ref/changes.html for the base API function
documentation, which also reveals important details on how it counts
changes in various environments (e.g. triggers, multithreaded updates).


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select within transaction

Adrian Ho
In reply to this post by Roman Fleysher
On 15/6/19 2:22 AM, Roman Fleysher wrote:

> I have a transaction consisting of two commands: update and select. The idea is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT  d FROM t WHERE c = 5 AND ...;
> COMMIT;
>
> Is this what will happen:
>
> 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
> 2. If lock obtained, attempt to update table t to set c=5.
> 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. If update failed, then c will not be 5 (it will be old value, different from 5) and output of SEELCT will be empty.
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback.
>
> Is this what changes() is for?

"Successful update" is rather vague. In some contexts, "no rows changed,
but no error thrown either" might be considered successful.

So there are actually *three* scenarios for your existing code here:

1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT
happens.

2. UPDATE touches one or more rows (WHERE clause matches something) --
SELECT happens.

3. UPDATE touches one or more rows, but triggers a constraint violation
in the process -- ROLLBACK kicks in, SELECT doesn't happen.

If you actually want the SELECT to *not* happen in scenario 1, and you
*must* use the SQLite shell instead of a proper language binding like
the Tcl API (https://sqlite.org/tclsqlite.html), then I think you're
stuck. You can sorta get what you want by changing your SELECT statement
as follows:

SELECT  d FROM t WHERE changes() > 0 AND c = 5;

which still runs the SELECT, but returns nothing in scenario 1. It's
just not very efficient, especially for large tables.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: select within transaction

Roman Fleysher
Thank you,  Adrian. I think this is reason changes() exist.

Roman



Sent from my T-Mobile 4G LTE Device


-------- Original message --------
From: Adrian Ho <[hidden email]>
Date: 6/15/19 12:25 AM (GMT-05:00)
To: [hidden email]
Subject: Re: [sqlite] select within transaction

On 15/6/19 2:22 AM, Roman Fleysher wrote:

> I have a transaction consisting of two commands: update and select. The idea is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT  d FROM t WHERE c = 5 AND ...;
> COMMIT;
>
> Is this what will happen:
>
> 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
> 2. If lock obtained, attempt to update table t to set c=5.
> 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. If update failed, then c will not be 5 (it will be old value, different from 5) and output of SEELCT will be empty.
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one transaction and thus I need some indicator if SELECT was after successful update, not rollback.
>
> Is this what changes() is for?

"Successful update" is rather vague. In some contexts, "no rows changed,
but no error thrown either" might be considered successful.

So there are actually *three* scenarios for your existing code here:

1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT
happens.

2. UPDATE touches one or more rows (WHERE clause matches something) --
SELECT happens.

3. UPDATE touches one or more rows, but triggers a constraint violation
in the process -- ROLLBACK kicks in, SELECT doesn't happen.

If you actually want the SELECT to *not* happen in scenario 1, and you
*must* use the SQLite shell instead of a proper language binding like
the Tcl API (https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Ftclsqlite.html&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069&amp;sdata=QRTKRQ1%2F4kqnHPzXv3mr8o%2BiDPoW9fQWcDraMCl7W%2Fk%3D&amp;reserved=0), then I think you're
stuck. You can sorta get what you want by changing your SELECT statement
as follows:

SELECT  d FROM t WHERE changes() > 0 AND c = 5;

which still runs the SELECT, but returns nothing in scenario 1. It's
just not very efficient, especially for large tables.


_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069&amp;sdata=PO4P1VXub%2FA6isCptXd4rHPUbw1UywudAs0WJkFmiPM%3D&amp;reserved=0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users