| 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: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 1.0.7 | OS: | Windows (Windows) |
| Assigned to: | CPU Architecture: | Any | |
[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.
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();