Skip to end of metadata
Go to start of metadata

Purpose

This Wiki will explain how the SQL Anywhere guarantees recoverability and discusses issues of transaction log management and long recovery times.\.

Overview

SQL Anywhere makes use of the transaction log and the internal checkpoint and rollback logs to ensure that all transactions are either committed or fully rolled back. 

What happens during normal operation?

SQL Anywhere is exclusively a transactional database. All INSERT, UPDATE and DELETE must be committed to become permanent. If a transaction is not committed, it will be rolled back when the user disconnects, when the database shuts down, or on recovery if the server is shut down "dirty", without a chance to roll back uncommitted transactions.

Periodically, the database will perform a checkpoint. At this time, all data currently in memory, both committed and uncommitted, will be written to disk, and the checkpoint will be written to the database and transaction log file. 

The first time a database page is changed after a checkpoint, a "before" image of the page will be saved to the checkpoint log. Subsequent changes, before the next checkpoint will not cause a write to the checkpoint log.

When an operation is performed against table data, the operation is written to the transaction log, and the reverse operation is written to the internal rollback log. When multiple rows are affected by the operation, the change to each row is written to the transaction log and it's reverse to the rollback log.

When a commit is performed, all operations on the current connection is made permanent in the database and the reverse operations are cleared from rollback log.

What happens during recovery?

When a database shuts down cleanly, any uncommitted transactions are rolled back, a checkpoint is performed and offset of the last checkpoint is written to the database and transaction log file.

If the database goes down "dirty", due to a power outage, a crash or being forcibly killed by a user, the offset of the last checkpoint in the database will not match the last offset written to the transaction log, and the server will initiate recovery when it is started back up. The following steps are performed:

  1. The database is restored to the last checkpoint. Pages changed since the last checkpoint are overwritten with the page images stored in the checkpoint log.
  2. The transaction log is read to re-apply SQL operations performed since the last checkpoint. During this process, reverse operations are written to the rollback log.
  3. Any operations not committed by the time the server reaches the end of the transaction log are rolled back.
  4. A checkpoint is performed.
  5. The server begins accepting connections.

Why does recovery sometimes take a long time?

The two largest contributors to slow recovery are large, uncommitted transactions, and tables lacking primary keys.

If very large or very many transactions have taken place since the last checkpoint, these entire operations need to be re-applied from the transaction log. If the transactions were not committed, they then need to be rolled back. A single DELETE or UPDATE statement could potentially modify millions of rows.

If large transactions have run against a table with no primary key, then UPDATES and DELETES will log the complete row for every modified entry to the transaction log, and INSERTS with every value in the row to the rollback log.

For example, if the primary key is removed from the Customers table from the SQL Anywhere 17 demo database, and all rows are then deleted from the table, the transaction log will contain the following entries: 

BEGIN TRANSACTION
go
--DELETE-1015-0001170845
DELETE FIRST FROM GROUPO.Customers
WHERE ID=101
AND Surname='Devlin'
AND GivenName='Michaels'
AND Street='114 Pioneer Avenue'
AND City='Kingston'
AND State='NJ'
AND Country='USA'
AND PostalCode='07070'
AND Phone='2015558966'
AND CompanyName='The Power Group'
go
--DELETE-1015-0001170941
DELETE FIRST FROM GROUPO.Customers
WHERE ID=102
AND Surname='Reiser'
AND GivenName='Beth'
AND Street='33 Whippany Road'
AND City='Rockwood'
AND State='NY'
AND Country='USA'
AND PostalCode='10154'
AND Phone='2125558725'
AND CompanyName='AMF Corp.'
go

and so on for each of the 126 rows in the table. Each of these rows will need to be found via a table scan that checks every value.

The rollback log will contain 

INSERT INTO "GROUPO"."Customers" ("ID","Surname","GivenName","Street","City","State","Country","PostalCode","Phone","CompanyName") VALUES(101,'Devlin','Michaels','114 Pioneer Avenue','Kingston','NJ','USA','07070','2015558966','The Power Group');
INSERT INTO "GROUPO"."Customers" ("ID","Surname","GivenName","Street","City","State","Country","PostalCode","Phone","CompanyName") VALUES(102,'Reiser','Beth','33 Whippany Road','Rockwood','NY','USA','10154','2125558725','AMF Corp.');

and so on.

By contrast, with a primary key in place, the transaction log will only record:

BEGIN TRANSACTION
go
--DELETE-1011-0001174301
DELETE FROM GROUPO.Customers
WHERE ID=101
go
--DELETE-1011-0001174312
DELETE FROM GROUPO.Customers
WHERE ID=102
go

and so on, and each row will be found quickly via an index scan. The inserts in the rollback log will still contain every value, since this is what is needed in the case of a rollback.

Avoiding slow recovery

By default, a checkpoint will occur every 20 minutes on an idle database. This frequency will increase as the database becomes busier. 

Two server start switches are provided to control the maximum 

-gc <minutes> set maximum checkpoint timeout period. Default 60. Setting this value will increase or decrease the maximum time between checkpoints. Setting -gc to 0 sets the timeout to 60 minutes.

-gr <minutes> set maximum recovery time. Default 2. This setting causes the server to attempt to limit recovery times to the value specified. In some cases, such as very large operations, it is impossible to restrict the recovery time. Recovery will always run to completion regardless of whether the value specified for maximum recovery time has been exceeded.

The most important factors in avoiding long recovery times are: avoiding uncommitted transactions, and ensuring that all tables have a primary key.

Dynamic cache sizing does not occur until recovery is complete, so setting the initial cache size (-c) can speed recovery.

<Another Section Title>

Same as the first section, this is also part of the main body of the WIKI. You can add as many sections as you require to make the information you are explaining as clear and simple to follow as possible.

Related Content

Related Documents

Insert SAP Help links or other WIKI content link.
Please hyperlink the title of the related document

Example: Explanation about how to create a WIKI page

Related SAP Notes/KBAs

Insert links to any related SAP Notes/KBAs that support your topic or are related. Please hyperlink ONLY SAP Note or KBA number.
Example:

SAP Note 83020: What is consulting, what is support

SAP KBA 12345: This is an example KBA link

(Use the following Hyperlink with SAP Note/KBA number at end: https://launchpad.support.sap.com/#/notes/123456)
__________________________________________________________________________________________________________

Use this structure to help you compose your contributions for WIKI and at the same time will ensure spelling and grammar.

  • No labels