Next Sep 15 PreviousItem 547204
After starting a database transaction and executing a number of SQL statements to update a database you issue a commit. After the commit you can be sure that (check all that apply):
A: All updated data is written to the database if the commit succeeded.
B: All statements in the transaction are executed properly.
C: Your database will be in a consistent state.
D: The commit will always succeed.
Answer
Answer A is the only correct one. After a successful commit the data is guaranteed to be written to the disk and not to some temporary memory buffer. All lights can go out after a commit and you should still be able to retrieve your data.
Being able to commit will not guarantee that all your statements were executed properly. It's the other way round: you decide whether all statements did execute property and if they did then you issue a commit, if they didn't you issue a rollback. How you check the result of the individual statements is up to you. You can use return statuses, status code checks or use exceptions (PDO) but if you fail to do so and one statement in your batch fails an incomplete transaction still will be written to the disk after the commit.
After a successful commit you can be sure that your database is in a consistent state as far as internal rules for the database apply (foreign keys and other database constraints) but it might still not be consistent with business logic that cannot be (or wasn't) secured by database constraints. How much business logic can be secured by database constraints depends heavily on the database used; a good DBA can do a whole lot, if not all, with Oracle or PostgreSQL.
As already suggested above: a commit may fail.
