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

asked 09 Jun '12, 09:02

snafuz88's gravatar image

snafuz88
46559
accept rate: 0%

edited 09 Jun '12, 09:03


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).

permanent link

answered 09 Jun '12, 09:32

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

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

...isn't it true that ALL changes, ...

Great detail question! I would think this will depend on

  • whether the changes of different connections are put on the same transaction log page (as usually a page is written to permanent storage as whole) and
  • whether a COMMIT does writes all unwritten (full) log pages or just those that are related to the COMMITting connection.

(Yes, these are itself question, I don't have that multi-page whitepaper:)

To cite from the v12.0.1 docs:

The change is forced to disk when the earlier of the following operations happens:

  • The page is full.
  • A COMMIT is executed.
(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?

permanent link

answered 09 Jun '12, 10:36

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%

edited 09 Jun '12, 11:00

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17
×8

question asked: 09 Jun '12, 09:02

question was seen: 1,759 times

last updated: 11 Jun '12, 03:40