rookie rookie - 1 month ago 13
SQL Question

when exactly does writing in data files occur in sql?

i am currently studying transaction management in dbms .... from database systems elmasri and navathe 6th edition link: http://mathcomp.uokufa.edu.iq/staff/kbs/file/2/Fundamentals%20of%20Database%20Systems%20-%20Ramez%20Elmasri%20&%20Navathe.pdf

can someone please tell (in short) the transaction commit process i.e. without going into too much detail .... i also read some portion from oracle forum link :
https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm
what i could understand is that actual writing can take place before or after committing ... but if changes made have to be visible to all users then it must take before commit not after commit , right ?

can someone please help me clear the confusion ??

Answer

As the forum post indicates, writes to the data files are completely independent of transaction control. Changes might be written before a transaction commits, they might be written after the transaction commits.

When changes are made, those changes are made to a version of the data in memory. In order for a transaction to commit successfully (assuming default commit settings), the change must be written to the redo logs. That allows the database to re-create the change if it has not been written to data files and the database crashes. Conversely, if a change is written to a data file before the transaction is committed, information on how to reverse the change will be in the undo logs so that if the transaction will be able to be rolled back if the database fails before the transaction commits (or if the application issues a rollback).

Comments