patternsqlModerate
transaction log in RAM or physical file?
Viewed 0 times
filelogtransactionphysicalram
Problem
I'm a beginner in transaction, just a question on transaction log.
We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical files? If it is in RAM and when system failure happens, obviously the RAM will be re-erased so we lose the transaction information, so how can we recover the commit?
We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical files? If it is in RAM and when system failure happens, obviously the RAM will be re-erased so we lose the transaction information, so how can we recover the commit?
Solution
You can find a pretty comprehensive guide to this question here, but to summarise, SQL Server will not return control to the application that committed a transaction until that transaction has been hardened to disk. Specifically, once it has been hardened to the transaction log file, control can be returned.
The data, at this point, may not be hardened into the data file, it may still be in the data buffer cache, but because it has been hardened into the transaction log then database recovery, in the event of a failure, can recover this transaction and persist the changes safely.
There is a log buffer cache in memory used to reduce the performance impacts of the sequential writes to transaction logs. The buffer is flushed to disk on several conditions, but one of them is a transaction commit. Until this data has been hardened, control is not returned to the caller, so even if you have a failure during this buffer flush the transactional consistency is maintained because this transaction is not yet considered committed. You will lose the data changes in that transaction, but as it was not committed, your application would already consider those changes lost as the commit was never completed.
The data, at this point, may not be hardened into the data file, it may still be in the data buffer cache, but because it has been hardened into the transaction log then database recovery, in the event of a failure, can recover this transaction and persist the changes safely.
There is a log buffer cache in memory used to reduce the performance impacts of the sequential writes to transaction logs. The buffer is flushed to disk on several conditions, but one of them is a transaction commit. Until this data has been hardened, control is not returned to the caller, so even if you have a failure during this buffer flush the transactional consistency is maintained because this transaction is not yet considered committed. You will lose the data changes in that transaction, but as it was not committed, your application would already consider those changes lost as the commit was never completed.
Context
StackExchange Database Administrators Q#238070, answer score: 15
Revisions (0)
No revisions yet.