Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Difference between "Transaction Roll back" and "Statement Roll back" ?

 
Kaxhif Khan
Ranch Hand
Posts: 50
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

i am preparing for JPA exams where i have some minor confusions.

1) PerssimisticLockException marks transaction mark transaction for rollback only if lock failure cause transaction level roll back, from this what i understand is that all operations within a transaction will roll back.

2) LockTimeoutException is thrown and only cause statement level roll back, what does this "statement level rollback" means exactly ?
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A transaction can consist of many statements.

For example
is a statement.
is another statement.

For a transaction-level rollback, all the statements in the transaction are rolled back.
For a statement-level rollback, only the statement that has problems is rolled back. So if you had inserted into department, and then had a problem when inserting into employees, the first insert into department will not be rolled back.
 
Kaxhif Khan
Ranch Hand
Posts: 50
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, i do understand this but i have confusion regarding this in the context of pessimistic locks.

Let say we try to obtain pessimistic lock on "Department" object (from your example) i.e.



before we changed anything in it and calling lock method (as shown above) throws "LockTimeoutException" then what is the statement level rollback in this case ?
 
Tim Holloway
Saloon Keeper
Posts: 18363
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In DBMS's, you often have the option to set your session to either auto-commit mode, where each statement is committed at the time it is executed, or explicit commit mode, where nothing is committed until an explicit COMMIT command is issued.

Having said that, I'm really not sure about "statement roll back" and would be interested in seeing a reference to it. Rollback is not an "undo". Databases don't (usually) support an "undo" function. Rollback cancels the pending transaction and makes it as though they had never been submitted. Once you commit a transaction, you can no longer roll it back.

You can have simple transactions, where the statements are all against a single target or extended transactions where multiple targets are affected (this is a lot harder to do), but I've never heard of "statement roll back" except when referring to a transaction that only had a single statement with in it, because I've always considered rollback to be something done to the transaction, not to the statement.
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Holloway wrote:

Having said that, I'm really not sure about "statement roll back" and would be interested in seeing a reference to it. Rollback is not an "undo". Databases don't (usually) support an "undo" function. Rollback cancels the pending transaction and makes it as though they had never been submitted. Once you commit a transaction, you can no longer roll it back.

You can have simple transactions, where the statements are all against a single target or extended transactions where multiple targets are affected (this is a lot harder to do), but I've never heard of "statement roll back" except when referring to a transaction that only had a single statement with in it, because I've always considered rollback to be something done to the transaction, not to the statement.


If Oracle detects a deadlock, then Oracle will terminate one of the statements involved in the deadlock. Anything that this statement did will have to be "undone".

Oracle maintains an undo segment which contains the old data that a transaction/statement is modifying. So say I change employee.address for employee.id=100 and employee.id=101 in one statement, and imagine they are both stored on disk in different data blocks, what will happen is the data blocks will be read into memory (database buffer cache), modified, and the undo segment will be updated with the old data before any changes happen in memory. Because Oracle doesn't support READ UNCOMMITTED, any other transaction that isn't READ COMMITTED i.e. SERIALIZABLE or READ-ONLY, will need to go to the undo segment to get the old addresses (and any transaction that is READ COMMITTED will have to wait). Now say there are a lot of other transactions happening, and a lot of data blocks are being read into memory - as memory is finite, Oracle will start writing some of these dirty buffers onto disk to free up space.
Now imagine that Oracle wrote the dirty buffer where we changed the address of employee 100 to disk, but before we could change the address of employee 101, a deadlock occurred, and Oracle decided to roll back our statement. To change the address on disk to what it was before, Oracle will go to the undo segment, read the old employee address, and then change the new employee address for employee 100 (which we have written to disk) back to the old address.
 
Ahmed Bin S
Ranch Hand
Posts: 378
8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kaxhif Khan wrote:
before we changed anything in it and calling lock method (as shown above) throws "LockTimeoutException" then what is the statement level rollback in this case ?


Nothing. You can't roll back something you haven't done.

Read my post above for an example of statement rollback.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic