sql - 您如何在SQLite中进行事务处理？

Question

我试图弄清楚如何在SQLite中进行事务，但是我碰壁了。假设我想将50美元从一个人的帐户转移到另一个人。看一下下面的代码。对此发表了强烈评论。

DROP TABLE IF EXISTS accounts;
CREATE TABLE IF NOT EXISTS accounts (
    name       VARCHAR(20) NOT NULL,
    balance    INTEGER         NULL -- Money is going to be stored in cents
);

INSERT INTO accounts (name, balance) VALUES
("John Doe",  10050), -- This means 100 dollars and 50 cents
                      -- because 100 dollars and 50 cents is
                      -- 100 * 100 cents + 50 cents = 10050 cents
("Bob Smith", 20000); -- 200 dollars



DROP VIEW IF EXISTS view_accounts;
CREATE VIEW view_accounts AS
    SELECT rowid,
           name,
           printf("$%.2f", balance / 100.0) AS balance
    FROM accounts;

SELECT * FROM view_accounts;
-- rowid       name        balance
-- ----------  ----------  ----------
-- 1           John Doe    $100.50
-- 2           Bob Smith   $200.00



BEGIN TRANSACTION;

-- Subtract $50 from John Doe's balance
UPDATE accounts SET balance = balance - 5000 WHERE rowid = 1;
-- And add $50 to Bob Smith's balance, but let's now intentionally
-- create something erroneous here. Let's say there's been a mistake
-- and we got the wrong rowid (maybe we received an id that does not
-- exist in our table from a host language such as PHP, but really it
-- could be anything from power-down to inadvertent reboot. I'm using
-- this particular example because it's easy to emulate an exceptional
-- situation). Instead of rowid 2, we mistakenly used a rowid of 3
-- which does not exist in our table.
UPDATE accounts SET balance = balance + 5000 WHERE rowid = 3;

-- Here's where I get stuck. What exactly should my next steps be?
-- What statements should I use here? Obviously I should roll all the
-- changes made so far back with the ROLLBACK command if something
-- exceptional happens, but I can't know that beforehand because the
-- value for rowid is received from external sources. On the other hand,
-- I can't use COMMIT either because what if in fact something
-- exceptional did happen? I somehow need to detect that something bad
-- has happened and conditionally either roll all the changes back or, if
-- everything is okay, commit them.

Answer 1

您不能仅在SQLite中执行此操作，因为SQLite没有任何流控制语句。

您要查找的内容与此伪代码类似：

IF rows-affected = 0 THEN ROLLBACK TRANSACTION

将 ROLLBACK TRANSACTION替换为所需的任何类型的错误响应。

由于在SQLite语法中没有 IF语句或类似语句，因此如果没有使用SQLite的代码/编程语言/运行时的帮助，就无法做到这一点。

换句话说，最终调用SQLite的编程语言需要查看SQLite引擎报告的受影响的行数，并将其处理为零。

请注意，您要回滚事务这一事实是对该错误的部分响应，但此问题与事务本身无关。基本上，您的问题是关于流量控制的。