Common types of questions on database principles and application basics

1.B 2.C 3.B 4.C 5.D 6.C 7.C 8.D 9.C 10.A

11.A 12.A 13.A - Don't know14. Bevogue2006 - 10 - 24 11:47:01 15?16.A 17.B 18.A 19.D 20.C

Describe the concept of transactions and the four characteristics of transactions.

A:

A transaction is a user-defined sequence of database operations. These operations are either all done or not done as a whole, and are an indivisible unit of work.

Transactions have four characteristics: atomicity (atomicity), consistency (consistency), isolation (isolation) and durability (Durability). These four characteristics are also called ACID properties.

Atomicity: The logical unit of work of a transaction database. The transaction includes all operations, whether done or not.

Consistency: The result of transaction execution must change the database from one consistent state to another consistent state.

Isolation: The execution of a transaction cannot be interfered with by other transactions. Operations within a transaction are separated from data using other concurrent transactions, and individual transactions executed concurrently cannot interfere with each other.

Persistence: Persistence, also known as permanent (durability), refers to the fact that changes to the data stored in the database should be permanent until the transaction commits it. Subsequent operations or failures should not have any impact on the results of its execution.

2. Here is an example of why the abnormal passage of transaction time will affect the correctness of the data in the database.

A:

The execution result of the transaction must change the database from one consistent state to another consistent state. If the operation of the database system fails and some unfinished transactions are forced to be interrupted, part of these unfinished transactions have been written to the physical database. Changes made to the database are then in an incorrect state in the database, or are Inconsistent status.

Such as a factory inventory management system, it is necessary to store certain parts of quantity Q from warehouse 1 to warehouse 2.

You can define a transaction T, T includes two operations; Q1 = Q1-Q, Q2 = Q2 + Q. T changes are terminated only when the first operation, the database is inconsistent with Q There is no reason to stock.

3. Why is there a recovery subsystem in the database? What is its function?

A:

It is unavoidable because these failures caused by computer system hardware failures, software errors, operational errors and malicious damage occur abnormally from running transactions. Interruption affects the correctness of data in the database and destroys the database. Therefore, all or part of the data in the database is lost, so there must be a recovery subsystem. Features

Recovery subsystem: The database recovers from an error state to a known good state (also called a consistent state or a complete state).

4. What types of failures can occur in the database? What glitch affects normal execution of transactions? What failure corrupts the data in the database?

A: Various possible failures of the database system can be roughly divided into the following categories:

(1) Internal transaction failure;

(2) System failure; />(3) Media failure;

(4) Computer virus.

Transaction failure, system failure and media failure affect the normal execution of transactions; media failure and computer viruses damage the data library.

5. Based on recycling technology?

A:

Data dump and login log files are the basic techniques for database recovery.

When a failure occurs during system operation, by dumping the database's log files, you can restore the database to a consistent state from the backup copy before the failure occurred.

6. What is the significance of database dump? Comparison of various data dump methods.

Answer:

Data dumping is the basic technique in recovering a database.

DBA periodically copies the so-called dump database to tape or other disk for saving. A state dump can be reloaded when a backup copy of a corrupted database is restored.

Static dump: Dumps transactions running by system operations. Static dump, but must wait for user transaction to end before running. Likewise, new transactions must wait for the execution dump to complete. Obviously, this will reduce database availability.

Dynamic dump: Allow database access during dump or. Dynamic dump can overcome the shortcomings of static dump. It does not need to wait for the end of the running user transaction, nor will it affect the operation of new transactions. However, the backup copy of the data is dumped at the end and cannot be guaranteed to be correct and valid. Because the dump run may remove some data during the transaction, the backup data copy is not consistent with the version of the database.

To do this, we must register the database using the dump transaction during the event to create a log file (log file). In such a backup copy of the log files you can get the correct database state at the moment.

Dump Mass dump and incremental dump can be divided into two methods.

Mass dump every dump of all databases. Incremental dump only dumps the last dump data for each update. From a recovery perspective, large backup copies of dumps are generally easier to restore. If the database transactions are very frequent, the incremental dump method is more practical and effective.

7. Log files? Why create a log file?

Answer:

(1) The log file is used to record the update operations of the transaction file on the database.

(2) The purpose of creating log files: transaction failure recovery, system failure recovery; backup copy to assist media recovery.

Why should the registration log file be written to the database after being written to the log file?

A:

The data is written to the database, two different operations, and the subsequent log records are written to the log file. A failure may have occurred between the two operations, i.e. the two write operations only completed.

Write a database first, instead of changing the running record, this one cannot be restored after the decimal point. If you write a log but do not have a database, the UNDO operation is performed without affecting the correctness of the database. So make sure you write to the log file, write the log records to the log file, and then write the changes to the database.

9. Testing is for different failure recovery strategies and methods. (In other words, how to perform transaction system failure recovery and failure recovery media recovery?)

