我遇到 MySQL的SELECT问题.. FOR UPDATE,这是我试图运行的查询：

SELECT * FROM tableName WHERE HostName='UnkNownHost' 
        ORDER BY UpdateTimestamp asc limit 1 FOR UPDATE

在此之后,相关的线程将执行UPDATE并更改HostName,然后它应该解锁该行.

我正在运行一个多线程的java应用程序,所以3个线程正在运行这个sql语句,但是当线程1运行它时,它不会将其结果从线程2&因此,线程2& 3获得相同的结果,他们可以更新同一行.

每个线程也都有自己的MysqL连接.

我正在使用Innodb,事务隔离= READ-COMMITTED,并且在执行select for update之前自动提交已关闭

我可能会错过什么吗？或许还有更好的解决方案？
非常感谢.

代码：

public BasicJDBCDemo()
{
    Le_Thread newThread1=new Le_Thread();
    Le_Thread newThread2=new Le_Thread();
    newThread1.start();
    newThread2.start();         
}

线程：

class Le_Thread extends Thread  
{

    public void run() 
    {
    tring name = Thread.currentThread().getName();
        System.out.println( name+": Debut.");
    long oid=Util.doSelectLockTest(name);
    Util.doUpdateTest(oid,name);        
    }

}

选择 ：

public  static long doSelectLockTest(String threadName)
  {
    System.out.println("[OUTPUT FROM SELECT Lock ]...threadName="+threadName);
    PreparedStatement pst = null;
    ResultSet rs=null;
    Connection conn=null;
    long oid=0;
    try
    {
     String query = "SELECT * FROM table WHERE Host=? 
                               ORDER BY Timestamp asc limit 1 FOR UPDATE";


      conn=getNewConnection();
      pst = conn.prepareStatement(query);
      pst.setString(1,DbProperties.UnkNownHost);
      System.out.println("pst="+threadName+"__"+pst);
      rs = pst.executeQuery();

      if (rs.first())
      {
        String s = rs.getString("HostName");
        oid = rs.getLong("OID");
        System.out.println("oid_oldest/host/threadName=="+oid+"/"+s+"/"+threadName);

      }   

    }
    catch (sqlException ex)
    {
      ex.printstacktrace();
    }
    finally
    {
        DBUtil.close(pst);
        DBUtil.close(rs);
        DBUtil.close(conn);
    }
    return oid;
  }

请帮忙…. ：

结果：

Thread-1: Debut.
Thread-2: Debut.
[OUTPUT FROM SELECT Lock ]...threadName=Thread-1
New connection..
[OUTPUT FROM SELECT Lock ]...threadName=Thread-2
New connection..
pst=Thread-2: SELECT * FROM b2biCheckPoint  WHERE HostName='UnkNownHost' ORDER BY UpdateTimestamp asc limit 1 FOR UPDATE
pst=Thread-1: SELECT * FROM b2biCheckPoint  WHERE HostName='UnkNownHost' ORDER BY UpdateTimestamp asc limit 1 FOR UPDATE
oid_oldest/host/threadName==1/UnkNownHost/Thread-2
oid_oldest/host/threadName==1/UnkNownHost/Thread-1
[Performing UPDATE] ... oid = 1,thread=Thread-2
New connection..
[Performing UPDATE] ... oid = 1,thread=Thread-1
pst_threadname=Thread-2: UPDATE b2bicheckpoint SET HostName='1_host_Thread-2',UpdateTimestamp=1294940161838 where OID = 1
New connection..
pst_threadname=Thread-1: UPDATE b2bicheckpoint SET HostName='1_host_Thread-1',UpdateTimestamp=1294940161853 where OID = 1

你非常困惑,但至少在编辑后事情看起来更好.有多种方法可以做到这一点,但我发现最好的方法是实际使用JDBC的ResultSet.update *方法：

首先,您需要使用ResultSet.CONCUR_UPDATABLE参数准备SELECT … FOR UPDATE语句,如下所示：

ps = conn.prepareStatement(query,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);

然后,您必须使用ResultSet实际更新表：

if(rs.next())
{
    rs.updateString(columnIndex,"new_hostname");
    rs.updateRow();
}

第三,您可能需要使用我可以在更新中看到的事务.希望你的DbUtil.close方法不会抛出任何异常,检查null等等.另外,如果你的方法变得更复杂,你也应该有回滚逻辑.

你不应该因为任何原因修改my.ini.

gtlions=# create table joinupdate1(id int,name varchar(20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
gtlions=# create table joinupdate2(id int,name varchar(20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
gtlions=# insert into  joinupdate1 values(1,'a');
INSERT 0 1
gtlions=# insert into  joinupdate1 values(2,'b');
INSERT 0 1
gtlions=# insert into  joinupdate2 values(1,'b');
INSERT 0 1
gtlions=# insert into  joinupdate2 values(2,'b');
INSERT 0 1
gtlions=# truncate table joinupdate2;
TRUNCATE TABLE
gtlions=# insert into  joinupdate2 values(1,'c');
INSERT 0 1
gtlions=# insert into  joinupdate2 values(2,'d'); 
INSERT 0 1
gtlions=# select * from joinupdate1;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

gtlions=# select * from joinupdate2;
 id | name 
----+------
  1 | c
  2 | d
(2 rows)

gtlions=# begin;
BEGIN
gtlions=# update joinupdate1 set name=(select name from joinupdate2 where joinupdate1.id=joinupdate2.id);
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
  1 | c
  2 | d
(2 rows)

gtlions=# rollback;
ROLLBACK
gtlions=# end;
WARNING:  there is no transaction in progress
COMMIT
gtlions=# select * from joinupdate1;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

