SQL: Sybase transaction log

Each user's connection, has 2KB of memory assigned, that should be used as a buffer. It is called User Log Cache (ULC). It can be said, that transaction is played in the ULC first. If transaction is short, than it fits fully into the ULC, but if it is too long, than the ULC would fill up...

So whenever COMMIT is issued by the transaction or if the ULC becomes full, Sybase sends the modified pages into the database's transaction log.

At that time, we can say, that transaction log is more important as the database itself. Every committed transaction is saved in the log first. The data file can be not modified at all! Let's analyze, what happens when user modifies some data:

• data is read form the disk into the buffer
• user transaction begins
• transaction data modifications are done and stored in ULC
• finally, the transaction issues the COMMIT
• data from ULC is sent to transaction log
• when the 'save on disk' operation finishes the user application can continue, as at that moment we are sure, that the transaction is fully stored on the disk, data modification is also done in memory buffer, where the "up to date" pages are stored
• ...
• at some moment a CHECKPOINT is issued
• CHECKPOINT synchronizes the data from buffer cache to disk

Why it was decided to update the log file first and not the data? One of the reasons is that the save operation done on transaction log has much better performance than save into database, as write to log is a sequential write operation. Much more important is, that we want to have the data on disk to be consistent. When saving pages to disk, if some unplanned system crash would happen, we would have some pages already updated and some of them still in the state before modification. Having the correct data in the log allows us to update the database to correct status looking at all committed in transaction log transactions. An operation of synchronizing the log with the database file is called a RECOVERY process. It happens every time, when the database is made online, so for example after a server start.

Database could function generally without checkpoints, but think about all the activities that would need to be executed by the server when it would be finally restarted, During the startup procedure, all the transactions residing in the transaction log would need to be saved in the database, what will take surely a lot of time.

BTW, all pages in buffer that differ from the version on disk (have been modified and still not synchronized with the disk) are called DIRTY pages. When synchronization finishes, the cache version and disk version are equal, and such pages are called CLEAN pages, It is easy to note, that CHECKPOINT makes all the pages CLEAN again, why the BEGIN TRANSACTION statement begins modifying the cached pages and makes them DIRTY again. Also server start begins with RECOVERY, so synchronizes pages in transaction log with the disk.