|
Hi all, how can I be sure that after a checkpoint in SA12, log file contains exactly all my updated data? I verified that even using a simple CHECKPOINT statement, my log file is not updated to the last modify applied to DB. I proof even to decrease the checkpoint_time option from 60 min. to 1 min. but even in this case, it has been necessary wait several minutes to view my changes applied to my DB. I have used -a dbsrv12 option on a different DB to verify all my changes was applied. Kind Regards Vito Degirolamo |
|
Like other DBMS systems SQL Anywhere uses write-ahead logging (WAL) so that when a transaction issues a COMMIT all of the logical operations for that transaction have been written to the transaction log and have been flushed to stable storage (ie disk). Table pages modified by the transaction may still only be in the buffer pool - that condition is fine, because the existence of the log records in the transaction log ensures transaction durability in the case of failure. A CHECKPOINT operation is designed to flush all dirty (modified) pages in the buffer pool to stable storage. Once the CHECKPOINT completes, and the end-of-checkpoint record has been written to the transaction log, a subsequent recovery, if necessary, is shortened in duration because the database file already contains stable copies of the modified pages, and the application of the transaction log to re-do operations needs only to do so for those operations since the last CHECKPOINT. So I don't understand your question "how can I be sure that after a a simple CHECKPOINT statement, my log file is not updated to the last modify applied to DB". The transaction log file is ALWAYS up-to-date with committed transactions (note there is no need to write transaction log records to stable storage unless the transaction issues a COMMIT). 2
Um, two options... cooperative_commits and delayed_commits :) But assuming those options aren't set to STUPID, isn't it true that ALL changes, committed or otherwise, for ALL connections are guaranteed to be written to the physical transaction log when a commit is completed on ANY connection?
(09 Jun '12, 10:29)
Breck Carter
Replies hidden
Comment Text Removed
Great detail question! I would think this will depend on
(Yes, these are itself question, I don't have that multi-page whitepaper:) To cite from the v12.0.1 docs:
(11 Jun '12, 03:39)
Volker Barth
|
|
Point 1: I am sure you did not confuse "checkpoint log" with "transaction log"... but as a warning to other folks: Checkpoint and Commit aren't in the same ballpark, they aren't in the same league, they aren't even in the same sport. Point 2: The Help topics "Checkpoint statement" and "Commit statement" are [ahem] less than thorough. Some detailed information is located in other Help topics, but the subject deserves a 100-page white paper (that's not as bad as "How Cursors Work" which deserves 1000 pages :) Point 3: While you're waiting for the White Paper, you can search for existing documents here: http://sqlanywhere.blogspot.ca/2012/06/sql-anywhere-advanced-search.html Point 4: Checkpoint and commit operations are only loosely connected, and there's a huge and extremely confusing mess of operations going on behind the scenes. Glenn hints at the complexity :) Now, the $64,000 question... did you do a commit and wait for it to finish before looking at the transaction log? |
