. NET to java.05. Why doesn't MySQL have nolock

Today, a problem suddenly came to mind, originally in order to improve SQL Server performance, the Company rules query statements are generally added with (NOLOCK)

Now turn Java, use MySQL why not mention this thing?

First in MySQL wrote a query statement, than the appearance of adding nolock, suggesting that the syntax is not correct, is it used readuncommitted? Still hints that the syntax is not correct,

It seems that MySQL does not support the syntax of NOLOCK.

Then the question becomes, Why does MySQL need not support syntax such as NOLOCK, or if MySQL does not support NOLOCK, modify the record to cause the lock table?

So I did the following experiment.

Two MySQL connection was opened, (by the way, because the client is SQLyog, I thought the same as SQL Server Management Studio every "Ask" is a connection, actually not, each connection to "create a new Connection", I have been testing myself for a long while to find this problem)

The first MySQL connection executes a query

TRANSACTION ; UPDATE SET NAME='newvalue'WHERE id=1

Because the transaction is not committed, if it is SQL Server by default, the second connection queries the same record, which is bound to be blocked. If the SQL Server query adds a nolock read to the dirty value "newvalue" that is not yet commit

Second MySQL connection I execute query

SELECT *  from ' testtable '

I found that neither the blocking nor the dirty reads occurred , the old values were queried, and the new values were not read to the uncommitted newvalue

This means that MySQL and SQL Server have different mechanisms for maintaining transactions by default.

SQL Server by default, a transaction modifies a value, before the transaction commits, blocking other connections to read the value of the change, if the NOLOCK read is modified to commit the value (that is, dirty read, because the value may eventually be rolled back)

MySQL by default, a transaction modifies a value that, before the transaction commits, does not block the other connection to read the value in the modification, and reads the value before the modification.

For internet companies, most scenarios do not want to write a transaction to block the read,

So SQL Server recommends adding NOLOCK

MySQL itself does not block, nolock also meaningless.

. NET to java.05. Why doesn't MySQL have nolock