Transactional theory

Question

(I have a simple CRUD API in a DAO pattern implementation.) All operations (save, load, update, delete) have a transaction id that must be supplied. So eg. its possible to do:

...

id = begintransaction();
dao.save(o, id);
sao.update(o2, id);
rollback(id);

All examples excluding load invocations seems intuitive. But as soon as you start to load objects from the database, things "feel" a little bit different. Are load-operations, per definition, tied to a transaction? Or should my load operations be counted as a single amount of work?

Answer 1

Depends on the transaction isolation level (http://en.wikipedia.org/wiki/Isolation_(database_systems)) you're using, but in general they should be part of the transaction. What if somebody else is just in the middle of updating the data you're trying to read? If the read operation is not transactional, you would get old data, and maybe you're interested in the latest data.

Answer 2

If the database is set to decent isolation level, uncommited writes can only be read from the transaction that created them. For example, in Oracle, if a procedure inserts or updates a row and then (without commiting) calls another procedure, which uses "pragma autonomous_transaction" to run in a seperate transaction, that other procedure does not see the new row. (An excellent way to shoot yourself in the foot, btw).

For that reason, you should always consider your load operations as tied to the transaction.