In Firebird, transactions are always started and finished by a client. Some other DBMSs can start and commit transactions from within stored procedures because two-phase locks and transaction logging are used to control transactions. Instead, Firebird provides other mechanisms that can operate on the flow of work within a transaction without breaking atomicity. Two of these mechanisms, exception handler blocks and executable strings, are restricted for use within PSQL modules. The other, not available in PSQL, is user savepoints.
User savepoint statements, also known as nested transactions, enable you to “batch” groups of operations inside a transaction and signpost them when they post successfully. Should an exception occur later in the task sequence, the transaction can be rolled back to the last savepoint. Operations that were posted between the savepoint and the exception are rolled back and the application can correct, branch, commit, perform a full rollback, resume, or whatever is required.
Setting a Savepoint
User savepoints are a client-side operation, available only as DSQL statements. The statement for setting a savepoint isSAVEPOINT <identifier>;
The identifier can be any valid Firebird SQL identifier (31 characters maximum, U.S. ASCII alphanumeric characters, and distinct within the transaction). You can reuse the same identifier in the same transaction and it will overwrite any existing savepoint associated with the name.
Rolling Back to a Savepoint
A rollback to a savepoint begins by rolling back all the work posted after the savepoint was set. The named savepoint and any that preceded it are retained. Any savepoints that were set after the named savepoint are lost.
Any locks (explicit or implicit) that were acquired since the named savepoint was set are released. However, transactions that have been locked out, waiting for access to the released rows, still do not get access to them until the transaction completes. This ensures that currently waiting contenders will not interfere with the established work-flow if it resumes. It does not block a transaction that was not waiting at the time of the nested rollback.
This is the syntax pattern for rolling back to a savepoint:ROLLBACK [WORK] TO [SAVEPOINT] <identifier>;
If the transaction is allowed to resume after rolling back to a savepoint, the work-flow can roll back to the same savepoint again, as many times as necessary. Record versions that get rolled back will not be eligible for garbage collection, because the transaction is still active.
On the server, the savepoint mechanism—a memory-based log—can have a significant impact on resources, especially if the same rows receive updates many times during the course of retracking through the task. The resources from unwanted savepoints can be released using a RELEASE SAVEPOINT statement:RELEASE SAVEPOINT <identifier> [ONLY];
Without the keyword ONLY, the named savepoint and all savepoints that were set after it will be released and lost. Use ONLY to release just the named savepoint.
The following example illustrates how savepoints work:CREATE TABLE SAVEPOINT_TEST (ID INTEGER);
When the server engine executes rollbacks, its default point of reference for backing out a transaction is to internal savepoints stored in the memory-based auto-undo log. At the end of the rollback, it commits the transaction. The purpose of this strategy is to reduce the amount of garbage generated by rollbacks.
When the volume of changes performed under a transaction-level savepoint is getting large—into the range of 10,000 to 1 million rows affected —the engine abandons the auto-undo log and takes its references directly from the global TSB. If you have a transaction that you expect to command a large number of changes, disabling auto-undo logging will forestall the wastage of resource consumption that would occur if the server decided to abandon the logging. For more information, see “The Auto-Undo Log” earlier in this chapter.
Exception Handling Extensions
The equivalent of savepoints in PSQL modules is exception handling. Each PSQL exception-handling block is also bounded by automatic system savepoints. The PSQL extensions provide language wrappers implementing the same kind of nesting that user savepoints provide in DSQL.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
Blobs And Arrays
From Drawing Board To Database
Creating And Maintaining A Database
Firebird’s Sql Language
Expressions And Predicates
Querying Multiple Tables
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Developing Psql Modules
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.