OceanBase V3.2 分布式死锁测试分析

select t.tenant_id, t.tenant_name, t.zone_list, t.primary_zone, t.locality, u.resource_pool_name, u.zone, u.svr_ip 
from gv$tenant t join gv$unit u on (t.tenant_id=u.tenant_id)
where t.tenant_id=effective_tenant_id()
order by t.tenant_id, u.zone, u.svr_ip;

create table bmsql_oorder (
  o_w_id       integer      not null,
  o_d_id       integer      not null,
  o_id         integer      not null,
  o_c_id       integer,
  o_carrier_id integer,
  o_ol_cnt     integer,
  o_all_local  integer,
  o_entry_d    timestamp,
  primary key (o_w_id, o_d_id, o_id)
)  partition by hash (o_w_id) partitions 12 ;

SELECT  t5.tenant_name, t5.primary_zone tnt_primary_zone,  t4.database_name, t4.primary_zone db_primary_zone,  t3.tablegroup_name, t3.primary_zone tg_primary_zone,  conv(t1.table_id,10,16) table_id, t1.primary_zone ,  t1.table_name,   t2.partition_id,  t2.role,  t2.zone,  concat(t2.svr_ip, ':', t2.svr_port) observer,  round(t2.data_size  1024  1024) data_size_mb,  t2. row_count
 FROM  gv$table t1  JOIN gv$partition t2 ON  (t1.tenant_id = t2.tenant_id AND t1.table_id = t2.table_id)  
 LEFT JOIN __all_virtual_tablegroup t3 ON  (t1.tenant_id = t3.tenant_id  AND t1.tablegroup_id = t3.tablegroup_id)   
 JOIN gv$database t4 ON  (t1.tenant_Id = t4.tenant_id  AND t1.database_id = t4.database_id)   
 JOIN gv$tenant t5 ON  (t1.tenant_id = t5.tenant_id)
WHERE  t5.tenant_id = 1002  AND t2.role = 1 AND t4.database_name = 'TPCCDB' AND t1.table_name = 'bmsql_oorder'
ORDER BY  t5.tenant_name,  t4.database_name,  t3.tablegroup_name,  t2.partition_id;

select '0' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p0) group by o_w_id 
 union 
select '1' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p1) group by o_w_id 
 union
select '2' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p2) group by o_w_id 
 union 
select '3' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p3) group by o_w_id 
 union 
select '4' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p4) group by o_w_id 
 union 
select '5' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p5) group by o_w_id 
 union 
select '6' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p6) group by o_w_id 
 union 
select '7' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p7) group by o_w_id 
 union 
select '8' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p8) group by o_w_id 
 union 
select '9' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p9) group by o_w_id 
 union 
select '10' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p10) group by o_w_id 
 union 
select '11' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p11) group by o_w_id 
 union 
select '12' as "partition id", o_w_id "warehouse id" , count(*) cnt from tpccdb.bmsql_oorder partition (p0) group by o_w_id 
;

select * from tpccdb.bmsql_oorder 
where o_w_id in (4,5,6) and o_d_id = 1 and o_id in (1,2)
order by o_w_id ,o_d_id ,o_id ;

mysql> begin;select connection_id(); update bmsql_oorder set o_ol_cnt = o_ol_cnt+10, o_entry_d = now() where o_w_id = 4 and o_d_id = 1 and o_id = 1 ;
Query OK, 0 rows affected (0.00 sec)


+-----------------+
| connection_id() |
+-----------------+
|      3221870232 |
+-----------------+
1 row in set (0.00 sec)


Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> begin;select connection_id(); update bmsql_oorder set o_ol_cnt = o_ol_cnt+10, o_entry_d = now() where o_w_id = 5 and o_d_id = 1 and o_id = 1 ;
Query OK, 0 rows affected (0.03 sec)


+-----------------+
| connection_id() |
+-----------------+
|      3221742394 |
+-----------------+
1 row in set (0.01 sec)


Query OK, 1 row affected (0.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

update bmsql_oorder set o_ol_cnt = o_ol_cnt+10, o_entry_d = now() where o_w_id = 5 and o_d_id = 1 and o_id = 1 ;

update bmsql_oorder set o_ol_cnt = o_ol_cnt+10, o_entry_d = now() where o_w_id = 4 and o_d_id = 1 and o_id = 1 ;

mysql> update bmsql_oorder set o_ol_cnt = o_ol_cnt+10, o_entry_d = now() where o_w_id = 4 and o_d_id = 1 and o_id = 1 ;


ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


mysql> show global variables where variable_name in ('ob_query_timeout','ob_trx_idle_timeout','ob_trx_lock_timeout','ob_trx_timeout');
+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| ob_query_timeout    | 10000000   |
| ob_trx_idle_timeout | 1000000000 |
| ob_trx_lock_timeout | -1         |
| ob_trx_timeout      | 1000000000 |
+---------------------+------------+
4 rows in set (0.01 sec)

+ 前提条件
- 当前登录账号具备集群的管理权限。
- 当前集群版本为 V3.2.0 及以上，且集群非只读集群。
- 集群未开启死锁自动检测功能。


+ 操作步骤
1. 登录 OCP 。默认进入 **集群** 页面。    
2. 在 **集群** 页面的 **集群列表** 区域，选择待操作的集群并单击其集群名。    
3. 单击 **总览** 页面右上角的 **...** 图标，在菜单栏中单击 **开启死锁自动检测** 。    
4. 在对话框中单击 **确定** ，即可开启死锁自动检测功能。

select gmt_create , module, event, name1, value1, name2, value2,name3, value3  
from `__all_rootservice_event_history` e 
where module in ('root_service') and event='admin_set_config'
order by gmt_create desc limit 5;

alter system set `_lcl_op_interval` = '25ms';
select gmt_modified , svr_type, svr_ip , name, data_type , value ,`section` ,`scope` ,edit_level  
from `__all_sys_parameter` p where p.name='_lcl_op_interval' ;

select tenant_id, event_id, svr_ip, detector_id, usec_to_time(report_time) report_time, cycle_idx, cycle_size, role, priority, usec_to_time(create_time) create_time, start_delay,visitor, resource, extra_name1,extra_value1
from __all_virtual_deadlock_event_history
order by create_time desc limit 10;

set session ob_query_timeout=800000000;
begin;select now(),connection_id(); update bmsql_oorder set o_ol_cnt = o_ol_cnt+10, o_entry_d = now() where o_w_id = 5  and o_d_id = 1 and o_id = 1 ;

set session ob_query_timeout=800000000;
begin;select now(),connection_id(); update bmsql_oorder set o_ol_cnt = o_ol_cnt+10, o_entry_d = now() where o_w_id = 6  and o_d_id = 1 and o_id = 1 ;

mysql> update bmsql_oorder set o_ol_cnt = o_ol_cnt+10, o_entry_d = now() where o_w_id = 5 and o_d_id = 1 and o_id = 1 ;
ERROR 1213 (HY000): Deadlock