mysql - 为什么我可以读取 MySql 中的脏行

Question

我不敢相信，我一直认为下面的内容是并发安全的。

我在一个事务中写入一行，我能够从另一个事务/命令/连接中读取脏值!为什么这是可能的(不是我的主要问题)这不是不希望的并且会造成更多的麻烦吗!？!

无论如何，我预计一旦我写入一行，在事务完成之前，其他任何东西都无法读取到该行。并且至少如果仍然可以读取该行，则将读取干净的(原始)值。 (但如果事务在运行时不使用来自另一个事务的新提交的数据，这也可能会导致问题)

我想数到 == 11。我认为这在所有 sql 变体中都是安全的。我该怎么办 1) 不读取脏值但干净 2) 在事务完成之前锁定该行？

    static MySqlConnection MakeConn()
    {
        string connStr = "server=192.168.126.128;user=root;database=TestDB;port=3306;password=a;";
        MySqlConnection conn = new MySqlConnection(connStr);
        conn.Open();
        return conn;
    }
    static Semaphore sem1 = new Semaphore(1, 1);
    static Semaphore sem2 = new Semaphore(1, 1);
    static void Main2()
    {
        Console.WriteLine("Starting Test");

        //
        sem1.WaitOne(); Console.WriteLine("1W");
        sem2.WaitOne(); Console.WriteLine("2W");
        Thread oThread = new Thread(new ThreadStart(fn2));
        oThread.Start();

        var conn = MakeConn();
        var cmd = new MySqlCommand(@"
            CREATE TABLE IF NOT EXISTS Persons
            (
            P_Id int NOT NULL,
            name varchar(255),
            count int,
            PRIMARY KEY (P_Id)
            )", conn);
        cmd.ExecuteNonQuery();

        cmd.CommandText = "delete from Persons; insert into Persons(name, count) VALUES('E', '4');";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "select count from Persons;";
        var count = (int)cmd.ExecuteScalar();

        Console.WriteLine("Finish inserting. v={0}", count);
        sem2.Release(); Console.WriteLine("2R");
        sem1.WaitOne(); Console.WriteLine("1W");


        Console.WriteLine("Starting transaction");
        using (var tns = conn.BeginTransaction())
        {
            cmd.CommandText = "update Persons set count=count+1";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "select count from Persons;";
            count = (int)cmd.ExecuteScalar();
            Console.WriteLine("count is {0}", count);

            sem2.Release(); Console.WriteLine("2R");
            sem1.WaitOne(); Console.WriteLine("1W");

            count += 5; //10

            cmd.CommandText = "update Persons set count=" + count.ToString();
            cmd.ExecuteNonQuery();
            cmd.CommandText = "select count from Persons;";
            count = (int)cmd.ExecuteScalar();
            Console.WriteLine("count is {0}", count);

            tns.Commit();
        }
        Console.WriteLine("finished transaction 1");
        sem2.Release(); Console.WriteLine("2R");
        sem1.WaitOne(); Console.WriteLine("1W");

        cmd.CommandText = "select count from Persons;";
        count = (int)cmd.ExecuteScalar();
        Console.WriteLine("count is {0}", count);

        sem2.Release(); Console.WriteLine("2R");
        //sem1.WaitOne(); Console.WriteLine("1W");

    }
    static void fn2()
    {
        int count;
        Console.WriteLine("Starting thread 2");
        sem2.WaitOne(); Console.WriteLine("1W");
        var conn = MakeConn();
        var cmd = new MySqlCommand("", conn);
        sem1.Release(); Console.WriteLine("1R");
        sem2.WaitOne(); Console.WriteLine("2W");

        using (var tns = conn.BeginTransaction())
        {
            cmd.CommandText = "update Persons set count=count+1";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "select count from Persons;";
            count = (int)cmd.ExecuteScalar();
            Console.WriteLine("count is {0}", count);

            sem1.Release(); Console.WriteLine("1R");
            sem2.WaitOne(); Console.WriteLine("2W");

            tns.Commit();
        }
        Console.WriteLine("finished transaction 2");
        sem1.Release(); Console.WriteLine("1R");
        sem2.WaitOne(); Console.WriteLine("2W");

        cmd.CommandText = "select count from Persons;";
        count = (int)cmd.ExecuteScalar();
        Console.WriteLine("count is {0}", count); //should be 11. 4 + 1x2(one each thread) += 5 from first thread == 11

        sem1.Release(); Console.WriteLine("1R");
    }

控制台

Starting Test
1W
2W
Starting thread 2
Finish inserting. v=4
2R
1W
1R
1W
Starting transaction
count is 5
2R
2W
count is 6
1R
1W
count is 10
finished transaction 1
2R
2W
finished transaction 2
1R
1W
count is 10
2R
2W
count is 10
1R

Answer 1

INNODB 表支持事务，MYISAM 不支持，确保你创建的是 innodb 表。