gtlions=# begin;
BEGIN
gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
  1 | c
  2 | d
(2 rows)

gtlions=# rollback;
ROLLBACK
gtlions=# insert into joinupdate2 values(1,'e');
INSERT 0 1
gtlions=# begin;
BEGIN
gtlions=# update joinupdate1 set name=(select name from joinupdate2 where joinupdate1.id=joinupdate2.id);
ERROR:  more than one row returned by a subquery used as an expression  (seg0 slice2 h1:40000 pid=14123)
gtlions=# rollback;
ROLLBACK
gtlions=# begin;
BEGIN
gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
  1 | e
  2 | d
(2 rows)

gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
  1 | e
  2 | d
(2 rows)

gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
  1 | e
  2 | d
(2 rows)

gtlions=# insert into joinupdate2 values(1,'f');
INSERT 0 1
gtlions=# update joinupdate1 set name=joinupdate2.name from joinupdate2 where joinupdate1.id=joinupdate2.id;
UPDATE 2
gtlions=# select * from joinupdate1;
 id | name 
----+------
  1 | f
  2 | d
(2 rows)

gtlions=# rollback;
ROLLBACK

登录后复制

 版权声明：欢迎转载，请注明出处 https://blog.csdn.net/xinzhongtianxia/article/details/80994259

同步发表于：http://blog.lanjingdejia.com/articles/2018/07/11/1531245873812.html
许多同学或应该都听过"select … lock in share mode" 和 “select … for update”，但是或多或少会有以下几个疑问：

1. 这俩货是什么含义？
2. 和普通的select有什么区别呢？
3. 什么场景下用？用哪个？

含义

我们直接从mysql官网上看它们的定义：

SELECT … LOCK IN SHARE MODE

Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

翻译一下：在你正在读的行设置一个共享锁，其他session也可以读这些行，但是直到你读完这些行，事务提交释放锁之后，其他sesstion才能更改这些行。如果你要读取的行正在被其他session修改，那么读取会卡住，直到其他session修改完毕，读取才能继续，并且读到的是最新版本的数据。

SELECT … FOR UPDATE

For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT … LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels.

翻译一下：对于select的数据，会对这些行及其相关行加锁，效果和用update更新这些行时是一样的。其他对这些数据进行操作，无论是update也好，上面提到的SELECT … LOCK IN SHARE MODE也好，甚至在某些事务隔离级别下读取这些数据也好，所有这些操作都会被阻塞。

通俗一点

lock in share mode能保证读到的一定是最新的值，而且保证读的过程中不会被其他session修改。因为加的是共享锁，所以多个session同时lock in share mode时都可以读到数据。
select for update更严格一些，加的是排它锁(根据情况，有时还会加间隙锁)，会阻塞住所有其他session对该数据的操作。

和普通的select的区别

mysql(InnoDB)的select有三种：

• 啥也不加的普通的select
• select … lock in share mode
• select … for update

上面提到的共享锁、排它锁等等，这些锁对普通的select是没有用的，普通的select是永远不会被阻塞的。下面举例说明。

普通select和lock in share mode的区别

1. session A正在修改(update或者delete)某一行数据，如果这时session B进行普通的select，session B是不会被阻塞的，且读到的是session A修改之前的数据。如果这时session C进行select … lock in share mode，那么session C将会阻塞，直到session A所在的事务提交(或者回滚释)放锁之后，session C才会继续进行，且读到的使session A修改过后的数据。
2. session A 正在进行普通的select，如果这时session B 进行update操作，那么session B不会被阻塞。但是如果session A进行的是select … lock in share mode，那么session B的update操作会被阻塞，直到session A提交之后，session B才能继续进行。

普通select和select for update的区别

区别同上

lock in share mode和select for update的区别

1. session A正在 lock in share mode, 此时session B 进行lock in share mode 操作不会被阻塞，但是如果此时session C进行select for update的话，就会被阻塞。
2. 如果session A 正在select for update，如果此时session B 进行lock in share mode, 那么session B会被阻塞，如果此时session C也进行select for update操作，那么session C也会被阻塞。

使用场景

select … lock in share mode的使用场景

引用mysql官网的例子，有两张表：parent表和child表，向child表插入数据时，要保证child的parent在parent表中存在，删除parent表时，会将parent以及该parent的child都删掉。一般的操作是先select，得到parentId，然后向child中插入一条数据(关联parentId)，但是这样是有问题的，如果select之后，插入之前，另一个session将parent删掉了，那么向child表中插入的数据并不会受到影响，最终造成该child没有parent的状况。
根据上面提到的特性，select时用select … lock in share mode就能解决这个问题。其实select … lock in share mode 在平时用到的场景很少很少，用的比较多的还是select … for update。

select … for update的使用场景

账户表中有一个字段money，取出来后，将money更新(比如加上一个值)后再存进去。对于这个场景，如果两个session同时select，比如都取出来的是100，然后都加30, 都变成130，然后都update money，最终money的值是130，与预期的160不符。这个时候用select … for update 就能完美解决这个问题，这时因为两个session不能同时select … for update。

思考

上述select … for update的场景如果使用select … lock in share mode会怎样？
答案是，会很大概率造成死锁，造成死锁的原因是：session A和session B同时select lock in share mode, 这时都未提交事务，session A 继续执行update操作，此时因为session B事务还没提交，锁还没释放，所以session A的update操作会被阻塞，等待session B释放锁，同样的，session B此时也在等待session A提交事务释放锁，这就发生了死锁。
所以lock in share mode 使用时要仔细检查，确认该场景是否真的适用且不会发生死锁。