This Wiki will explain how the SQL Anywhere guarantees recoverability and discusses issues of transaction log management and long recovery times.\.
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.
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.
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:
- 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.
- 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.
- Any operations not committed by the time the server reaches the end of the transaction log are rolled back.
- A checkpoint is performed.
- 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:
DELETE FIRST FROM GROUPO.Customers
AND Street='114 Pioneer Avenue'
AND CompanyName='The Power Group'
DELETE FIRST FROM GROUPO.Customers
AND Street='33 Whippany Road'
AND CompanyName='AMF Corp.'
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:
DELETE FROM GROUPO.Customers
DELETE FROM GROUPO.Customers
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.
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
Insert links to any related SAP Notes/KBAs that support your topic or are related. Please hyperlink ONLY SAP Note or KBA number.
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.