Search code examples
javadatabaseobjectpersist

Is it necessary to begin a transaction to find an object in the data base?

I know the following is necessary to interact with objects in the database:

db.getTransaction().begin();
//Code to modify objects in the data base
db.getTransaction().commit();

But is it necessary when I use a function find(object.class, object.id)?

Solution

  • Is it necessary to begin a transaction to find an object in the data base?

    Depends on what you mean by the word 'transaction'.

    Databases do everything in transactions. Period. Therefore, trivially, 'yes', it is neccessary to begin a transaction, because you can't interact with a database without doing this.

    However, databases have a 'convenience' feature where you can work as if transactions do not exist. This is a lie; it merely means every statement you send to the DB is silently wrapped in 'START TRANSACTION;' and 'COMMIT;' calls. This is called 'auto commit' mode (and not 'transactionless mode'), because that is the proper word for it: It commits after every transaction automatically.

    Thus, in basic JDBC (the base layer upon which all SQL-based DB interaction in java is generally built, but note that you're using some sort of library built on top of it!), you can just run a select query, no 'need' to bother with transactions, by using auto-commit mode.

    But, auto commit mode is generally a bad idea.

    transactions are fundamental to database design. Even if you are only making 'read' queries, transactions are still important. They guarantee consistency and atomicity. Here is an example:

    Let's say that you want to know the bank balance of Jane, and the bank balance of Jack, for example to check if the balance is sufficient to give out a lease for a home or whatnot.

    Seems simple:

    SELECT balance FROM bankaccounts WHERE user = 'Jack';
SELECT balance FROM bankaccounts WHERE user = 'Jane';

    and add em up, right?

    Wrong.

    If you run that in auto-commit mode, then it is possible that Jack has 50k, Jane has 100k, and nevertheless, the sum appears to be 200k. This is done by jack managing to transfer his 50k to jane right in between the 2 statements, giving you a sum of 50k+150k = 200k, even though that is a lie.

    transactions therefore also apply to read-only sessions; and they can even cause retry assuming you use the correct isolation level (SERIALIZABLE is the sensible level). With transactions, you'd get the right answer, of 150k, every time, no matter when or how often jane and jack transfer funds back and forth to try to fake you out.

    JDBC itself does not have this 'getTransaction()' thing, or 'begin', and in general that is not the right way to 'do' transactions. Thus, it is not clear what third party library you're using here.

    A proper database abstraction would look something like:

    int sum = dbAccess.executeInt(db -> {
  // query or modify things here:
  return
  db.select("SELECT balance FROM bankaccount WHERE user = ?", "Jack").singleInt() +
  db.select("SELECT balance FROM bankaccount WHERE user = ?", "Jane").singleInt();
};

    It needs to be in a lambda to deal with retries, and it meshes well with the truth that all interactions with a database are transactional.

    You may want to peruse the docs some more; this strategy (of using lambdas) is may post-date the beginnings of your DB library (lambdas were added in java8; admittedly, that's a decade ago).