Thank you, Adrian. I think this is reason changes() exist. Roman
Sent from my T-Mobile 4G LTE Device -------- Original message -------- From: Adrian Ho <ml+sql...@03s.net> Date: 6/15/19 12:25 AM (GMT-05:00) To: sqlite-users@mailinglists.sqlite.org 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&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069&sdata=QRTKRQ1%2F4kqnHPzXv3mr8o%2BiDPoW9fQWcDraMCl7W%2Fk%3D&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 sqlite-users@mailinglists.sqlite.org https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069&sdata=PO4P1VXub%2FA6isCptXd4rHPUbw1UywudAs0WJkFmiPM%3D&reserved=0 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
