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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
