Improve transaction examples

[tbg]

The problem with examples is that we want them to be easily grasped (so they don't do the most elaborate error checking) and that they highlight the need to handle restarts. Unfortunately, most (all?) examples are "wrong" in the sense that they

• don't check all errors
• don't check whether both accounts involved in a transfer exist (so essentially you can lose money or create it from nowhere)
• do two (or rather, six) individual queries even though they could do one

I think we should strive to make the examples "optimal" in all regards since often that's where folks are going to copy-paste from.

[jseldess]

Great feedback, @tschottdorf Would you be willing to correct one example (perhaps python) to guide this further?

[tbg]

Sure. Before I do this, the resulting query (or at least the best one I could come up with) is

# ($1, $2, $3) = (from, to, amount)
UPDATE accounts
SET balance = CASE
                  WHEN id = $1 THEN balance - $3
                  ELSE balance + $3
              END
WHERE (id = $1
       OR id = $2)
  AND
    (SELECT COUNT(id)
     FROM accounts
     WHERE (id = $1
            AND balance >= $3)
       OR id = $2) = 2;

This will return with either two rows updated (in case the transfer did happen, i.e. both accounts actually exist and the first account had enough money for the transfer) or zero.

What do you think? Is this too complicated?

Somewhat related, the above query is a good candidate for explaining query inspection (i.e. what tools does CockroachDB give me to see how well my queries perform). For example, one would want to check that the SELECT in the AND clause is not evaluated twice.

[tbg]

cc @RaduBerinde as resident SQL wizard.

[bdarnell]

I think that's too complex. While it might be good to show an example somewhere of how you can use advanced sql to eliminate round-trips (and this could be a good doc article in its own right, walking through explain, etc), the examples in "build a test app" should focus on how to do multi-statement transactions using the basic sql syntax that most developers understand.

[RaduBerinde]

I would just check the accounts in the same transactions (using separate SELECTs). I think we are already checking one of them for the balance.

We can put the complex server-side query in an example of how we can "offload" more logic to the server but I think it's too complicated for the "basic" example.

[JackKrupansky]

I think having a range of depth of processing sophistication for a single functional example makes sense and would aid users in both getting started with CockroachDB and evolving up to a more sophisticated level of data access. IOW, have a bare-bones example that shows only the basics needed for concurrent transactions, and then incrementally add processing sophistication. That way any given user can decide what level of depth they need.

Granted, this raises the question of whether every example for every language needs every level of processing, but... it probably makes sense to have at least one example for each client language that shows each level of processing. Maybe the bank example is that example that needs this combinatoric explosion of example cases. Other functional examples can just pick a single modest to moderate level of processing.

And to the extent possible, some of these levels of processing sophistication should focus on the SQL level so that they don't need to be client language-specific. IOW, document best practices in SQL only, unless they actually do require client language-specific processing.

[jseldess]

Closing in favor of various other issues related to transactions.