MySQL Transactions Within Transactions

Mysql transactions within transactions

This page of the manual might interest you : 12.3.3. Statements That Cause an Implicit Commit; quoting a few sentences :

The statements listed in this section
(and any synonyms for them) implicitly
end a transaction, as if you had done
a COMMIT before executing the
statement.

And, a bit farther in the page :

Transaction-control and locking
statements. BEGIN, LOCK TABLES,
SET autocommit = 1 (if the value is
not already 1), START TRANSACTION,
UNLOCK TABLES.

Are nested transactions allowed in MySQL?

No, but

InnoDB supports SAVEPOINTS.

You can do the following:

CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;

START TRANSACTION;

INSERT
INTO    t_test
VALUES  (1);

SELECT  *
FROM    t_test;

 id
---
  1

SAVEPOINT tran2;

INSERT
INTO    t_test
VALUES  (2);

SELECT  *
FROM    t_test;

 id
---
  1
  2

ROLLBACK TO tran2;

SELECT  *
FROM    t_test;

 id
---
  1

ROLLBACK;

SELECT  *
FROM    t_test;

 id
---

MySQL How to properly use transactions?

This is a somewhat broad question, so I'll try to cover all the things as much as I can.

At first you can ignore the mysqli api (the api specific transaction functions are just wrappers), and go straight to the MySQL manual. The important thing here is that disabling autocommit and starting a transaction are the same thing. Also a single query (including modifications by triggers) is always a transaction.

The answer you question 1 & 2 is "probably not". It very much depends on what your existing code assumes about the database connection, and how your application is structured.

From what you mentioned in the question, the answer would be: it will be better if you only put transactions in the places that need them.

For question 3: it will not commit automatically. You can however make it do so, by using register_shutdown_function, although I don't recommend doing that.

There are statements (implicit commits) which will commit the transaction automatically. These include all DDL statements (CREATE,ALTER...) and also TRUCNATE, LOCK TABLES and others. This basically means those statements can't be used in transactions.

MySQL rolls back transactions when the connection is terminated.

I would recommend to add transactions only to the code which needs them (to be safe you can do this for all code which does more than one write query to the db).

The classic approach is:

START TRANSACTION
query
other things
another query
some other stuff
3-rd query
...
COMMIT

The main thing here is to make sure you only commit if no errors have occurred.

Leave the rollback to either connection termination (or register_shutdown_function if you are using persistent connections), because making sure each and every script will have a correctly working rollback logic is hard :)

This will make sure that nothing is committed if bad things happen (exceptions, fatal errors, time/mem limits, power outages, meteors...).

It is also possible to have transactions at a function/method level (nested and stack-like), but thats out of the scope for this question.

how mysql handle multiple START TRANSACTION (nesting?)

Read the manual instead of wikipedia.

First entry from googling "mysql start transaction":

13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax

Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.

In InnoDB,is all sql in the transaction?

it will be executed in single transactions - unless grouping statements with START TRANSACTION & COMMIT (the autocommit configuration will then be ignored, no matter it's value). SET autocommit = 0/1 can be used to control the level of isolation on-the-fly. there's also a configuration option for that, in order to change the behavior globally:

[mysqld]
autocommit=0

the documentation for "Server System Variables" explains it in detail:

The autocommit mode. If set to 1, all changes to a table take effect immediately. If set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. If autocommit is 0 and you change it to 1, MySQL performs an automatic COMMIT of any open transaction. Another way to begin a transaction is to use a START TRANSACTION or BEGIN statement.

How do MySQL Transactions work under the hood? Where does MySQL store the temporary value of a field?

Some clues:

Read about "MVCC" -- MultiVersion Concurrency Control
A tentatively-changed row is kept until COMMIT or ROLLBACK. (See "history list" in the documentation.) This is row-by-row, not whole table or database. It will not "escalate the row locks to a table lock".
Each row of each table has a transaction_id. Each new transaction has a new, higher, id.
That xaction id, together with the "transaction isolation mode", determine which copy of each row your transaction can "see". So, yes, there can briefly be multiple "rows" WHERE id = 31.
Rows are locked, not tables. In some of your examples, transactions ran for a while, then stumbled over the 'same' row.
In some cases, the "gap" between rows is locked. (I did not notice that in your examples.)
Whole tables are locked only for DDL (Drop, Alter, etc), not DML (Select, Update, etc)
When a conflict occurs, a "deadlock" might occur. This is when each transaction is waiting for the other one to release a lock. One transaction is automatically rolled back.
When a conflict occurs, a "lock wait" might occur. This is when the transaction with a lock will eventually let go, letting the waiting transaction continue.
When a conflict occurs and "lock wait" occurs, innodb_lock_wait_timeout controls how long before giving up.
Every statement is inside a transaction. When autocommit=ON, each statement is a transaction unto itself. (Your last example is missing a BEGIN, in which case Process 2 has 2 separate transactions.)

In your first example, the isolation mode of read_uncommitted would let you see the other transaction's changes as they happened. That is a rarely used mode. The other modes won't let you see the changes until they are COMMITted, and it would never see the changes if it were ROLLBACK'd. (Yes, there was a copy of each changed row.)

repeatable_read mode (and others) effectively limit you to seeing only the rows with your transaction_id or older. Hence, even at 12:00:31, you still see "John".

General advice:

Don't write a transaction that runs longer than a few seconds
Remember to use SELECT ... FOR UPDATE where appropriate -- this adds a stronger lock on the rows in the SELECT just in case they will be updated or deleted in the transaction.
Where practical, it is better to have one INSERT adding 100 rows; that will be 10 times as fast as 100 single-row INSERTs. (Similarly for UPDATE and DELETE.)
Use SHOW ENGINE=InnoDB STATUS; (I find it useful in dealing with deadlocks, but cryptic for other purposes.)

MYSQL Using Transactions within an Event Schduler

As you have observed the event only consists of the code to the start transaction; (which will run forever on your system until you either drop or disable). The code appears to run once but that's only because it drops through. 'As with stored routines, you can use compound-statement syntax in the DO clause by using the BEGIN and END keywords' (https://dev.mysql.com/doc/refman/8.0/en/create-event.html), in your case

delimiter $$
CREATE EVENT IF NOT EXISTS monitored_15min
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
begin
START TRANSACTION;

TRUNCATE dashboard.monitored;
INSERT INTO dashboard.monitored (brand,product,Status,old_Price,Current_Price,Product_Title)
SELECT
amp.brand,
amp.product,
amp.Status,
amp.old_Price,
amp.Current_Price,
Product_Title

FROM results.amp;

COMMIT;
end $$
delimiter ;

See the manual for more information AND ensure you have enabled the event scheduler.

Will errors handled in sub mysql transactions trigger rollbacks in the mysql transaction that calls it

Statements That Cause an Implicit Commit

Transaction-control and locking statements. BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.

You may investigate this in details: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=76bc26fa4e96b5ea3643aabe8161feea

MySQL Transactions Within Transactions