Create and modify tables with transaction support
You can create new tables with transaction support enabled or alter an existing table to enable transaction support on that table.
To enable transaction support, you must use the
TRANSACTIONAL=true parameter when creating a table, followed by
COMMIT command at the end of your transaction.
Create a table with transaction support enabled.
CREATE TABLE NEW_TABLE (ID INT) TRANSACTIONAL=true; TRANSACTION_PROVIDER='OMID';
Set autocommit to
offbefore you start a transaction.The following example shows you how to turn off autocommit and then upsert a value into
jdbc:phoenix:> !autocommit off Autocommit status: false jdbc:phoenix:> upsert into NEW_TABLE values(1,'ph'); 1 row affected (0.015 seconds) jdbc:phoenix:> select * from NEW_TABLE; +---+----+ | A | B | +---+----+ | 1 | ph | +---+----+ 1 row selected (0.144 seconds) jdbc:phoenix:> upsert into NEW_TABLE values(2,'ph'); 1 row affected (0.001 seconds) jdbc:phoenix:> !commit
Modify a table to enable transactions on the table.
ALTER TABLE EXISTING_TABLE SET TRANSACTIONAL=true;
!autocommit off -- Turn off autocommit SELECT * FROM example_table; -- Transaction starts here. Other transactions cannot see your updates yet UPSERT INTO example_table VALUES (1,'A'); SELECT count(*) FROM example_table WHERE e=1; -- You can see an uncommited row DELETE FROM my_other_table WHERE e=2; !commit -- Other transactions can now see the updates
You will see an exception if you try to commit a row that is conflicting with another transaction. In the following example, you will see an exception when you try and commit the first transaction after committing the second transaction because the second transaction updates the same row.
Now, when you do a commit on the same row. Row 1 in the following example, you will see an error.
UPSERT INTO NEW_TABLE VALUES (1,'a');
You see an exception similar to the following:
UPSERT INTO NEW_TABLE VALUES (1,'b'); !commit
java.sql.SQLException: ERROR 523 (42900): Transaction aborted due to conflict with other mutations. Conflict detected for transaction 1556122566800000000.