こんにちは、おうどんです🍜
在庫の数を UPDATE しました。でも、隣の席の先輩の画面では、前の数のままなんです
先輩のパソコン、昨日のままで止まってるのかな。時差ぼけ
止まっているのは先輩ではなくて、新人さんの COMMIT です
(会話は説明用に作ったものです。SQL の結果は今回の検証で実際に動かしたものです)
先輩のパソコンは元気です。
新人さんの変更は、まだ新人さんの手元にあるだけなんです。
データベースの変更は、COMMIT(確定)するまで「下書き」です。
うどん屋さんでいえば、注文を書いた伝票を、まだ自分のポケットに入れている状態。厨房(ほかの人)からは見えません。
たとえを外して言うと、コミットしていない変更は、変更した本人のセッション(データベースとの1本の接続)からしか見えない、ということです。
この「まとめて確定するか、まとめて取り消すかの単位」を、トランザクションといいます。
この記事では、トランザクションの意味と、COMMIT・ROLLBACK の使い方、そして「知らないうちにコミットされる場面」を、Oracle Database 19c で実際に動かして確かめます。
検証は、今回の検証環境(Oracle Database 19c 19.3、シングルインスタンス・CDB 構成、PDB の中にテスト用の表を作成。19c の Oracle Home の SQL*Plus)で行いました。
まず覚えるのは、この3つです
- 変更は COMMIT するまで、自分のセッションにしか見えない
- 間違えたら、COMMIT の前なら ROLLBACK で戻せる
- CREATE や TRUNCATE などの DDL は直前の変更を、SQL*Plus の句なしの EXIT は既定で、黙って COMMIT する
トランザクションは「全部やるか、全部やめるか」のひとまとまり
いきなり答えを言うと、トランザクションは、いくつかの変更を「1つの仕事」としてまとめる単位です。
Oracle の公式ドキュメント(Database Concepts 19c の Transactions の章)では、トランザクションが持つ性質として、次の4つ(頭文字で ACID)が挙げられています。
| 性質 | ドキュメントの説明(要約) |
|---|---|
| Atomicity(原子性) | トランザクションの作業は、全部行われるか、1つも行われないか |
| Consistency(一貫性) | データベースを、一貫した状態から別の一貫した状態へ移す |
| Isolation(独立性) | トランザクションの結果は、コミットされるまでほかのトランザクションから見えない |
| Durability(永続性) | コミットされた変更は失われない |
ACID、なんだか酸っぱそう
酢ではなく、4つの性質の頭文字です。うどんのつゆにも入れません。
たとえば、銀行の振り込みです。A さんの口座から1万円減らして、B さんの口座に1万円足す。
減らすほうだけ成功して足すほうが失敗したら、1万円が消えます。だから、2つの変更を1つのトランザクションにして、全部成功したら COMMIT、どこかで失敗したら ROLLBACK にします。
うどんでいえば、麺だけ届いて汁が来ない、を防ぐ仕組み
麺だけでも、食べられなくはないですよね
食べられるかどうかではなく、注文どおりかどうかの話です。釜揚げを頼んだ人に、乾いた麺を出してはいけません。
始まりと終わりも、同じ章に書かれています。
- 始まり: 最初に実行できる SQL 文が来たとき
- 終わり: COMMIT か ROLLBACK(SAVEPOINT 句のないもの)を出したとき、DDL(CREATE・ALTER・DROP・TRUNCATE など、表の形を決める文)を実行したとき、ツールを正常に終了したとき、クライアントが異常終了したとき
「BEGIN を書いたら始まる」ではなく、SQL を1つ実行したら、もう始まっているんです。
伝票は、注文を1品書いた時点で、もう1枚の伝票になっている。白紙の伝票を宣言してから書く、という手順はありません。
COMMITで確定、ROLLBACKで取り消し
試してみます。メニューの表(id・name・stock の3列)に、kake と zaru の2行が入っているところから始めます。
ここから載せる SQL は、接続・PDB への切り替え・表示の設定(set feedback on pagesize 50 linesize 80 tab off、column name format a10)の行を省いています。
insert into t_udon.menu values (3, 'kitsune', 4);
select id, name, stock from t_udon.menu order by id;
rollback;
select id, name, stock from t_udon.menu order by id;
1 row created.
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
3 kitsune 4
3 rows selected.
Rollback complete.
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
2 rows selected.
INSERT した直後は、自分には kitsune が見えています。
ROLLBACK すると、kitsune は消えました。
きつね、化かして帰っていった
化かしたのではなく、伝票を破っただけです。
ROLLBACK は「まだ COMMIT していない変更を、全部なかったことにする」命令です。逆に、COMMIT したあとの変更は、ROLLBACK では戻りません。
じゃあ、COMMIT しなければ、何でもやり直せるんですね
人生にも ROLLBACK がほしい
人生にあるかは知りませんが、データベースでは「コミットまでなら」です。ここは大事なので、あとの章で何度も出てきます。
コミットするまで、ほかのセッションには見えない
冒頭の新人さんの話を、2つのセッションで再現します。
セッション A は、kitsune を INSERT してから6秒待って COMMIT します(dbms_session.sleep は、指定した秒数だけ止まる手続きです)。
-- セッション A
insert into t_udon.menu values (3, 'kitsune', 4);
exec dbms_session.sleep(6)
commit;
セッション B は、A が始まって約2秒後と、A が終わったあとに、同じ SELECT を実行しました。
-- セッション B
select id, name, stock from t_udon.menu order by id;
A の待ち時間中(約2秒後)の B の結果です。
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
2 rows selected.
A の COMMIT のあとの B の結果です。
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
3 kitsune 4
3 rows selected.
A が INSERT した kitsune は、コミットされるまで、B からは見えませんでした。
Database Concepts には、アクティブなトランザクションについて、ほかのユーザーはコミットされていない変更を見ることができない、と書かれています。上の表の Isolation(独立性)のとおりです。
A さん、6秒も寝てるんですね
おうどんの昼寝より、だいぶ短い
比べる相手がおかしいです。この6秒は、順番を見せるために入れた待ち時間です。
じゃあ、先輩に見せたいときは、どうすればいいんですか
先輩の膝の上に座って、同じ画面を見る
それだと同じセッションですね。おすすめはしません。正解は COMMIT です。
伝票は、厨房に出して初めて、ほかの人にも届きます。
同じ行を書き換えようとすると、待たされる
コミットしていない変更には、もう1つ性質があります。
ほかの人は、その行を書き換えられません。
セッション A で、kake の在庫を0にして、8秒待ってから ROLLBACK します。
-- セッション A
update t_udon.menu set stock = 0 where id = 1;
exec dbms_session.sleep(8)
rollback;
A が待っている間に、セッション B で、ふつうの SELECT と、行を押さえる SELECT … FOR UPDATE(読んだ行をあとで書き換えるために、先にロックする書き方)を実行しました。WAIT 3 は「3秒まで待つ」という指定です。
-- セッション B(set timing on で、かかった時間も表示)
select id, name, stock from t_udon.menu where id = 1;
select id, name, stock from t_udon.menu where id = 1 for update wait 3;
rollback;
ID NAME STOCK
---------- ---------- ----------
1 kake 5
1 row selected.
Elapsed: 00:00:00.00
select id, name, stock from t_udon.menu where id = 1 for update wait 3
*
ERROR at line 1:
ORA-30006: resource busy; acquire with WAIT timeout expired
Elapsed: 00:00:03.01
Rollback complete.
Elapsed: 00:00:00.00
ふつうの SELECT は、待たずに、A が変える前の在庫5を返しました。
FOR UPDATE のほうは3秒待って、ORA-30006(待ち時間の上限まで待ったが、ロックが取れなかった)になりました。
読むだけならすぐ。書こうとすると行列
人気のうどん屋さんの、メニュー表と注文口みたいですね
メニュー表は誰でも眺められて、注文口は前の人が終わるまで空かない。今回は、そのたとえがわりと正確です。
Database Concepts にも、アクティブなトランザクションが変更している行は、ほかのユーザーが変更できない、と書かれています。
実務で「UPDATE が終わらない」「画面が固まった」と言われたら、まず、どこかのセッションが COMMIT を忘れていないかを疑います。犯人は、たいてい昼休みに行った人の開きっぱなしの画面です。
(おうどんは疑われる側の顔をしています。今日は違います)
🔰 ここまで読めば今日から困らない
- トランザクションは、いくつかの変更を「全部確定するか、全部やめるか」でまとめる単位
- 変更は COMMIT するまで、自分のセッションにしか見えない
- COMMIT の前なら、ROLLBACK で取り消せる。COMMIT のあとは戻らない
- コミットしていない行は、ほかの人が書き換えられない。待たされたら、COMMIT 忘れを疑う
| やりたいこと | 書くこと |
|---|---|
| 変更を確定する | COMMIT; |
| 変更を取り消す | ROLLBACK; |
| 途中まで戻す | SAVEPOINT 名前; と ROLLBACK TO SAVEPOINT 名前;(中級の章) |
COMMIT を押す前に、深呼吸することにします
深呼吸の前に、SELECT で中身を確かめましょう。呼吸は確定してくれません。
この表、伝票の裏に書いておく
伝票は厨房に出すと戻ってこないので、メモ帳に書いてください。
ここから先は中級者向けです。読み飛ばしてもOKです。
中級:エラーになった文だけが、なかったことになる
トランザクションの途中で SQL がエラーになったら、全部消えるのか。
答えは、消えるのはエラーになった文だけ、です。
wakame を1行入れたあと、2行をまとめて入れる INSERT を実行します。2行目は id=1 がすでにあるので、主キー違反になります。
insert into t_udon.menu values (6, 'wakame', 3);
insert into t_udon.menu
select 7, 'niku', 2 from dual
union all
select 1, 'kake2', 9 from dual;
select id, name, stock from t_udon.menu order by id;
rollback;
select id, name, stock from t_udon.menu order by id;
1 row created.
insert into t_udon.menu
*
ERROR at line 1:
ORA-00001: unique constraint (T_UDON.MENU_PK) violated
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
6 wakame 3
3 rows selected.
Rollback complete.
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
2 rows selected.
失敗した INSERT の niku(問題のなかった1行目)も入っていません。でも、その前の wakame は残っています。
Database Concepts では、これを文レベルの原子性(statement-level atomicity)と呼んでいます。SQL 文は1つが原子的な単位で、エラーになった文の結果は全部取り消されるけれど、同じトランザクションのそれより前の作業は失われない、という説明です。
niku、何も悪くないのに、巻き添えで帰された
同じ伝票の2行目が売り切れだったから、ですね
そうです。1つの文は、1枚の伝票の1つの注文欄。その欄に1つでもダメな品があれば、その欄ごと書き直しです。前の欄の wakame は無事。
ここで注意したいのは、エラーのあともトランザクションは続いている、ということです。wakame はまだコミットされていません。
エラーが出たら全部やり直したいなら、自分で ROLLBACK する必要があります。
エラーが出たのに、平然と次の注文を待ってる。メンタル強い
メンタルではなく仕様です。止めたいなら、止める人(自分か、あとで出てくる WHENEVER SQLERROR)が要ります。
中級:SAVEPOINTで途中まで戻す
「全部やめる」ではなく「ここまでは残して、そのあとだけやめたい」ときは、SAVEPOINT(途中に付ける目印)を使います。
insert into t_udon.menu values (8, 'tanuki', 4);
savepoint after_tanuki;
insert into t_udon.menu values (9, 'curry', 1);
select id, name, stock from t_udon.menu order by id;
rollback to savepoint after_tanuki;
select id, name, stock from t_udon.menu order by id;
commit;
1 row created.
Savepoint created.
1 row created.
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
8 tanuki 4
9 curry 1
4 rows selected.
Rollback complete.
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
8 tanuki 4
3 rows selected.
Commit complete.
curry だけが消えて、tanuki は残り、COMMIT で確定しました(別のセッションで確かめると、tanuki が入っていました)。
カレーうどん、汁が飛ぶから戻されたのかな
白いシャツの日だったんですね
おうどんは毎日同じ服なので、シャツの色は関係ありません。戻したのは SAVEPOINT のせいです。
Database Concepts によると、ROLLBACK TO SAVEPOINT のあとも、トランザクションは続いています。指定した SAVEPOINT より後のロックは外され、前に取ったロックは残ります。指定した SAVEPOINT は残りますが、それより後に作った SAVEPOINT は消えます。
つまり、ROLLBACK TO SAVEPOINT は「途中まで戻す」で、COMMIT も ROLLBACK もまだ済んでいません。最後にどちらかを書くのを忘れないようにしましょう。
ゲームのセーブポイントみたいですね
似ていますが、ゲームと違って、セーブしただけでは何も確定しません。確定するのは COMMIT だけです。
中級:DDLを実行すると、その前の変更もコミットされる
ここからが、知らないと事故になる話です。
Database Concepts には、データベースはすべての DDL の前と後に、暗黙の COMMIT を発行する、と書かれています。
試してみます。tempura を INSERT したあと、別の表を CREATE TABLE して、ROLLBACK します。後半は、すでにある表をもう一度作ろうとして、わざと失敗させます。
insert into t_udon.menu values (4, 'tempura', 2);
create table t_udon.memo (x number);
rollback;
select id, name, stock from t_udon.menu order by id;
insert into t_udon.menu values (5, 'ebi', 1);
create table t_udon.memo (x number);
rollback;
select id, name, stock from t_udon.menu order by id;
1 row created.
Table created.
Rollback complete.
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
4 tempura 2
3 rows selected.
1 row created.
create table t_udon.memo (x number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Rollback complete.
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
4 tempura 2
5 ebi 1
4 rows selected.
ROLLBACK したのに、tempura は消えませんでした。CREATE TABLE の前に、COMMIT されていたからです。
しかも今回の検証環境では、CREATE TABLE が ORA-00955(同じ名前のものがすでにある)で失敗した場合でも、その前の ebi は ROLLBACK で消えませんでした。
ROLLBACK って、言うことを聞いてくれないんですか
聞いてるよ。取り消す相手が、もういないだけ
戻す伝票は、DDL がもう厨房に出してしまっていました。
実務でよくあるのは、データを直している途中で、作業用の表を CREATE したり、TRUNCATE(表の中身を全部消す DDL)したりする場面です。そこで、それまでの変更も確定してしまいます。
TRUNCATE で全部消して、あ、間違えた、ROLLBACK
「あ」の時点で、もう戻りません。TRUNCATE も DDL なので、ROLLBACK の出番が来る前に確定しています。
DML(INSERT・UPDATE・DELETE)の作業と DDL は混ぜず、DDL の前に、今の変更を COMMIT するか ROLLBACK するかを、自分で決めておきましょう。
中級:SQL*PlusはEXITでコミットする
もう1つの「勝手にコミット」は、ツールの終わり方です。
Database Concepts には、多くの Oracle のツールでは、正常に終了すると、今のトランザクションは暗黙にコミットされる、と書かれています。SQL*Plus のマニュアルの EXIT のページにも、句を付けない EXIT はコミットして終了する、とあります。
kamatama を INSERT して、COMMIT を書かずに EXIT するスクリプトです。
insert into t_udon.menu values (10, 'kamatama', 2);
exit
終わったあと、別のセッションで表を見ました。
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
10 kamatama 2
3 rows selected.
COMMIT と書いていないのに、kamatama は確定していました。
これを決めているのが SET EXITCOMMIT です。SQL*Plus のマニュアルでは、既定は ON で、終了時に作業がコミットされる、期待していたかどうかにかかわらず、と書かれています(この一言、マニュアルにしては正直です)。
SET EXITCOMMIT OFF にして、同じことをします。
set exitcommit off
insert into t_udon.menu values (11, 'tsukimi', 2);
exit
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
2 rows selected.
tsukimi は残りませんでした。
月見が、月に帰った
かぐや姫ですね。EXITCOMMIT OFF は
いい名前ですが、正式名称ではありません。
エラーで止めるスクリプトでも、途中までがコミットされる
バッチで SQL*Plus のスクリプトを動かすときは、エラーで止めるために WHENEVER SQLERROR EXIT FAILURE をよく書きます。
これも試しました。2行目の INSERT で主キー違反にします。
whenever sqlerror exit failure
insert into t_udon.menu values (12, 'oroshi', 3);
insert into t_udon.menu values (1, 'kake2', 9);
insert into t_udon.menu values (13, 'nabeyaki', 1);
commit;
exit
1 row created.
insert into t_udon.menu values (1, 'kake2', 9)
*
ERROR at line 1:
ORA-00001: unique constraint (T_UDON.MENU_PK) violated
SQL*Plus の終了コードは 1 でした。止まったのは期待どおりです。ところが、別のセッションで見ると、
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
12 oroshi 3
3 rows selected.
エラーの前の oroshi が、コミットされて残っていました。最後の COMMIT までたどり着いていないのに、です。
エラーで止まった、という知らせと、途中まで確定した、という結果が、同時に来ています。
失敗したのに、半分だけ成功してる……
失敗の報告に、成功のおまけが付いてくる。うれしくないおまけです。
WHENEVER SQLERROR のマニュアルのページには、EXIT のあとに COMMIT か ROLLBACK を書いて、終了前の動きを選べると書かれています(そのページには、どちらが既定かは書かれていません)。ROLLBACK を書いた版です。
whenever sqlerror exit failure rollback
insert into t_udon.menu values (14, 'sansai', 3);
insert into t_udon.menu values (1, 'kake2', 9);
insert into t_udon.menu values (15, 'chikara', 1);
commit;
exit
なお、WHENEVER SQLERROR が反応するのは SQL 文や PL/SQL ブロックのエラーで、SQL*Plus のコマンドのエラーでは動かない、ともマニュアルに書かれています。
終了コードは同じく 1。別のセッションで見ると、sansai は残っていませんでした。
ID NAME STOCK
---------- ---------- ----------
1 kake 5
2 zaru 3
2 rows selected.
データを変えるスクリプトは、頭をこう書いておくと安全です(下の2行は、今回試した2つの設定を並べたものです)。
whenever sqlerror exit failure rollback
set exitcommit off
-- ここに INSERT・UPDATE・DELETE
-- 最後まで来たら、自分で確定する
commit;
exit
途中で落ちたら ROLLBACK、最後まで来たら自分で COMMIT。コミットするかどうかを、ツールの既定値ではなく、スクリプトに書いてある1行で決める形です。
ここから先は上級者向け。読み飛ばしてもOKです。
上級:強制終了したら、ロールバックされる
正常に終わればコミット。では、異常に終わったら。
Database Concepts には、クライアントのプロセスが異常終了すると、トランザクションは暗黙にロールバックされる、と書かれています(トランザクション表と UNDO セグメントの情報を使って戻す、という説明です)。
試しました。セッション A で、kake の在庫を99にして、COMMIT も EXIT もせずに入力待ちのまま置いておきます。
-- セッション A(このあと何も入力せずに待つ)
update t_udon.menu set stock = 99 where id = 1;
A が待っている間に、セッション B で、20秒まで待つ FOR UPDATE を実行します。
-- セッション B(set timing on)
select id, name, stock from t_udon.menu where id = 1 for update wait 20;
rollback;
B を起動して約3秒後に、A の sqlplus.exe のプロセスを、Python の Popen.kill() で強制終了しました。B の結果です。
ID NAME STOCK
---------- ---------- ----------
1 kake 5
1 row selected.
Elapsed: 00:00:02.90
Rollback complete.
Elapsed: 00:00:00.00
A が止まると、待っていた B は行を取れて、在庫は 5 のままでした。99 にした変更は、ロールバックされていたわけです。
店員さんが伝票を持ったまま逃げたら、注文はなかったことになる
逃げた店員さんは、どうなるんですか
それはデータベースの管轄外です。
ここで気をつけたいのは、正常終了はコミット、異常終了はロールバックで、結果が逆になることです。
同じスクリプトでも、最後まで走って EXIT したか、途中で強制終了されたかで、データが変わります。終わり方に任せず、COMMIT と ROLLBACK を自分で書く、という前の章の結論は、ここからも言えます。
ちゃんと帰ると確定、倒れると取り消し。真面目な人ほど損してない?
損はしません。真面目な人は、帰る前に自分で COMMIT か ROLLBACK を書けばいいだけです。
上級:トランザクションIDが付くのは、最初のDMLのとき
トランザクションは「最初に実行できる SQL 文」で始まる、と最初の章で書きました。
一方で Database Concepts には、トランザクション ID は UNDO セグメントとトランザクション表のスロットが割り当てられるまで付かず、それは最初の DML 文のときに起きる、とも書かれています。
自分のセッションのトランザクションが V$TRANSACTION に出ているかを数えました(V$SESSION の TADDR と V$TRANSACTION の ADDR をつないでいます)。
select count(*) as my_tx from v$transaction t join v$session s on t.addr = s.taddr
where s.sid = sys_context('userenv', 'sid');
select count(*) from t_udon.menu;
select count(*) as my_tx from v$transaction t join v$session s on t.addr = s.taddr
where s.sid = sys_context('userenv', 'sid');
insert into t_udon.menu values (16, 'shippoku', 2);
select count(*) as my_tx from v$transaction t join v$session s on t.addr = s.taddr
where s.sid = sys_context('userenv', 'sid');
commit;
select count(*) as my_tx from v$transaction t join v$session s on t.addr = s.taddr
where s.sid = sys_context('userenv', 'sid');
MY_TX
----------
0
1 row selected.
COUNT(*)
----------
2
1 row selected.
MY_TX
----------
0
1 row selected.
1 row created.
MY_TX
----------
1
1 row selected.
Commit complete.
MY_TX
----------
0
1 row selected.
SELECT のあとは 0、INSERT のあとに 1、COMMIT のあとは 0 に戻りました。今回の検証環境では、V$TRANSACTION に行が出たのは、最初の DML のあとでした。
メニューを眺めているだけのお客さんには、まだ伝票番号が付かない
注文したら、番号札が来るんですね
おうどんは、メニューを見ているだけで番号札がほしい
番号札は、注文した人にだけ配られます。
この番号札のたとえは、V$TRANSACTION の行が出たかどうか、という今回の観察の範囲で使っています。
COMMIT 忘れを探すときは、V$TRANSACTION に行が残っているセッションを見る、という使い方ができます(ほかのセッションを見るには、V$TRANSACTION と V$SESSION を見られる権限が要ります)。
上級:COMMITのとき、中では何が起きているか
COMMIT は、見た目は一瞬です。中で何をしているのかを、Database Concepts の Commits of Transactions の節から拾います。
- LGWR(ログライター)プロセスが、REDO ログバッファに残っている REDO(変更の記録)と、トランザクションの SCN(変更の順番を表す番号)をオンライン REDO ログに書く。この書き込みが、トランザクションのコミットそのものになる
- 行と表のロックを外す
- SAVEPOINT を消す
- 既定では、LGWR は REDO を同期的に書き、トランザクションは REDO がディスクに載るのを待ってから、ユーザーにコミット完了を返す
つまり、既定の動きでは、「Commit complete.」が返ってきた時点で、変更の記録(REDO)はオンライン REDO ログに書かれています。
伝票の控えを、金庫にしまってから『承りました』と言う店
慎重なお店ですね
慎重なぶん、1行ごとに COMMIT すると、1行ごとに金庫まで歩くことになります。たとえを外すと、COMMIT のたびに、REDO がディスクに載るのを待つ時間が入る、ということです(今回は速さを測っていません)。
LGWR さん、ずっと書いてる人なんですね
ずっとではなく、書くべきときに書く人です。少なくとも、おうどんよりは勤勉です。
SQL*Plus の AUTOCOMMIT と EXITCOMMIT の組み合わせ
SQL*Plus には、文を実行するたびにコミットする SET AUTOCOMMIT もあります。マニュアルの説明では、ON は INSERT・UPDATE・DELETE・PL/SQL ブロックが成功するたびにコミット、n を指定すると n 回ごとにコミットです。そして、SET AUTOCOMMIT は終了時のコミットの動きを変えない、と書かれています。
終了時の動きは、マニュアルの表(Exit Behavior: AUTOCOMMIT, EXITCOMMIT, EXIT)にまとまっています。要点を抜き出すと、こうです(この表は今回の検証では一部だけ試しました。試したのは、AUTOCOMMIT を設定していない状態での、EXITCOMMIT ON の EXIT と、EXITCOMMIT OFF の EXIT です)。
| AUTOCOMMIT | EXITCOMMIT | EXIT の書き方 | 終了時 |
|---|---|---|---|
| OFF | ON | EXIT | COMMIT |
| OFF | ON | EXIT ROLLBACK | ROLLBACK |
| OFF | OFF | EXIT | ROLLBACK |
| OFF | OFF | EXIT COMMIT | COMMIT |
| ON | ON・OFF どちらも | どれでも | COMMIT |
AUTOCOMMIT が ON だと、そもそも文ごとに確定しているので、終わり方で戻せるものが残っていない、と読めます。
迷ったら、表を覚えるより、スクリプトの最後に COMMIT か ROLLBACK を自分で書くほうが確実です。
上級:Pythonのsqlite3は逆に、閉じてもコミットしない
道具が変わると、「何もしなかったときの動き」も変わります。SQL*Plus は EXIT でコミットしました。Python の標準ライブラリの sqlite3 は、逆です。
import sqlite3
import sys
from pathlib import Path
db = Path(__file__).resolve().parent / "udon.db"
db.unlink(missing_ok=True)
print("Python", sys.version.split()[0], "/ SQLite", sqlite3.sqlite_version)
con = sqlite3.connect(db)
print("autocommit が既定の値:", con.autocommit == sqlite3.LEGACY_TRANSACTION_CONTROL)
con.execute("create table menu (id integer primary key, name text)")
con.execute("insert into menu values (1, 'kake')")
print("トランザクション中:", con.in_transaction)
con.close() # commit() を忘れた
con = sqlite3.connect(db)
print("閉じたあとの件数:", con.execute("select count(*) from menu").fetchone()[0])
with con:
con.execute("insert into menu values (2, 'zaru')")
print("with のあとの件数:", con.execute("select count(*) from menu").fetchone()[0])
con.close()
Python 3.13.17 / SQLite 3.50.4
autocommit が既定の値: True
トランザクション中: True
閉じたあとの件数: 0
with のあとの件数: 1
(python -X utf8 sqlite_tx.py で実行)
commit() を忘れて close() すると、kake は残りませんでした。with con: で囲んだ zaru は残りました。
Oracle ではずっと無事だった kake が、Python に来たとたん消えた
kake が悪いのではなく、伝票を出さずに店を閉めたからです。
Python 3.13 の sqlite3 のドキュメントによると、
- autocommit 属性は 3.12 で追加され、今の既定値は LEGACY_TRANSACTION_CONTROL(3.12 より前と同じトランザクションの扱い)。将来の版で既定値が False に変わる予定
- その既定の扱いでは、INSERT・UPDATE・DELETE・REPLACE の前に、トランザクションが暗黙に開かれる
- close() は、autocommit が LEGACY_TRANSACTION_CONTROL なら、暗黙のトランザクション制御をしない。変更を失わないよう、閉じる前に commit() するように、と書かれている
- with con: は、本体が例外なく終わればコミット、例外ならロールバックする。ただし接続は閉じない
SQL*Plus は、帰り際に勝手に伝票を出す店員さん。sqlite3 は、帰り際に伝票を捨てる店員さん
どっちの店員さんも、ひとこと言ってほしいです
本当にそうです。どちらも、ひとことも言わずにやります。
だから、どの道具でも同じ結論になります。確定したいなら COMMIT を、やめたいなら ROLLBACK を、自分で書く。
冒頭の新人さんに戻ると
冒頭の新人さんの UPDATE は、COMMIT していなかったので、先輩のセッションからは見えていませんでした。
確定したいなら COMMIT、やめたいなら ROLLBACK。どちらも、ツールの終わり方に任せずに自分で書きましょう。最初に、隣の画面がおかしいと気づけたのは、えらかったですよ
ほめられた。この気持ち、COMMIT しておこう
ほめ言葉まで、確定してから受け取る。ちょっとだけ成長しています。
COMMIT するまでは下書き、COMMIT したら確定。
そして、DDL と SQL*Plus の EXIT は、その下書きを黙って確定させることがある。この2つを覚えておくと、「見えない」「戻らない」の大半に説明がつきます。
まずは、データを変える SQL*Plus のスクリプトの頭に whenever sqlerror exit failure rollback を1行足すところから。伝票は、自分の手で厨房に出しましょう🍜
参考資料
- Database Concepts 19c: Transactions — ACID の4つの性質、トランザクションの始まりと終わり(DDL の前後の暗黙の COMMIT、正常終了でのコミット、異常終了でのロールバック)、コミットされていない変更はほかのユーザーから見えず変更もできない、文レベルの原子性、SAVEPOINT とロック、トランザクション ID は最初の DML で割り当て、COMMIT のときの LGWR と REDO
- SQL*Plus User’s Guide and Reference 19c: EXIT — 句のない EXIT はコミットして SUCCESS で終了、COMMIT と ROLLBACK の句
- SQL*Plus User’s Guide and Reference 19c: WHENEVER SQLERROR — EXIT のあとに COMMIT か ROLLBACK を選べること、SQL*Plus のコマンドのエラーでは動かないこと
- SQL*Plus User’s Guide and Reference 19c: SET System Variable Summary — SET EXITCOMMIT の既定は ON、SET AUTOCOMMIT の ON と n、AUTOCOMMIT は終了時の動きを変えないこと、AUTOCOMMIT・EXITCOMMIT・EXIT の組み合わせの表
- sqlite3 — DB-API 2.0 interface for SQLite databases(Python 3.13) — autocommit 属性と既定の LEGACY_TRANSACTION_CONTROL、暗黙のトランザクション、close() と commit()、with con: の動き
2026年11月時点の内容です。










コメント