Menu
Does MySQL allow the use of nested transactions?
630
A nested transaction is used to provide a transactional guarantee for a subset of operations performed within the scope of a larger transaction. Doing this allows you to commit and abort the subset of operations independently of the larger transaction.
A nested transaction can lock a datum in some mode only if its parent has locked the datum in the same mode. A parent transaction's actions are considered to conflict with its child's actions but not vice versa. Thus, it cannot access a resource if a child's lock prohibits the access. Thus, the child's lock wins.
MySQL supports local transactions (within a given client session) through statements such as SET autocommit , START TRANSACTION , COMMIT , and ROLLBACK . See Section 13.3. 1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”. XA transaction support enables MySQL to participate in distributed transactions as well.
A modern disk can do ~1000 fsyncs per second, but MySQL will group multiple writes with each fsync. An okay rule-of-thumb would be 5000-15,000 writes per second, depending on things like writes per transaction, number of indexes, hardware, size of writes, etc.
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 --- From MySQL documentation:
Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms. https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
