# 确认 session1、session2 连在同一节点
# 如果不是连在同一节点上，ddl是可以执行成功的，参考“跨节点 DDL-DML 冲突”

show variables like'hostname';

#session1
BEGIN;
UPDATE sbtest1 SET k =0 WHERE id =999;

#session2
ALTER TABLE sbtest1 ADD COLUMN new_column VARCHAR(255);

#查看 metadata_locks，可以看到：
#第一行LOCK_STATUS=GRANTED的记录正是 session1，表示已获得 MDL 锁；
#第二行LOCK_STATUS=PENDING的记录正是 session2，表示获取 MDL 锁被挂起。
#需要在开头加上 broadcast HINT 指定在所有节点广播查询。

/*#broadcast*/select * from performance_schema.metadata_locks where OBJECT_NAME='sbtest1' \\G
***************************1.row***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: sbtest1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140384374661472
            LOCK_TYPE: SHARED_WRITE
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:6373
      OWNER_THREAD_ID: 4879164
       OWNER_EVENT_ID: 1
***************************2.row***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: sbtest1
          COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140375267009376
            LOCK_TYPE: SHARED
        LOCK_DURATION: EXPLICIT
          LOCK_STATUS: PENDING
               SOURCE: ddl_executer.cc:245
      OWNER_THREAD_ID: 4879122
       OWNER_EVENT_ID: 1
2 rows in set(0.02 sec)

#当查询多次都发现 LOCK_STATUS: GRANTED 的会话一直没有变化时，可通过 OWNER_THREAD_ID 定位到持有锁的线程所对应的 SESSION ID，在确认该会话可安全终止后，先通过 KILL 命令结束该会话，再重新发起 DDL 操作。
/*#broadcast*/select*from performance_schema.threads where THREAD_ID=4879164\\G
***************************1.row***************************
          THREAD_ID: 4879164
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 2346946
   PROCESSLIST_USER: xxxxx
   PROCESSLIST_HOST: xxx.xxx.xxx.xxx
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 1330
  PROCESSLIST_STATE:
   PROCESSLIST_INFO:
   PARENT_THREAD_ID:
               ROLE:
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: TCP/IP
       THREAD_OS_ID: 45448
     RESOURCE_GROUP:
       SQLEngine_id: node-tdsql3-***-002
1rowinset(0.02 sec)

#KILL 持锁者（LOCK_STATUS: GRANTED 会话）
#session1 被杀

#session2
ALTERTABLE sbtest1 ADDCOLUMN new_column VARCHAR(255);
Query OK,0rows affected (1.67 sec)
Records: 0  Duplicates: 0  Warnings: 0


# 这里 session1、session2 无论是否连到同一节点，结果都是一样的；因为行锁是存储层的，两个会话发生行锁冲突，说明最终都访问了相同存储节点的主副本

#session1
SELECT id FROM sbtest1 ORDER BY id limit10;
+----+
| id |
+----+
|1|
|3|
|5|
|7|
|9|
|11|
|12|
|13|
|14|
|15|
+----+
10rowsinset(9.23 sec)

BEGIN;
UPDATE sbtest1 SET k=50000WHERE id<=11AND id>=5;

#session2
INSERT INTO sbtest1(id) VALUES(8);

#查询当前节点上，持锁者（Lock Holder）的悲观锁信息。
#TDStore 的 range lock 左闭右开，可以看到列 ENGINE_LOCK_ID 中显示为 [5,12) 区间。
tdsql3_sys_local@localhost [(none)]> SELECT data_locks.* FROM performance_schema.data_locks, performance_schema.data_lock_waits WHERE blocking_engine_lock_id = engine_lock_id \\G;
*************************** 1. row ***************************
                             ENGINE: RocksDB
                     ENGINE_LOCK_ID: 29818886123683850_[0004596580000005,000459658000000C)
              ENGINE_TRANSACTION_ID: 29818886123683850
                          THREAD_ID: 924280
                           EVENT_ID: NULL
                      OBJECT_SCHEMA: test
                        OBJECT_NAME: sbtest1
                     PARTITION_NAME: NULL
                  SUBPARTITION_NAME: NULL
                         INDEX_NAME: NULL
              OBJECT_INSTANCE_BEGIN: 140666287763504
                          LOCK_TYPE: PRE_RANGE
                          LOCK_MODE: Write
                        LOCK_STATUS: GRANTED
                          LOCK_DATA: NULL
                          START_KEY: 0004596580000005
                            END_KEY: 000459658000000C
                  EXCLUDE_START_KEY: 0
           BLOCKING_TRANSACTION_NUM: 1
BLOCKING_CHECK_READ_TRANSACTION_NUM: 0
                    READ_LOCKED_NUM: 0
                          TINDEX_ID: 285029
                    DATA_SPACE_TYPE: DATA_SPACE_TYPE_USER
               REPLICATION_GROUP_ID: 88168
                KEY_RANGE_REGION_ID: 722680
                     PROCESSLIST_ID: 3029964
                            NODE_ID: 2
                          NODE_NAME: node-tdsql3-******-002
1 row in set (0.17 sec)


# 查看持锁者（Lock Holder）的会话信息。
# 说明：data_locks 表中的 THREAD_ID 为 Performance Schema 的内部线程 ID。如需查询对应的会话信息，请使用 PROCESSLIST_ID 字段关联 information_schema.processlist 表。此外，NODE_ID 和 NODE_NAME 字段可用于定位该事务所在的 SQLEngine 节点。
tdsql3_sys_local@localhost [(none)]> select * from information_schema.processlist where id=3029964\\G;
*************************** 1. row ***************************
           ID: 3029964
         USER: tdsql3_sys_local
         HOST: localhost
           DB: test
      COMMAND: Sleep
         TIME: 546
        STATE: 
         INFO: NULL
      TIME_MS: 546165
    ROWS_SENT: 0
ROWS_EXAMINED: 5
          TID: 30086245928649
1 row in set (0.04 sec)



#KILL 持锁者（Lock Holder）后，阻塞会话执行成功。
#session1 被杀

#session2
INSERTINTO sbtest1(id)VALUES(8);
Query OK,1row affected (43.78 sec)

#注意：高并发场景下，可能等待会话（Lock Waiter）队列比较长，这样可能又会再次出现持锁者（Lock Holder），可能需要多杀几次。