Do checkpoints block events?

[Breck_Carter]

The following evidence indicates that a checkpoint in SQL Anywhere Network Server Version 11.0.1.2276 will block a scheduled event from firing... either that, or it blocks the INSERT performed as the first statement inside that event.

Here are some snippets of code...

• First, the debug_watch table used to record the event,
• Second, the CREATE EVENT and INSERT statements,
• Third, the rows in debug_watch showing the sudden five-second delay between inserts, and
• Finally, the output from sa_server_messages() showing that the delay encompassed the Starting/Finished checkpoint timestamps.

CREATE TABLE debug_watch (
   pkey                 BIGINT          NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   inserted_at          TIMESTAMP       NOT NULL DEFAULT CURRENT TIMESTAMP,
   ...

CREATE EVENT rroad_watch_server_messages
SCHEDULE START TIME '00:00' EVERY 1 SECONDS
HANDLER BEGIN
INSERT debug_watch ( pkey ) VALUES ( DEFAULT );
COMMIT;
...

-- debug_watch...
pkey,inserted_at
1556,'2011-01-23 11:12:30.020'
1557,'2011-01-23 11:12:31.004' -- one second later
1558,'2011-01-23 11:12:32.098' -- one second later
1559,'2011-01-23 11:12:37.754' -- FIVE SECONDS LATER
1560,'2011-01-23 11:12:39.051'
1561,'2011-01-23 11:12:40.035'

-- sa_server_messages()...
msg_id,msg_text,msg_time,msg_severity,msg_category,msg_database
323,'Starting checkpoint of "f" (foxhound1.db) at Sun Jan 23 2011 11:12','2011-01-23 11:12:32.707','INFO','CHKPT','f'
324,'Finished checkpoint of "f" (foxhound1.db) at Sun Jan 23 2011 11:12','2011-01-23 11:12:37.739','INFO','CHKPT','f'

[MarkCulp]

Short answer: Yes.

Here is why:

When an event (or any connection) needs to run its sequence of statements, it must first be assigned to a server worker (aka a thread in the server) and that worker needs to acquire a connection to the database. By this I mean that the worker must be granted access to the database so that it can read and update the database data.

During the critical point of a checkpoint - the time in which the server is taking a consistent snapshot of the database and writing that snapshot data to disk - all workers are forbidden access to the database so that they do not make any changes to the in-memory contents while the snapshot is being taken.

So during the time that the checkpoint snapshot is being taken on a busy database - one with lots of on-going updates - the checkpoint can take several seconds (exact amount of time will depend on the number of recent updates and the speed of your I/O system) and all workers, including events, will momentarily stop what they are doing and wait until the checkpoint snapshot is complete.

In your particular example the checkpoint appears to have taken a little over five seconds and during that time you event will not have been allowed to run.

[johnsmirnios]

There are periods during any checkpoint in which no other operations are permitted; however, the durations of those periods are not predictable. Idle checkpoints are also different from explicit checkpoints since idle checkpoints allow new modifications to the database while all of the dirty pages are written out of the cache but explicit checkpoints do not. Idle checkpoints is actually a bad name and doesn't really mean that the server is idle -- they are the checkpoints that occur due to checkpoint urgency and max recovery time settings.

There can also be a lot of delays associated with a checkpoint before the "Starting checkpoint" ever shows up. See http://sqlanywhere-forum.sap.com/questions/1293/what-are-all-the-bad-things-which-happen-when-a-chec.... However, it would seem that you are interested in delays occurring between the Starting & Finishing messages. Do you know if the long checkpoints you are having problems with are idle checkpoints or explicit ones? If they are idle, is the server unresponsive the whole time? If they are explicit, does the duration of the checkpoint approximately match the time it would take to write out the expected number of dirty pages in the cache?

-john.