Bug #22400 Nested transactions
Submitted: 15 Sep 2006 17:44 Modified: 3 Jan 2007 11:18
Reporter: Vlad Untu Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:1.0.7 OS:Microsoft Windows (Windows)
Assigned to: CPU Architecture:Any

[15 Sep 2006 17:44] Vlad Untu
Description:
Since nested transactions in MySql are not supported the connector should not accept multiple transactions on the same connection. Currently the connector raises no error if on the same connection you start nested transactions.

How to repeat:
MySqlConnection conn = new MySqlConnection("Server = localhost; User ID = root; Database = test;";

MySqlTransaction t = conn.BeginTransaction();

// do some inserts or updates

MySqlTransaction t_nested = conn.BeginTransaction();

// do some inserts or updates

t_nested.Rollback(); //rolls back corectly inserts and updates made on the t_nested transaction

// do some inserts or updates on transaction t

t.Commit(); //OR t.Rollback() has no effect

---------

the above code behaves so when you use AUTOCOMMIT=0

Suggested fix:
When you start a nested transaction on the same connection the connector shoul throw a NotSupportedException();
[20 Sep 2006 11:23] Tonci Grgin
Hi Vlad and thanks for your problem report. It comes down to two scenarios:
Scenario 1:
trans1. Begin Transaction
  trans1. Modify Data
  trans1. Commit
  trans1. Begin Transaction

  trans2. Begin Transaction
  trans2. Modify Data
  trans2. Rollback

  trans1. Modify Data
trans1. Commit OR RollBack -> AV as Data in trans1 is allready commited

Scenario 2:
trans1. Begin Transaction
  trans2. Begin Transaction
  trans2. Modify Data
  trans2. Rollback

  trans1. Modify Data
trans1. RollBack -> Data in trans1 is allready commited, no AV

Mysql server 5.0.24BK on WinXP Pro SP2 localhost with c/NET 1.0.7 SVN and NET framework 2.0.

Test case:
            //UNREMARK REMARKED LINES TO SEE SCENARIO 1
            MySql.Data.MySqlClient.MySqlConnection conn;
            conn = new MySql.Data.MySqlClient.MySqlConnection();
            MySqlCommand cmd = new MySqlCommand();
            MySqlCommand cmdn = new MySqlCommand();

            conn.ConnectionString = "server=127.0.0.1;uid=root;pwd=;database=test;";
            conn.Open();
            
            MySqlTransaction t = conn.BeginTransaction();

            cmd.Connection = conn;
            cmd.Transaction = t;
            //cmd.CommandText = "DROP TABLE IF EXISTS `test`.`testtable`";
            //cmd.ExecuteNonQuery();
            //cmd.CommandText = "CREATE TABLE  `test`.`testtable` ("+
            //    "`bokey` varchar(150) default NULL,"+
            //    "`checkout_id` varchar(50) default NULL,"+
            //    "`shop_name` varchar(250) default NULL,"+
            //    "`offer_id` varchar(100) default NULL,"+
            //    "`delivery` varchar(150) default NULL,"+
            //    "`url` text,"+
            //    "`price` decimal(10,2) default NULL,"+
            //    "`category` varchar(255) default NULL,"+
            //    "`small_picture` varchar(255) default NULL,"+
            //    "`expire` varchar(50) default NULL,"+
            //    "`product` varchar(255) default NULL,"+
            //    "`product_cell` varchar(255) default NULL,"+
            //    "`manufactor` varchar(150) default NULL,"+
            //    "`ean` varchar(15) default NULL,"+
            //    "`han` varchar(20) default NULL,"+
            //    "`pzn` varchar(20) default NULL,"+
            //    "`asin` varchar(15) default NULL,"+
            //    "`shop_description` text,"+
            //    "`used` varchar(15) default NULL,"+
            //    "`rebuild` varchar(15) default NULL,"+
            //    "`contract` varchar(50) default NULL,"+
            //    "`porto` varchar(150) default NULL,"+
            //    "`shop_id` int(11) default NULL,"+
            //    "KEY `ean` (`ean`),"+
            //    "KEY `pzn` (`pzn`),"+
            //    "KEY `shop_id` (`shop_id`,`offer_id`),"+
            //    "KEY `han` (`han`),"+
            //    "KEY `bokey` (`bokey`),"+
            //    "KEY `shop_name` (`shop_name`(50))"+
            //    ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
            //cmd.ExecuteNonQuery();
            //t.Commit();
            
            //t.Connection.BeginTransaction();
            MySqlTransaction t_nested = conn.BeginTransaction();
            cmdn.Connection = conn;
            cmdn.Transaction = t_nested;
            
            cmdn.CommandText = "INSERT INTO testtable (bokey,checkout_id,shop_name) VALUES ('a', 'b', 'c')";
            cmdn.ExecuteNonQuery();

            t_nested.Rollback();

            cmd.CommandText = "INSERT INTO testtable (bokey,checkout_id,shop_name) VALUES ('a1', 'b1', 'c1')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO testtable (bokey,checkout_id,shop_name) VALUES ('a2', 'b2', 'c2')";
            cmd.ExecuteNonQuery();

            t.Rollback(); //OR t.Commit() has no effect
[20 Sep 2006 14:29] Reggie Burnett
fixed in 1.0.8 and 5.0.1
[20 Sep 2006 19:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12277
[20 Sep 2006 19:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12278
[29 Sep 2006 14:31] Reggie Burnett
initial fix not working so it's being rolled out.  Will fix using savepoints in 5.0.2
[29 Dec 2006 15:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17464
[29 Dec 2006 15:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17465
[29 Dec 2006 15:42] Reggie Burnett
Fixed in 1.0.9 and 5.0.3
[3 Jan 2007 11:18] MC Brown
A note has been added to the 1.0.9 and 5.0.3 changelogs.