FOR UPDATE · k8s原理与实践

[TOC] # FOR UPDATE FOR UPDATE 算子用于对表中的数据进行加锁操作。 OceanBase 数据库支持的 FOR UPDATE 算子包括 FOR UPDATE 和 MULTI FOR UPDATE。 FOR UPDATE 算子执行查询的一般流程如下： 1. 首先执行`SELECT`语句部分，获得查询结果集。 2. 对查询结果集相关的记录进行加锁操作。 ## FOR UPDATE FOR UPDATE 用于对单表（或者单个分区）进行加锁。如下示例中，Q1 查询是对 t1 表中满足`c1 = 1`的行进行加锁。这里 t1 表是一张单分区的表，所以 1 号算子生成了一个 FOR UPDATE 算子。 ~~~ obclient>CREATE TABLE t1(c1 INT, c2 INT); Query OK, 0 rows affected (0.12 sec) obclient>CREATE TABLE t2(c1 INT, c2 INT); Query OK, 0 rows affected (0.12 sec) obclient>INSERT INTO t1 VALUES(1, 1); Query OK, 0 rows affected (0.12 sec) obclient>INSERT INTO t1 VALUES(2, 2); Query OK, 1 rows affected (0.12 sec) obclient>INSERT INTO t1 VALUES(3, 3); Query OK, 1 rows affected (0.12 sec) obclient>INSERT INTO t2 VALUES(1, 1); Query OK, 1 rows affected (0.12 sec) obclient>INSERT INTO t2 VALUES(2, 2); Query OK, 1 rows affected (0.12 sec) obclient>INSERT INTO t2 VALUES(3, 3); Query OK, 1 rows affected (0.12 sec) Q1: obclient> EXPLAIN SELECT * FROM t1 WHERE c1 = 1 FOR UPDATE\G; *************************** 1. row *************************** Query Plan: ===================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------- |0 |MATERIAL | |10 |856 | |1 | FOR UPDATE | |10 |836 | |2 | TABLE SCAN|T1 |10 |836 | ===================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2]), filter(nil) 1 - output([T1.C1], [T1.C2]), filter(nil), lock tables(T1) 2 - output([T1.C1], [T1.C2], [T1.__pk_increment]), filter([T1.C1 = 1]), access([T1.C1], [T1.C2], [T1.__pk_increment]), partitions(p0) ~~~ 上述示例中，Q1 查询的执行计划展示中的 outputs & filters 详细列出了 FOR UPDATE 算子的输出信息如下： <table data-tag="table" id="table-g7o-h9u-76d" class="table"><colgroup span="1" width="139" data-tag="col" id="col-4w9-p9t-vkd" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup span="1" width="609" data-tag="col" id="col-vh8-omi-wxs" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-zae-vy6-t5u" class="thead"><tr id="tr-0yj-x02-k0r"><th id="td-6mg-5zl-v5v"><p id="p-2qu-mg3-35k"><b>信息名称</b></p></th><th id="td-09r-loh-u2t"><p id="p-4wn-ih8-03o"><b>含义</b></p></th></tr></thead><tbody data-tag="tbody" id="tbody-1w5-el1-f7g" class="tbody"><tr data-tag="tr" id="tr-dpx-xlt-eoj" class="tr"><td data-tag="td" id="td-swh-bte-jj4" class="td"><p id="p-fr6-37h-jbi">output</p></td><td data-tag="td" id="td-gqb-cbb-9kj" class="td"><p id="p-5d9-p9j-zjd">该算子输出的表达式。</p></td></tr><tr data-tag="tr" id="tr-zg1-rxv-k71" class="tr"><td data-tag="td" id="td-lv6-uka-kle" class="td"><p id="p-80y-k32-8ng">filter</p></td><td data-tag="td" id="td-7zi-m5k-uul" class="td"><p id="p-kmz-wx0-a7t">该算子上的过滤条件。</p><p id="p-wd2-0dt-fnf">由于示例中 FOR UPDATE 算子没有设置 filter，所以为 nil。</p></td></tr><tr data-tag="tr" id="tr-4ed-y8j-qw0" class="tr"><td data-tag="td" id="td-d9m-xqt-czu" class="td"><p data-tag="p" id="p-zgh-fpd-nj1" class="p">lock tables</p></td><td data-tag="td" id="td-2wc-ggx-s52" class="td"><p data-tag="p" id="p-29h-t0u-h09" class="p">需要加锁的表。</p></td></tr></tbody></table> ## MULTI FOR UPDATE MULTI FOR UPDATE 用于对多表（或者多个分区）进行加锁操作。如下示例中，Q2 查询是对 t1 和 t2 两张表的数据进行加锁，加锁对象是满足`c1 = 1 AND c1 = d1`的行。由于需要对多个表的行进行加锁，因此 1 号算子是 MULTI FOR UPDATE。 ~~~ obclient>CREATE TABLE t1 (c1 INT, c2 INT); Query OK, 0 rows affected (0.12 sec) obclient>CREATE TABLE t2 (d1 INT, d2 INT); Query OK, 0 rows affected (0.12 sec) obclient>EXPLAIN SELECT * FROM t1, t2 WHERE c1 = 1 AND c1 = d1 FOR UPDATE\G; *************************** 1. row *************************** Query Plan: ===================================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------------------- |0 |MATERIAL | |10 |931 | |1 | MULTI FOR UPDATE | |10 |895 | |2 | NESTED-LOOP JOIN CARTESIAN| |10 |895 | |3 | TABLE GET |T2 |1 |52 | |4 | TABLE SCAN |T1 |10 |836 | ===================================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2], [T2.D1], [T2.D2]), filter(nil) 1 - output([T1.C1], [T1.C2], [T2.D1], [T2.D2]), filter(nil), lock tables(T1, T2) 2 - output([T1.C1], [T1.C2], [T2.D1], [T2.D2], [T1.__pk_increment]), filter(nil), conds(nil), nl_params_(nil) 3 - output([T2.D1], [T2.D2]), filter(nil), access([T2.D1], [T2.D2]), partitions(p0) 4 - output([T1.C1], [T1.C2], [T1.__pk_increment]), filter([T1.C1 = 1]), access([T1.C1], [T1.C2], [T1.__pk_increment]), partitions(p0) ~~~ 上述示例中，Q2 查询的执行计划展示中的 outputs & filters 详细列出了 MULTI FOR UPDATE 算子的信息如下： <div data-card-value="data:%7B%22rows%22%3A4%2C%22cols%22%3A2%2C%22html%22%3A%22%3Ctable%20class%3D%5C%22lake-table%5C%22%20style%3D%5C%22width%3A%20748px%3B%5C%22%3E%3Ccolgroup%3E%3Ccol%20span%3D%5C%221%5C%22%20width%3D%5C%22139%5C%22%20%2F%3E%3Ccol%20span%3D%5C%221%5C%22%20width%3D%5C%22609%5C%22%20%2F%3E%3C%2Fcolgroup%3E%3Ctbody%3E%3Ctr%20style%3D%5C%22height%3A%2033px%3B%5C%22%3E%3Ctd%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cp%20data-lake-id%3D%5C%22b92b778e0b6d42b6559f0dbdfd18725c%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%20style%3D%5C%22color%3A%20%23262626%3B%5C%22%3E%3Cstrong%3E%E4%BF%A1%E6%81%AF%E5%90%8D%E7%A7%B0%3C%2Fstrong%3E%3C%2Fspan%3E%3C%2Fp%3E%3C%2Ftd%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%20style%3D%5C%22color%3A%20%23262626%3B%5C%22%3E%3Cstrong%3E%3Cspan%3E%E5%90%AB%E4%B9%89%3C%2Fspan%3E%3C%2Fstrong%3E%3C%2Fspan%3E%3C%2Ftd%3E%3C%2Ftr%3E%3Ctr%20style%3D%5C%22height%3A%2033px%3B%5C%22%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3Eoutput%3C%2Fspan%3E%3C%2Ftd%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3E%E8%AF%A5%3Cspan%3E%E7%AE%97%E5%AD%90%E8%BE%93%E5%87%BA%E7%9A%84%E5%88%97%3C%2Fspan%3E%E3%80%82%3C%2Fspan%3E%3C%2Ftd%3E%3C%2Ftr%3E%3Ctr%20style%3D%5C%22height%3A%2033px%3B%5C%22%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3Efilter%3C%2Fspan%3E%3C%2Ftd%3E%3Ctd%20rowspan%3D%5C%221%5C%22%20colspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3E%3Cspan%3E%3Cspan%3E%E8%AF%A5%E7%AE%97%E5%AD%90%E4%B8%8A%E7%9A%84%E8%BF%87%E6%BB%A4%E6%9D%A1%E4%BB%B6%E3%80%82%3C%2Fspan%3E%3C%2Fspan%3E%3Cspan%3E%E7%94%B1%E4%BA%8E%C2%A0%3C%2Fspan%3E%3Cspan%3E%3Cspan%3Efor%20update%20%E7%AE%97%E5%AD%90%E6%B2%A1%E6%9C%89%20filter%3C%2Fspan%3E%3C%2Fspan%3E%3Cspan%3E%EF%BC%8C%E6%89%80%E4%BB%A5%E4%B8%BA%20nil%3C%2Fspan%3E%E3%80%82%3C%2Fspan%3E%3C%2Ftd%3E%3C%2Ftr%3E%3Ctr%20style%3D%5C%22height%3A%2033px%3B%5C%22%3E%3Ctd%20colspan%3D%5C%221%5C%22%20rowspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cp%20data-lake-id%3D%5C%2293538b528a874d0c0e5cada372cb346f%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3Elock%20tables%3C%2Fspan%3E%3C%2Fp%3E%3C%2Ftd%3E%3Ctd%20colspan%3D%5C%221%5C%22%20rowspan%3D%5C%221%5C%22%20style%3D%5C%22vertical-align%3A%20top%3B%5C%22%3E%3Cp%20data-lake-id%3D%5C%22a3f281ff31f6021020c6bc5cf7498a73%5C%22%3E%3Cspan%20class%3D%5C%22lake-fontsize-11%5C%22%3E%E9%9C%80%E8%A6%81%E5%8A%A0%E9%94%81%E7%9A%84%E8%A1%A8%E3%80%82%3C%2Fspan%3E%3C%2Fp%3E%3C%2Ftd%3E%3C%2Ftr%3E%3C%2Ftbody%3E%3C%2Ftable%3E%22%2C%22margin%22%3Afalse%2C%22hideBorder%22%3Afalse%2C%22id%22%3A%22ZczYL%22%7D" id="div-cro-exq-1eq" data-tag="div" class="div"><table data-tag="table" id="table-44k-j5i-ry1" class="table"><colgroup span="1" width="139" data-tag="col" id="col-elo-0g9-6bp" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup span="1" width="609" data-tag="col" id="col-451-fpt-392" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-wi0-8f3-99m" class="thead"><tr id="tr-ycz-alu-44b"><th id="td-r5h-k6y-5pf"><p id="p-vkt-4yc-z8d"><b>信息名称</b></p></th><th id="td-8x0-xuz-b64"><p id="p-tpq-a4v-cke"><b>含义</b></p></th></tr></thead><tbody data-tag="tbody" id="tbody-4dh-akh-5vo" class="tbody"><tr data-tag="tr" id="tr-vm3-1sc-1zl" class="tr"><td data-tag="td" id="td-hq1-1ru-p3e" class="td"><p id="p-aw4-350-emp">output</p></td><td data-tag="td" id="td-zf2-m57-6af" class="td"><p id="p-co8-udl-jot">该算子输出的列。</p></td></tr><tr data-tag="tr" id="tr-xb1-zqj-f5e" class="tr"><td data-tag="td" id="td-v8n-1j6-58e" class="td"><p id="p-ocq-8zw-jbn">filter</p></td><td data-tag="td" id="td-2gk-u76-muu" class="td"><p id="p-f7j-i9q-0re">该算子上的过滤条件。</p><p id="p-sn4-ayt-ehe">由于示例中 MULTI FOR UPDATE 算子没有设置 filter，所以为 nil。</p></td></tr><tr data-tag="tr" id="tr-yp3-q26-m1q" class="tr"><td data-tag="td" id="td-ivb-1o1-rwi" class="td"><p data-tag="p" id="p-hgt-685-kq0" class="p">lock tables</p></td><td data-tag="td" id="td-foy-2xd-lbp" class="td"><p data-tag="p" id="p-wt0-qpi-6od" class="p">需要加锁的表。</p></td></tr></tbody></table></div>