COMMIT
Synopsis
Use the COMMIT statement to commit the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs.
Syntax
commit ::= COMMIT [ TRANSACTION | WORK ]
commit
Semantics
commit
COMMIT [ TRANSACTION | WORK ]
WORK
Add optional keyword — has no effect.
TRANSACTION
Add optional keyword — has no effect.
Examples
Create a sample table.
CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));
Begin a transaction and insert some rows.
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
INSERT INTO sample(k1, k2, v1, v2) VALUES (1, 2.0, 3, 'a'), (1, 3.0, 4, 'b');
Start a new shell with ysqlsh and begin another transaction to insert some more rows.
yugabyte=# BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
INSERT INTO sample(k1, k2, v1, v2) VALUES (2, 2.0, 3, 'a'), (2, 3.0, 4, 'b');
In each shell, check the only the rows from the current transaction are visible.
1st shell.
yugabyte=# SELECT * FROM sample; -- run in first shell
k1 | k2 | v1 | v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows)
2nd shell
yugabyte=# SELECT * FROM sample; -- run in second shell
k1 | k2 | v1 | v2
----+----+----+----
2 | 2 | 3 | a
2 | 3 | 4 | b
(2 rows)
Commit the first transaction and abort the second one.
COMMIT TRANSACTION; -- run in first shell.
Abort the current transaction (from the first shell).
ABORT TRANSACTION; -- run second shell.
In each shell check that only the rows from the committed transaction are visible.
yugabyte=# SELECT * FROM sample; -- run in first shell.
k1 | k2 | v1 | v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows)
yugabyte=# SELECT * FROM sample; -- run in second shell.
k1 | k2 | v1 | v2
----+----+----+----
1 | 2 | 3 | a
1 | 3 | 4 | b
(2 rows)