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 算子的信息如下： <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>