A:

Transaction failure recovery:

Transaction failure recovery It is done automatically by the DBMS and is transparent to the user.

DBMS performs recovery steps:

(1) Scan the file log in reverse (that is, scan the log file from the last time), then the transaction updates the operation.

(2) The update operation of the transaction performs the reverse operation. About logging before update value? Write to the database.

(3) Reverse scan the log file and do the same.

(4)? Continue until you read the start mark of this transaction, and restore the complete transaction if it fails.

A:

System failure recovery:

System failure may cause the database to be in an inconsistent state:

First, it is not completed Updates to the transaction database may have been written to the database;

Updates to the transaction database that have been committed may still remain in the buffer and have not been written to the database.

The unfinished business of the recovery operation (UNDO) fails and the completed transaction of the recovery operation (REDO) is redone.

Recovery steps:

(1) Forward scan the log file to determine that the transaction has been submitted in the queue before the failure (REDO queue) and the unfinished transaction queue ( UNDO queue).

(2) UNDO processes individual transactions in the queue.

The UNDO processing method is to scan the log file in reverse, and the update operation performs the opposite operation. The imminent "value" (pre-image) record of each UNDO transaction is written to the database and then updated.

(3) Treat redo redo queue transactions.

REDO processing method: forward scan the log file, and re-execute the log file registration for each REDO transaction. Upcoming log records are written to the database "updated values" (after image).

Resolution:

Step (1) How to determine the REDO queue and UNDO queue, please Think about it. The algorithm of BR /> is as follows:

1) Create two transaction queues:

·UNDO-LIST: The set of transactions that need to perform undo operations;

·REDO-LIST: Transaction set that needs to perform redo operations;

The transaction queue is initially empty

) From the log file header, scan the log file forward

If there is a new start (encountered BEGIN TRANSACTION) transaction titanium, titanium is temporarily put into the UNDO-LIST queue;

·If the committed transaction (encountered end transaction) TJ TJ from the queue REDO-LIST queue in undo-list;

Until the last log file A:

Media failure recovery:

Media failure is the most serious failure.

The recovery method is to reinstall the database and then redo the completed transactions. The process is:

(1) DBA loads the latest database backup (from the most recent dump copy at the time of failure) ), restore the database to a consistent state dump

(2) DBA's copy of the log file loads the dump end time

(3) DBA starts the system recovery command to complete. Restore DBMS function to redo completed transactions

Analysis

1) We assume that the static dump step (1) installs a backup copy of the database.

2) If you are using static and dynamic dumps step (1) taking a backup copy of the database is not enough, you need to load the copy of the log file at the same time after the dump starts treatment to get the correct database Backup copy.

3) (2) step algorithm to redo completed transactions:

Forward scan the log file to identify transactions submitted before the failure occurred , counted in the weight queue

B. Scan the log file forward again, redoing all transactions in the redo queue and writing the updated values ??to the database.

>

10. What are the advantages of checkpoint recovery technology?

A:

Logging technology must search the recovery subsystem Logs to determine which transactions need to be redone. Generally speaking, you need to check all records. There are two problems with doing this:

First, searching the entire log will take a lot of time.

The transactions processed by REDO actually write the update operations and the database recovery subsystem then performs these operations, which wastes a lot of time.

Checkpoint technology is used to solve these problems.

11. Restoration steps for Uncle Shi’s checkpoint.

① Find the address of the last checkpoint recorded in the log file from the address recorded in the log file of the last checkpoint of the startup file.

②All running transactions ACTIVE-LIST in the creation time list of the checkpoint record of the checkpoint.

Create two transaction queues:

·UNDO-LIST: The set of transactions that need to perform undo operations;

·REDO-LIST: The set of transactions that need to be restored Operation;

ACTIVE-LIST temporarily goes to the UNDO-LIST queue, and the REDO queue is temporarily empty.

③ Scan the log file forward starting from the checkpoint

Any new starting point transaction Ti in the undo-list is temporarily placed in the queue;

· If transaction TJ is committed, TJ moves from the UNDO-LIST queue to the REDO-LIST queue until the last log file;

>④UNDO operation is executed for each transaction in the undo-list and for each transaction in the REDO-LIST A transaction performs a REDO operation.

12. Database mirroring? use?

Answer:

Database mirroring is the automatic copying of critical data in the entire database or parts of it to another disk based on the requirements of the DBA. Whenever the master database is updated, the DBMS automatically copies the updated data. In the past, the DBMS automatically ensured the consistency of the mirror

Data and master data using database mirroring. :

One for database recovery. When media fails, the mirror disk continues to provide the database with automatic mirror disk data recovery using the database management system, and there is no need to shut down the system and reinstall a copy of the database.

The second is to improve the availability of the database without failure. When the user's data is locked exclusively, other users can read the data. Mirror the database without waiting for the user to release the lock.