SQLite Forum

PROBLEM WITH TRANSACTION
Login

PROBLEM WITH TRANSACTION

(1) By anonymous on 2020-04-03 19:03:02 [link]

Hello, I am trying to learn SQLite and I am taking an online course. I have a problem with the transaction code. In particular, the message return by status on SQL is "Error while executing SQL query on database 'test': cannot start a transaction within a transaction"

The text in the qery is:

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

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

INSERT INTO widgetInventory (description, onhand) VALUES ('rock', 25);
INSERT INTO widgetInventory (description, onhand) VALUES ('paper', 25);
INSERT INTO widgetInventory (description, onhand) VALUES ('scissor', 25);
  
SELECT * FROM widgetSales; 
SELECT * FROM widgetInventory;

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

The transaction code doesn't work, what's wrong?

(2) By Stephan Beal (stephan) on 2020-04-03 19:10:58 in reply to 1 [link]

Are you entering this SQL in the sqlite console app? If not, the problem might be that the app/page you are using is starting its own transaction implicitly, and sqlite does not support (last i checked) nested transactions.

(3) By Keith Medcalf (kmedcalf) on 2020-04-03 19:17:33 in reply to 1 [link]

That is 11 query's, not 1.

Do you complete each query before beginning the next?

That is, after preparing each of the 11 queries do you ensure that you (1) step until SQLITE_DONE is returned and (2) reset / finalize the statement?

Are you checking the return code from EVERY API call?

(4) By Warren Young (wyoung) on 2020-04-03 21:36:11 in reply to 1 [link]

You need a semicolon at the end of the `INSERT` statement within the `TRANSACTION`, not a comma.

If you paste your queries into the `sqlite3` command line app, you get a syntax error when it hits `UPDATE`, since you can't use that keyword as part of an `INSERT` query.

(5) By anonymous on 2020-10-02 01:08:03 in reply to 1 [link]

Type and execute the query 'ROLLBACK' first, and then restart from BEGIN TRANSACTION

(7) By anonymous on 2020-12-08 18:43:09 in reply to 5 [link]

I posted below but after completing the lesson this IS THE CORRECT ANSWER.  Father down in lesson it addresses this exact issue.  I and the OP both somehow started the transaction and something when wrong.  SO it appears we we're both "stuck" mid transaction and not able to execute the query.  I was only able to "fix" the issue via dropping the tables and starting over but that doesn't HELP the actual issue or help us understand what was going on.  ROLLBACK is the correct method to fix this issue and future issues with this error.

(6) By anonymous on 2020-12-08 18:35:15 in reply to 1 [link]

I ran across this error on this exact course.  I used the DROP TABLE IF EXISTS for both tables and started over.  I wanted to replicate what I did as it worked after a lot of back and forth once I dropped the tables and started over. Not sure if something got messed up from the copy and paste from the text file with the query's or I messed up something playing around.  I tried to replicate the error again but was not able to.  I was only able to get the same error when in "World" database and attempted to run the query.  Not sure if user error or not but dropping all tables and starting over fixed my issue with the same error.  I noticed the instructor is using a Mac and I am using a Windows PC.  SQLiteStudio doesn't seem to like running on my machine and has quit suddenly a couple of times or behaved a little odd.  Not sure if that could contribute as well.  Mine quit suddenly in the middle of the error you and I both had and I had to restart the program.  Could all be related somehow.  Good luck.

(8) By anonymous on 2021-04-24 12:02:13 in reply to 1

I was using SQLlite Browser when I tried executing the BEGIN TRANSACTION section in the same exercise. I tried the SQLiteStudio application and the code worked well.

There seems to be a problem with the SQLlite Browser. Can anyone help, please? Thank you!

(9) By Ryan Smith (cuz) on 2021-04-24 14:37:17 in reply to 8 [link]

Two points:

- Please do not piggy-back on other unrelated threads, start a new one for a new question.
- This is not an SQLite question but I assume a "DB Browser for SQLite" question, which should get you best results when posting it to that system's forum/list.

That said, my guess would be that the Browser you use might automagically keep an already-opened transaction for you (as is the habit of some such browsers), so perhaps you can dispense with opening the transaction and just commit it when needed.

If you are testing a script that should run by itself, and you need to include the Starting and ending of transactions, perhaps make that in a file that can be run through sqlite3.exe, or, you know, use sqlite studio or another browser that works the way you prefer, or ask the DB Browser folks if a setting can perhaps change that behavior.

(10) By Keith Medcalf (kmedcalf) on 2021-04-24 21:59:49 in reply to 1 [link]

My car doesn't work, what's wrong?

(11) By Simon Slavin (slavin) on 2021-04-25 12:24:38 in reply to 1 [link]

You are using software which handles transactions itself.  It won't let you create your own transactions.  Which means it's useless for anything including transaction commands.  The SQLite team is not connected with that software and cannot help you with problems with it.

If you want to use software which the SQLite team can help with, please use the software in the appropriate 'Precompiled Binaries' section of this page

<https://sqlite.org/download.html>

People on this forum can help you with that software since it was developed by the same people who developed SQLite itself.