Availability is the Holy Grail of database administrators. Conventional techniques to perform a PIT recovery will remove the effects of all transactions since that specified point in time. After the write operation is complete, the transaction performs operation UNLOCK(X). In order to make this mechanism work, we need two more actions associated with each transaction: LOCK( X), and UNLOCK( X). This is done by the use of the SQL commands: for example, the command issued by the DB-owner for the ieem230 Database in your class: Will allow the user with login name ie_abc to CREATE Tables, Modify Tables, and of course, Access all information on the Tables created from the account of ie_abc. Exacerbating this trend is the drive toward e-business. Further, they must decide whether the access for each category of users is merely to read the data, or to change it. PIT recovery usually is performed to deal with an application level problem. Application failures, not hardware failures, are the predominant drivers of recovery needs. Transaction Recovery allows a user to recover a specific portion of the tablespace, based on user-defined criteria, so only a portion of the data is affected. Then the data is accessed. After the access is complete, the LOCK is reset to 0 by the UNLOCK( X) command. The transaction may impact data in multiple tablespaces, too. Any number of problems can occur at the application level. The second possibility is to deploy UNDO Transaction Recovery. During commit, all updates are stored in a file called the DB Log file. If the multi-tasking server works on the two transactions as shown above, what is the value of X_TAKEN at the end ? When undoing erroneous transactions, recovery can be done online without suffering an application or database outage. If your data is not available, your applications can not run, and therefore your company is losing business. These errors, called non-catastrophic errors, can be corrected. But, the potential for anomalies causing failures in the UNDO is certainly a consideration. Modern DBMS's may allow even further levels of security. The REDO Transaction Recovery strategy is a combination of the first two recovery techniques we have discussed but with a twist. Usually, when a transaction is aborted, the DBMS will re-write the initial values that it reads from the DB file. Another traditional type of recovery is a point-in-time (PIT) recovery. This disaster could be anything from a simple media failure to a natural disaster destroying your data center. Note that in the case of UNDO Transaction Recovery, the portion of the database that does not need to be recovered remains undisturbed. The desired result is to maintain "Good Transaction 1" and "Good Transaction 2," while removing the "Bad Transactions" from the system. Any user who wishes to use the facilities of a DBMS must apply to the DBA for an account. Point-in-time recovery is the simplest strategy. Todays DBA must understand Transaction Recovery techniques to be able to prepare an optimal approach for every recovery situation. To accomplish this transformation, a solution is required that understands the DB2 log format and can create the SQL needed to undo the data modifications. These are all detrimental to the business, so the DBA must do everything in his power to ensure that databases are kept online and operational. Make the modification of record/Insertion of record as required. Typically, each DB will also be assigned an OWNER, which is also a user with a login and password, but who controls all access rights to that DB. When DBAs hear the word "recovery," the first thing that usually comes to mind is handling some sort of disaster. Another type of error due to multi-tasking arises when one transaction requires to compute some result using many records of a table, and while it is getting the required data, another transaction changes part of its data. Before a write( X) operation, we execute a WRITE_LOCK( X). Applications are completely unavailable until the recovery is complete. Using Transaction Recovery, application problems can be addressed quicker, thereby maintaining a higher level of data availability. These mechanisms and methods are called recovery methods. This means that the computer CPU is User errors and application failures are the most common causes of problems requiring recovery, and therefore, the primary cause for system unavailability. It also is the only one supported by native DB2 utilities. Feedback? In this case, the only way to recreate the data is if we have a copy of it stored as a backup. This is shown in the example below, where one process is computing the sum of the attribute values of a table, while another transaction changes the attribute value for some record: It is therefore essential for a DBMS to have special methods to control how multiple users access the DB values. Different DBMS allow very different security mechanisms. The need arises since most practical DB's are used by more than one user, In fact, most DBMS are managed by making systematic backups periodically. A DB2 QUIESCE works fine, but if that is not available, you will have to determine a point of consistency to be used for recovery. and B execute programs on the same computer at the same time, the CPU will execute a short portion of the process for A, suspend it, process a short This statement, while simple on the surface, hides a bevy of complicated details. on the DB server) are multi-tasking. In DBMS's which use this method, changes can be made on the DB while a transaction is still being processed.

It involves generating UNDO SQL statements to reverse the effect of the transactions in error. Thus, if users A There are three types of Transaction Recovery: PIT, UNDO and REDO. The DB owner can allow/disallow any user any amount of authority on the Database. This is true even for two transaction requests sent to a DB

