我如何看到TRANSACTION_REPEATABLE_READ在JDBC/MySQL中的运行效果? [英] How can I see TRANSACTION_REPEATABLE_READ in action with JDBC / MySQL?
问题描述
我目前在我的计算机上运行MySQL Server(5.7.16)，并将此示例行存储在此服务器上名为"Sakila"的数据库中:
I have MySQL Server (5.7.16) running on my computer currently, and have this sample row in a database called 'Sakila' on this Server:
mysql> SELECT * FROM actor WHERE last_name = 'tugay';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 201 | koray | tugay | 2017-06-11 21:42:08 |
+----------+------------+-----------+---------------------+
这是JDBC为我的MySQL服务器的TRANSACTION ISOLATION LEVEL返回的内容:
This is what JDBC returns for TRANSACTION ISOLATION LEVEL for my MySQL Server:
public static void main(String[] args) throws SQLException, InterruptedException {
final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
System.out.println(connection.getTransactionIsolation()); // Prints 4!
connection.close();
}
其中4是TRANSACTION_REPEATABLE_READ的常量，它是记录如下:
where 4 here is the constant for TRANSACTION_REPEATABLE_READ which is documented as follows:
int TRANSACTION_REPEATABLE_READ = 4;
A constant indicating that dirty reads are prevented.
This level prohibits a transaction from reading a row with uncommitted changes in it.
我想看看这种行为，所以我有2种主要方法，首先是:
I want to see this behavior so I have 2 main methods, first being:
import java.sql.*;
import static java.sql.DriverManager.*;
public class Foo {
public static void main(String[] args) throws SQLException, InterruptedException {
final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
connection.setAutoCommit(false);
final Statement statement = connection.createStatement();
statement.executeUpdate("UPDATE actor SET first_name = 'ALTERED' WHERE first_name = 'koray'");
System.out.println("Sleeping!");
// Here I am expecting MySQL to lock the Row so no other Connection
// can read data from it. This row will be updated, so unless the transaction is
// either commited or rolled back, the read data will be 'dirty'.
Thread.sleep(5000);
connection.commit();
connection.close();
}
}
如您所见，在自动提交模式下，事务不，并且我将该行锁定了5秒钟.在此过程处于休眠状态时，我也不断运行以下代码:
As you see, the transaction is not in auto-commit mode, and I am locking the row for 5 seconds. While this process is sleeping, I constantly run the following code as well:
public class Bar {
public static void main(String[] args) throws SQLException, InterruptedException {
final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
connection.setAutoCommit(false);
final Statement statement = connection.createStatement();
final ResultSet resultSet = statement.executeQuery("SELECT * FROM actor WHERE last_name = 'tugay'");
resultSet.next();
System.out.println(resultSet.getString("first_name"));
connection.close();
}
}
但是，即使我在控制台中看到正在休眠"的文本，只要我在Bar中运行main方法，我也会看到类似的值
But even I see "sleeping" text in the console, as long as I run the main method in Bar, I will see values like
koray
koray
koray
ALTERED
我的期望是从不见过koray ，因为那是一个脏数据"，第一个Java进程应该锁定该行.
My expectation was never to see koray since that is a "dirty data", where the first java process should be locking the row.
我缺少什么?如何锁定该行?
What am I missing and how can I lock this row?
推荐答案
您应该再次阅读该定义:
You should read the definition again:
此级别禁止事务读取未提交的行 发生变化.
This level prohibits a transaction from reading a row with uncommitted changes in it.
ALTERED是脏数据，因为未提交. Koray是正确的.酒吧禁止看到"ALTERED".只要未提交交易，您就会不断看到"koray".
ALTERED is the dirty data, since it's not commited. Koray is the correct one. Bar is prohibited to see "ALTERED". You'll keep seeing "koray" as long as the transaction wasn't commited.
这篇关于我如何看到TRANSACTION_REPEATABLE_READ在JDBC/MySQL中的运行效果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
