Re: "Transactions are bad, real bad" - discuss
Date: 30 Apr 2003 16:41:59 -0700
Message-ID: <c0d87ec0.0304301541.3382b4c4_at_posting.google.com>
Your pseudo-code has a problem.
UPDATE (SELECT A1.accountowner AS ao1,
A1.balance AS b1,
A2.accountowner AS ao2,
A2.balance AS b2
FROM Accounts AS A1,
Accounts AS A2)
SET b1 = b1 - 1000000, b2 = b2 + 1000000 WHERE ao1 = 'Paul Vernon'
AND ao2 = 'Jonathan Leffler';
If this worked in SQL, we would construct the temporary working table from the subquery expression, update it, and then throw it away without touching the base tables at all. If you used this pseudo-syntax, you can see the problem more easily with a derived table syntax.
UPDATE (SELECT A1.accountowner, A1.balance,
A2.accountowner, A2.balance
FROM Accounts AS A1,
Accounts AS A2) AS X(ao1, b1, ao2, b2)
SET X.b1 = X.b1 - 1000000,
X.b2 = X.b2 + 1000000
WHERE X.ao1 = 'Paul Vernon'
AND X.ao2 = 'Jonathan Leffler';
Table X does not persist.
Some vendors allow you to use an alias on the table in the UPDATE clause, but that is proprietary not Standard and has the same conceptual problem.
I am happy with the "BEGIN ATOMIC… END;" syntax in the SQL/PSM Standard. I can add exception handlers and get pretty good control over the unit of work. In theory, the compiler can optimize over multiple statements within such a block. Of course, we still have SQL compiles that don't optimize over SELECT clauses in a UNION [ALL] statement ... Received on Thu May 01 2003 - 01:41:59 CEST