如何使用JDBC和MySQL释放等待的行，直到其锁定被释放? [英] How can I make a row being read wait until a lock on it is released using JDBC and MySQL?

问题描述

所以我有一个小例子.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import static java.sql.DriverManager.getConnection;

public class Bar {
    public static void main(String[] args) throws SQLException, InterruptedException {
        final long start = System.currentTimeMillis();
        final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
        final Statement statement = connection.createStatement();
        statement.executeUpdate("UPDATE actor SET first_name = 'bar' WHERE last_name = 'tugay'");
        statement.close();
        connection.close();
        final long end = System.currentTimeMillis();
        System.out.println("Took: " + (end - start)); // Will print around 350ms
    }
}

当我执行这小段代码时，它将打印出大约350到400毫秒的值，这很好.

and when I execute this small piece of code, it will print out values around 350 ~ 400 milliseconds, which is fine.

现在，当我第一次启动以下代码时..

Now when I first start the following code..

import java.sql.*;

import static java.sql.DriverManager.*;

public class Foo {

    public static void main(String[] args) {
        final Connection connection;
        try {
            connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
            connection.setAutoCommit(false);

            final Statement statement = connection.createStatement();
            statement.executeUpdate("UPDATE actor SET first_name = 'foo' WHERE last_name = 'tugay'");

            System.out.println("Sleeping!");
            Thread.sleep(15000); // Sleep for 15 seconds..

            connection.commit();
            connection.close();
        } catch (SQLException | InterruptedException e) {
            e.printStackTrace();
        }
    }
}

然后运行Bar.java，我将获得大约12到13秒的值，这意味着当Foo.java锁定"姓氏='tugay'的行时，Bar.java只是等待，然后将first_name设置为酒吧".

and then run Bar.java, I will get values around 12 - 13 seconds, which means while Foo.java "locks" the rows where lastname = 'tugay', Bar.java just waits, and then sets the first_name to 'bar'.

如果Bar.java尝试读取last_name ='tugay'的行，我想得到相同的行为.这就是我的代码:

I would like to get same behavior if Bar.java tries to read rows where last_name = 'tugay'. So this is the code I have:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static java.sql.DriverManager.getConnection;

public class Bar {
    public static void main(String[] args) throws SQLException, InterruptedException {
        final long start = System.currentTimeMillis();
        final Connection connection = getConnection("jdbc:mysql://localhost:3306/sakila", "root", "root");
        final Statement statement = connection.createStatement();
        final ResultSet resultSet = statement.executeQuery("SELECT first_name FROM actor WHERE last_name = 'tugay'");
        resultSet.next();
        System.out.println(resultSet.getString("first_name"));
        statement.close();
        connection.close();
        final long end = System.currentTimeMillis();
        System.out.println("Took: " + (end - start));
    }
}

首先，在数据库中，当我启动Foo.java时，其值first_name是"koray"，而当我运行Bar.java时，它的值睡眠"时，我将得到:

Given, initally the value first_name is "koray" in the database, when I start Foo.java and while it "sleeps", when I run Bar.java I will get:

koray
Took: 390

有没有办法让Bar.java在读取时也等待，就像它在更新时等待一样?

Is there a way to make Bar.java wait while reading as well, just like it waits when updating?

推荐答案

您需要如果您不打算更新该行，而只是想确保该行上没有其他事务在运行，请获取意图共享(IS)锁:

If you do not intend to update the row but merely want to ensure that no other transaction is working on the row, obtain an Intention Shared (IS) lock:

SELECT first_name FROM actor WHERE last_name = 'tugay' LOCK IN SHARE MODE

如果打算在结果返回后更新行，请获取Intent eXclusive(IX)锁:

If you intend to update the row after the result comes back, obtain an Intention eXclusive (IX) lock:

SELECT first_name FROM actor WHERE last_name = 'tugay' FOR UPDATE

这两个查询都将阻塞，直到请求的锁可用为止.由于IS和IX锁与正在执行或已在行上进行更新(并且尚未提交或回滚)的事务所持有的排他X锁不兼容，因此上述查询中的任何一个都将阻塞，直到其他事务通过提交或回滚释放其X锁.

Both of these queries will block until the requested lock is available. Since IS and IX locks are incompatible with the exclusive X lock held by the transaction that is doing or has done an update on the row (and has not yet committed or rolled back), either of the above queries will block until that other transaction releases its X lock by either committing or rolling back.

然后该事务才获取锁并接收其结果.

Only then does this transaction obtain the locks and receive its result.

最后，该事务最终通过提交或回滚来释放它获得的锁.

Finally, this transaction eventually releases the locks it obtained, by committing or rolling back.

另请参见 https ://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-shared-exclusive-locks

这篇关于如何使用JDBC和MySQL释放等待的行，直到其锁定被释放?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！