Search code examples
sqlitesqltransaction

SQLite error: cannot start a transaction within a transaction with very basic tables

I am brand new to SQL, and I am learning on an SQLite editor. So I create a couple of very simple tables. This code is straight from Linkedin learning "SQL essential training", and I am using the recommended SQLite editor.

CREATE TABLE widgetInventory(
    id INTEGER PRIMARY KEY,
    description TEXT,
    onhand INTEGER NOT NULL);

CREATE TABLE widgetSales(
    id INTEGER PRIMARY KEY,
    inv_id INTEGER,
    quan INTEGER,
    price INTEGER);

Then I update widgetInventory with some data:

INSERT INTO widgetInventory (description, onhand) VALUES ('rock', 25);
INSERT INTO widgetInventory (description, onhand) VALUES ('paper', 25);
INSERT INTO widgetInventory (description, onhand) VALUES ('scissors', 25);

Next, I want to update the widgetSales table with a sale, and update the widgetInventory table to record the reduction of onhand. 

BEGIN TRANSACTION;
INSERT INTO widgetSales (inv_id, quan, price) VALUES (1,5,500);
UPDATE widgetInventory SET onhand = (onhand-5) WHERE id = 1;
END TRANSACTION;

I am not understanding why this gives me an error when I run it, as it is exactly as it is in the lesson.

[06:18:04] Error while executing SQL query on database 'test': cannot start a transaction within a transaction

But, I can run the INSERT and UPDATE lines separately, and they do what I want them to do.

Solution

  • Apparently, running - END TRANSACTION; - before running the entire transaction appears to work.

    I think that somehow, SQL thinks that a transaction is already occurring. Though, I'm not sure where exactly. So to stop it, you have to end the transaction first before proceeding with the course.