Why can i read dirty rows in MySql

Question

I cant believe this, i always throught the below would be concurrency safe.

I write to a row in one transaction and i am able to read the dirty value from another transaction/command/connection! Why is this possible (not my main question) isnt this not desired and cause more troubles!?!

Anyways, i expected that once i write to a row nothing else will be able to read to the row until the transaction is finished. And at least if the row can be still read that the clean (original) value will be read. (but maybe that would cause problems as well if the transaction doesnt use the newly commited data from the other transaction when it is ran)

I would like count to == 11. I thought this would be safe in all variants of sql. What can i do to either 1) Not read the dirty value but clean 2) Have that row be locked until the transaction is finished?

    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");
    }

console

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 tables support transactions, MYISAM doesn't not, make sure you are creatinng innodb tables.