Then we do a standard PIT recovery, eliminating all the transactions since the recovery point. It is therefore important for DBMS to enforce several mechanisms to restore the information in the DB. Next, to complete the transaction, we must REDO the entire transaction. If so, the LOCK bit is set to 1. As we have seen before, we are mainly concerned with two types of transactions in a DBMS: step 3. E-mail us. Note that user ie_abc has full control of all tables created by him, and can further allow/disallow other users of the ieem230 database to access his Tables: For example, if ie_abc has a table called "Supplier" created from his account, he may: GRANT INSERT, DELETE ON Supplier TO ie_xyz. The item can be accessed only if the value of the lock equals 0. One of the important decisions made by the DB designer (and administrator) is: what part of the information in a DB should be accessible to which person or group of people. By doing so, they can allow/disallow access from to Zero or One write commands, or Zero to many read commands. If the LOCK was 1, then the LOCK process waits till the LOCK value becomes 0 again (HOW ?). Questions? The current number of bookings for Flight X is indicated by the value of attribute X_TAKEN. Illegal operation in transaction: some transactions may have illegal operations (such as Divide by zero, illegal operation, parameters out of valid range etc.) 2.

We shall take only a brief look at the most basic security methods which are common to many systems. Any user who wishes to use an existing DB, must also apply to the DBA.

The successful completion of the transaction is called the COMMIT POINT. Problems? If the original DB is lost, the last state which was backed up can be restored. Industry analysts at the GartnerGroup estimate that as much as 80 percect of application errors are due to software failures and human error. If the computer which Agent1 is using crashed while the transaction was being processed. You must be able to determine a common recovery point for a set of tablespaces. To generate UNDO SQL, the DB2 log is read to find the data modifications that were applied during a given timeframe and: UPDATEs are reversed to UPDATE to the old value. These methods, or mechanisms, are called concurrency control mechanisms. After the point-in-time recovery, good transactions are missing from the database. Regardless of the type of recovery to be performed, if the error that caused the recovery to be performed is caught too late, subsequent processing could have occurred using the "bad data." When any transaction requires a read( X), or a write(X) operation, it must first issue a LOCK( X) command. To do so, we need three commands: READ_LOCK( X), WRITE_LOCK( X), and UNLOCK( X). Unlike the UNDO process, which creates SQL statements that are designed to back out all of the problem transactions, the REDO process creates SQL statements designed to reapply only the valid transactions from a consistent point of recovery to the current time. We are keeping track of seats booked for Flights. When a booking of B seats is made, the value of X_TAKEN is increased by B. In this case, if the transaction fails at some intermediate point, then all updates performed BEFORE the fail point must first be rolled back: we need to UNDO those changes. There are two main types of inconsistencies/damage to a DB: Type 1: When there is a catastrophic error to the Hard Disk, for example, when the Hard Disk crashes (or is physically damaged or lost). Catastrophic failures: due to electrical outage, power surge, lightning, theft, sabotage etc. We shall look at two simple methods of establishing concurrency control: both through the concept of LOCKS. Therefore these methods are also called UNDO/REDO methods. After the read operation is completed, it must be followed by an UNLOCK( X). Of course, the changes made since the last backup till the crash time are lost. As we know, most operating systems (including the one If multiple objects must be recovered, this approach is repeated for each object impacted. same time. An Industry at Risk - Protect Your Life Sciences Company with a Digital Intelligence Strategy, eDiscovery Investigations in the Age of Remote Work, Remote Mobile Collections for Corporate Investigations, Protect, Detect, and Respond to Supply Chain Cyber Attacks (e.g. Solarwinds) Using Splunk Enterprise Managed Security Services, Prevail or Fail: Overcoming the Challenges of Protecting Your Distributed Data. Traditional recovery is at the database object level: for example, at the tablespace or index level. Any associated indexes are automatically recovered as the transaction is recovered. With PIT recovery, you remove all transactions since a given point in time and then manually reenter the valid work. Finally, we reapply the good transactions captured in the first step. When performing a traditional recovery, a specific database object is chosen, a backup copy of that object is applied, and then log entries are applied for changes that occurred after the image copy was taken. This is the simplest type of SQL-based Transaction Recovery. Agent1: cancels bookings for 5 seats on Flight X, and books them for Flight Y. Why do we need to pay attention to transaction processing ? However, this is simply not the case anymore. Some may allow GRANT and REVOKE to be applied to some selected records. Transaction Recovery is a third type of recovery that addresses the shortcomings of the traditional types of recovery: downtime and loss of good data. We have to be careful how to implement the read-lock: While the read-lock is ON, the item can be used for reading by another transaction; however, it must not be accessible for a WRITE operation ! This concludes our discussion of Relational Databases. Historically, recovery was performed primarily to overcome disasters and hardware failures. After COMMITting, the contents of the log are copied to the DB. Shared locks track whether the data is being accessed for reading of writing.

The database does not always need to be taken offline while Transaction Recovery occurs (it depends on the type of Transaction Recovery being performed). This approach is used to recover the database object to a specific, desired point in time. Transaction Recovery is an application recovery whereby the effects of specific transactions during a specified timeframe are removed from the database. normally working on many processes in an interleaved way.