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

Question

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.

Apriorism

Whatever you're using to interact with the database is apparently already starting a transaction. Go over its documentation to see if there's any way of changing that behavior? – Amaya

@Amaya I initially fat fingered something between BEGIN TRANSACTIO and END TRANSACTION. I fixed that,ran it again, and got the cannot start error. I tried dropping and re-entering the tables and information, and I still get the error. Is it hung up on the initial BEGIN TRANSACTION? – Apriorism

Answer 1

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.

Yea

Answer 2

In the SQLite Editor, you may have to delete or comment out all of the code before and after these two transactions.

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

BEGIN TRANSACTION;
  INSERT INTO widgetInventory ( description, onhand ) VALUES ( 'toy', 25 );
ROLLBACK;

Otherwise it won't execute the transaction.

Other than that, there is probably an error written in somewhere. Copying and pasting in the .txt file didn't give me that transaction error and could execute the transaction normally.

Righthanded

Answer 3

Just had this same error and my issue was I only highlighted the first line so SQLLite started the transaction but didn't run it fully. All I did was run end transaction, highlight the whole block of code and run that and it worked fine. Must be some syntax issue in Lite that doesn't run the full block itself.

Gliadin

Answer 4

while executing SQL query on database 'test': cannot start a transaction within a transaction means a transaction already exists. It may happen if someone forgets to select the END TRANSACTION; statement. If you face this issue just select END TRANSACTION once and run. With this it will end the active transaction and then you can run any of the existing transaction.

Rahn

Answer 5

For the particular case of following the Linkedin learning "SQL essential training" course, I have figured out to fix it by running (f9) the "BEGIN TRANSACTION", "...TRANSACTION CONTENTS..." and "END TRANSACTION" statements separately, not all the statements at the same time.

So,

1. First select the "BEGIN TRANSACTION;" and run it by pressing f9.
2. Then select the contents of the transactions (I think you can include also the "END TRANSACTION;" part) and run it.

Tigon