ACCESS LOCK WITH JOIN

在连接操作中，如果用LOCKING ROW FOR ACCESS，那么会把两个表都变成ACCESS锁。如下所示，

LOCKING ROW FOR ACCESS

SELECT *

FROM TABLE_1 a, TABLE_2 b

那么TABLE_1和TABLE_2都是ROW HASH LEVEL ACCESS LOCK。

 

但是，一个比较晦涩的情况是：

SHOW VIEW test_views.test_table_v

 

REPLACE VIEW test_views.test_table_v

AS LOCKING ROW FOR ACCESS

SELECT * FROM test_tables.test_table_t;

 

那么，当我们运行如下查询语句时，会得到

EXPLAIN      

SELECT        *

FROM            test_tables.base_table_1 a

LEFT JOIN

    test_views.test_table_v b

ON      a.id = b.id

;

 

1) First, we lock test_tables.a for access, and we lock

     test_tables.test_table_t in view

     test_views.test_table_v for access.

因为这个查询等价于

EXPLAIN

LOCKING ROW FOR ACCESS

SELECT        *

FROM            test_tables.base_table_1 a

LEFT JOIN

     test_tables.test_table_t b

ON      a.id = b.id

===============================================

如果不是LOCKING ROW FOR ACCESS 而是LOCKING TABLE FOR ACCESS的话，情况就是没有LOCK TABLE Modifier的table加read lock。

SHOW VIEW test_views.test_table_2_v

 

REPLACE VIEW test_views.test_table_2_v

AS LOCK TABLE test_tables. test_table_2_t FOR ACCESS

SELECT * FROM test_tables. test_table_2_t;

 

EXPLAIN      

SELECT        *

FROM            test_tables.base_table_1 a

LEFT JOIN

    test_views.test_table_2_v b

ON      a.id = b.id

;

  1) First, we lock a distinct gdw_tables."pseudo table" for read on a

     RowHash to prevent global deadlock for gdw_tables.a.

  2) Next, we lock gdw_tables.a for read, and we lock

     test_tables. test_table_2_t in view test_views.test_table_2_v for access.

 

 

 等价于

EXPLAIN

LOCKING TABLE  test_tables. test_table_2_t FOR ACCESS

SELECT        *

FROM            test_tables.base_table_1 a

LEFT JOIN

    test_tables. test_table_2_t b

ON      a.id = b.id

;

 1) First, we lock a distinct gdw_tables."pseudo table" for read on a

     RowHash to prevent global deadlock for gdw_tables.a.

  2) Next, we lock gdw_tables.a for read, and we lock

     test_tables. test_table_2_t for access.