5. Let's Operate a Database!

Página 1

5. Let's Operate a Database!

Properties of transactions

A set of succesful operations performed by single users is called a transaction.

Properties required for a transaction (ACID)

Atomicity: a transaction must either end with a commit or rollback operation. All actions of a transaction are completed or all actions are canceled. Commit finalizes the operation. Rollback cancels the operation.

Consistency: processing a transaction never results in loss of cosistency of the database. A transaction must not create errors.

Isolation: even when transactions are processed concurrently, the results must be the same as for sequential processing. requires the schedule to be serializable and protects against errors. Yo need to have control over transactions that are attempted at the save time. The most commonly used method is the lock-based control. * A shared-lock is used when readingdata, while an exclusive lock is used when writing data.

Two-phase locking: should be used one for setting locks and the other for releasing them.

Locking granularity: there are a number of resources that can be locked. Coarse granularity occurs when many resources are locked at once and fine granularity occurs when few resources are locked.

Other concurency controls:

Timestamp control: a label containing the time of acces, referred to as timestamp is assigned to data accessed during a transaction.

Optimistic control: allows a read operation. when uite operation is attempted.

Página 2

Durability: the contents of a completed transaction should not be affected by failure. In a database you can set permissions for who can access the database or tables in it. The grant statement is used to grant read and write permissions to users.

Database privileges

Select : allows user to search for rows in a table.

Insert: allows user to insert rows in a table.

Update: allows user to update rows in a table.

Deleste: allows user to delete rows in a table.

All: gives user all privileges.

When disaster strikes

Types of failures:

Transaction failures: transaction cannot be completed due to an error in the transaction itself. The transaction is rolled back.

System failure: the system goes down due to a power failure or other such disruption. Disaster recovery takes place afte you reboot the system.

Madia failure occurs when the hard disk that contains the database is damaged.Disaster recivery is carried out using backup files.

Checkpoints: a buffer (serment of memory used to temporarily hold data) is often used to write data in the short term. Contents of the buffer and the database are synchronized and then a checkpoint is written.

Indexes

Is a tool that allows you to speedily access the location of the target data. The hash index method finds the location of target data by applying a hash function to the key value of the data. The hash acts as a unique figerprint for a value.

Optimizing a query

When you query a database the database analyzes the SQL query and cosiders whether to use an index so it can process the query more quickly. Rules o find the best querying order:

Execute selection first to reduce the number of rows.

Execute projection first to reduce the number of columns irrelevant to the result.

Execute join later.

Methods for join:

Nested loop: compares oner row in a table to several rows in another table.

Sort merge: sorts and then merges rows in multiple tables.

Hash: divides one of the tables using a hash function and then merges it with a row in another table that has the same hash value.

Optimazer: function incharge of optimization of querires.Types:

Rule-based processing: certain rules are established before any operations are performed. The optimizer tries to find the most efficient way to process the query that gives the same results.

Cost-based processing: tries to estimate the cost of processing the query based on satatistics that the database maintains.