I run the following statements against a Vertica database, one at a time:
BEGIN TRANSACTION;
UPDATE table
SET col1 = 'something'
WHERE col2 = 'something else';
SELECT COUNT(*)
FROM table
WHERE col1 = 'something';
ROLLBACK TRANSACTION;
I run the first line fine… OK, now I'm in a transaction.
I run my update… OK, that worked.
I run my SELECT
test to make sure things worked as expected… Oh wait, it looks like I missed a condition in the WHERE
clause of my UPDATE
statement.
No worries! That's why I did this in a transaction.
Let's rollback:
=> ROLLBACK TRANSACTION;
[Vertica][JDBC](10040) Cannot use commit while Connection is in auto-commit mode.
Jones, hand over that roll of toilet paper you have on your desk.
So Vertica happily accepted my BEGIN TRANSACTION
, knowing full well that very soon after that I would try to run either a ROLLBACK
or COMMIT
.
Yet, I can't! My connection is in auto-commit mode, so ROLLBACK
and COMMIT
mean nothing. My UPDATE
was committed the moment it completed.
Did I miss something, or am I right in thinking this is just a very bad implementation on Vertica's part?
Why would Vertica accept a BEGIN TRANSACTION
on a connection in auto-commit mode if the logical consequences (ROLLBACK
or COMMIT
) are illegal?
Best Answer
Vertica (actually, I think most if not all databases) behaves in this manner because the part that processes the
BEGIN TRANSACTION
SQL statement, namely the query parsing and execution engine, is not aware of the client AUTOCOMMIT setting. On the other hand, the client is not aware of the meaning of the string 'BEGIN TRANSACTION'.The flow of control looks something like this, assuming the command line client
vsql
is used to run the example:vsql
and connects to the database.\set AUTOCOMMIT on
. After that moment the client (vsql
) knows to issue an implicitCOMMIT
statement after each successful statement.BEGIN TRANSACTION
.vsql
does not recognize that string as one of its internal commands and sends the string to the server for processing.COMMIT
to the server.etc.