Sybex OCP 17 Study Guide, Chapter 15 page 908 -- JDBC transaction edge cases

[Greg Charles]

Review question 21 for chapter 15 has a JDBC connection with auto-commit set to false. In one scenario, the auto-commit is set back to true, which according to the "Edge Cases" blurb on page 894, will cause an immediate commit. The answer key calls this out correctly (answer B)

In the second scenario, the change of auto-commit to true is commented out. According to the same blurb, "... if you have autocommit set to false and close your connection without rolling back or committing your changes ... the behavior is undefined. It may commit or roll back ..." The answer key is consistent with it definitely rolling back (answer D).  

It seems to me the answer should be B and (D or E). Either that or the Edge Case blurb is wrong on this point.

[Jeanne Boyarsky]

Greg,
That edge case is about uncertainty in the real world to warn anyone using JDBC and autocommit in real code. However, it doesn't conflict with question 21. Those answers say "will". And the edge case says the behavior won't be deterministic. Which wouldn't match any of the answers and therefore you can assume the straightforward case.

[Greg Charles]

Hi Jeanne,

I may be reading that wrong, but it seems like you are arguing that the answer to this question should be B only. That is, if auto-commit is off and then turned on, then any uncommitted changes are immediately committed, so the table ends up with one row (answer B), but if auto-commit is not turned back on, and no commit or rollback is performed before the connection is closed, then the database state is undefined. That would make D incorrect, since it says the table "will" remain empty after the code runs. E is also incorrect because it says the table "will" contain one row. If either of these statements said "might", then it would be right, but they both say "will". The question says to pick two answers, and the answer key says those are B and D. I can't see how D is right, but E is wrong here.

Now, I'm guessing that most drivers are going to roll-back in this case, so D will usually be correct, but according to the text, that's not always going to be the case.

[Jeanne Boyarsky]

Ok. I'll bite that the language is sloppy and add it to our list of things that could be clearer.

[Greg Charles]

I guess I'm still not following here. It doesn't seem like the wording is sloppy, but just that the answer key is wrong. You said the behavior won't be deterministic ... I'm with you on that. It doesn't match any of the answers ... yes that's right. It's non-deterministic so it will either commit the insert or roll it back, but there's no way to tell which. One scenario would make the table have one row, and the other would leave the table empty. We're still on the same page up to there. However, "assume the straightforward case," loses me. What is the straightforward case? The answer key says, "D. The table will remain empty," which as far as I can tell isn't any more straightforward than, "E. The table will have one row."

I feel like you flipped a coin and asked whether it came up heads or tails, and because it can't be determined that means the answer is heads. I'm sure I'm missing something, but I just can't figure out what.

[Jeanne Boyarsky]

I meant the language is sloppy about exactly what is deterministic.