2

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?

|improve this question
  • Bad implementation? If AUTO COMMIT was disabled, then you would need a COMMIT after any DDL. – Kermit Mar 7 '16 at 19:05
  • 3
    @Kermit - I understand what auto-commit does and am not questioning its utility. I'm asking about a specific case--whether it makes sense to accept a BEGIN TRANSACTION in auto-commit mode when it is in fact being ignored, as I demonstrated. It's a matter of design, or user-friendliness, if you will. I think I was pretty clear about that. – Nick Chammas Mar 7 '16 at 19:17
  • 2
    I guess a better way to summarize the problem is this: By silently accepting the BEGIN TRANSACTION, Vertica is falsely implying to the user that they are within a transactional scope and will be able to roll back their actions. What Vertica should do instead, I believe, is fail loudly on the BEGIN TRANSACTION when the connection is in auto-commit mode to signal clearly that user-controlled transactions are not allowed. – Nick Chammas Mar 7 '16 at 19:20
  • 1
    What you're saying is in perfect accordance with Eric Raymond's "The Art of Unix Programming. If you succeed, do it quietly, if you fail, fail early and noisely. – Vérace Mar 7 '16 at 19:35
  • 1
    I get it now. I agree you shouldn't be able to create a transaction if AUTOCOMMIT is enabled. – Kermit Mar 7 '16 at 20:10
1

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:

  1. User starts vsql and connects to the database.
  2. User enters \set AUTOCOMMIT on. After that moment the client (vsql) knows to issue an implicit COMMIT statement after each successful statement.
  3. User enters BEGIN TRANSACTION. vsql does not recognize that string as one of its internal commands and sends the string to the server for processing.
  4. Server recognizes the string as a valid SQL statement, compiles it, starts a transaction, and returns a successful result code to the client. Since autocommit is a client-side setting, the server has no idea about it.
  5. Client, upon receiving the successful result code, issues COMMIT to the server.
  6. Server complies and commits the transaction it has just started.

etc.

|improve this answer
  • This is a good start at explaining things, and is along the lines of what I was thinking was happening. So what is happening when the rollback gets rejected? The client sends a rollback, it gets silently accepted as well, and Vertica rejects the auto-commit that follows? – Nick Chammas Mar 7 '16 at 20:55
  • 1
    For the record, this is not how SQL Server behaves, from what I remember. When you run an individual statement it gets auto-committed. If you start an explicit transaction, though, the server will respect it as you would expect. – Nick Chammas Mar 7 '16 at 20:57
  • 1
    In the autocommit mode each statement in effect starts a transaction, so issuing a ROLLBACK simply rolls back the transaction in which ROLLBACK is the only statement. The implicit COMMIT starts another transaction which immediately ends with that same COMMIT. – mustaccio Mar 7 '16 at 21:06
  • The error message is: [Vertica][JDBC](10040) Cannot use commit while Connection is in auto-commit mode. What is generating that message? I'm guessing it's either the server or some part of the JDBC driver, most likely the latter. Both of those pieces are written by Vertica. Since the component generating the error message is clearly aware that the connection is in auto-commit mode, shouldn't it be capable of raising a similar error when the client issues a BEGIN TRANSACTION? – Nick Chammas Mar 8 '16 at 23:08
  • I guess we're now drifting into the "opinion-based" territory. – mustaccio Mar 9 '16 at 1:21
1

Vertica support have acknowledged this issue and are tracking a fix under VER-44735. The Vertica issue tracker is not publicly visible, unfortunately.

Vertica refused to characterize this as a bug and labeled it instead as a "new feature request", but regardless this should be addressed in an upcoming release.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.