Chapter 6 - Database Administration

Lock Granularity

The level of the lock is called its _____ .

decreases, increases

The speed of the DB _____ as the isolation level _____.

database lock table lock page lock row lock all of these can be shared or exclusive locks. For example you can have a shared row lock or an exclusive row lock.

What are the lock granularities?

dirty reads nonrepeatable reads phantom reads

What can happen without concurrency control?

1) Columns frequently used in an ORDER BY clause. 2) Columns used to join tables 3) Columns frequently used in a HAVING clause.

What columns are generally a good candidate to be indexed?

rollback command

What command indicates that there was an error and the database software must undo any changes in the database made by this transaction?

commit command

What command usually indicates that the transaction was successfully applied to the database?

Atomic Consistent Isolated Durable

What does ACID stand for?

It means a transaction has either ALL of its changes applied to the DB or NONE of its changes applied to the DB. No partial changes allowed.

What does Atomic mean in the term ACID?

It means that the database was taken from one stable state to another as a result of the transaction.

What does Consistent mean in the term ACID?

It means that changes, once committed, cant be undone.

What does Durable mean in the term ACID?

It means that the transaction will not overwrite changes made by other transactions nor will it "view" temporary changes made by other transactions.

What does Isolated mean in the term ACID?

It means there are multiple simultaneous database users.

What does concurrency mean?

Database Authorization.

What enables the user to access and use specific objects and/or features in the DB?

This is a smaller set of new code to fix a limited number of computer bugs.

What is a "patch" in context to Network Security?

This is a new version of the software that incorporates new features and bug fixes.

What is a "release" in context to Network Security?

It is an identifiable and individual item in the database that can be created, updated, and deleted.

What is a database object?

It is a record of all the transaction (activity) made to the database since the last backup.

What is database transaction history?

BEGIN TRANSACTION SQL statements... ... ... ... ON ERROR ROLLBACK COMMIT END TRANSACTION

What is the basic syntax for the transaction part of an SQL transaction?

When a transaction writes its updates to a copy of the data that exists in memory (RAM) and then when the transaction ends/commits it is written to the DB.

What is the deferred update method?

The smaller the lock granularity the more overhead the database incurs to track the lock.

What is the drawback of using small granular locks on a database?

To make it look as if there is only one user even if there are thousands of users.

What is the goal of concurrency control?

When a DB writes updates to the DB as they are processed.

What is the immediate update method?

isolation

What is the solution to prevent dirty reads, non-repeatable reads, and phantom reads?

this is another DB table that provides a history of all database changes. It typically includes before-image column, after-image column, time, type of operation, etc. To see a full list go to chapter 6 page21.

What is the transaction log?

Read Uncommitted

What level of isolation doesn't prevent any of the three concurrency problems?

Serializable

What level of isolation eliminates dirty reads, non-repeatable reads, and phantom reads.

Repeatable read

What level of isolation eliminates only dirty reads and non-repeatable reads.

Read committed

What level of isolation eliminates only dirty reads?

Read Uncommitted Read Committed Repeatable Read Serializable

What levels of isolation are there?

Automated Database Recovery

What method of recovery is the used my most multi-user databases?

begin transaction command

What transaction command marks the start of a database transaction?

Page Level Locking

What type of locking strategy is being used if if each database lock locks a group of rows in a table but not all the rows in a table?

This is Authentication. It is typically accomplished with a username and password.

What type of security lets you "into" the DB but doesn't let you do anything.

DELETE and UPDATE

Which clauses will cause a "before image" to be written to the transaction log?

INSERT and UPDATE

Which clauses will cause an "after image" to be written to the transaction log?

Try to use optimistic locking. It will enable faster transaction processing and more concurrent users in the DB.

Which is the best choice for a database - optimistic or pessimistic locking?

locking objects

Database isolation is made possible by what?

It is a checkpoint that is scheduled to occur every fixed number of minutes. For example every 15 minutes. This works well if the database workload is known and stable.

Describe a "processing time based" checkpoint.

UPDATE INSERT DELETE

SQL _____ statement will have before and after image in the transaction log. SQL _____ statement will only have an after image in the transaction log. SQL _____ statement will only have a before image in the transaction log.

SQL Transaction

A _____ is a single unit of work that accomplishes some task

permissions

Database authorizations are accomplished by using what?

It is a checkpoint that is scheduled to occur whenever the system believes a sufficient number of updates have occurred that would cause the recovery process to run a set amount of time.

Describe a "recovery time-based" checkpoint.

This is a guaranteed recovery point for a DB. It represents a synchronization between the DB and the transaction log.

Describe a checkpoint.

It determines the physical storage sequence. Its acts like an extra table in the DB. One column holds the DB value and the other column holds the row number. It is ordered the same order as the data in the DB.

Describe a clustered index.

It is a validated copy of the database taken at a known date and time.

Describe a database backup

This is something in the DB that can be created, updated, and deleted. The most common DB object is a table.

Describe a database object.

This is reading, updating, creating, and deleting objects.

Describe a database operation.

This is a group of users who share the same permissions.

Describe a database role.

This occurs when two transactions hold locks on resources the other transaction needs. Example: Transaction A locks ROW1 Transaction B locks ROW2 Transaction A tries to lock ROW2 to perform an update. Transaction B tries to lock ROW1 to perform an update. Now both transactions have locked a row that the other transaction needs to complete itself.

Describe a deadlock (or deadly embrace).

This occurs when a users transaction reads uncommitted row data that is in the process of being changed by some other transaction.

Describe a dirty read.

This controls network traffic (communication) coming into and leaving a computer.

Describe a firewall.

It is a restriction on a database object (or row) that identifies and optionally restricts its usage by others.

Describe a lock

Its acts like an extra table in the DB. One column holds the DB value and the other column holds the row number. The order of the data in the index is not in the same order as the data in the DB.

Describe a non-clustered index.

This occurs when a transaction rereads database data it previously read, and that data has been changed by some other users committed transaction. A phantom read finds new/missing rows. A non-repeatable read finds new/missing data.

Describe a non-repeatable read.

This is the right to perform a database operation on a database object. Think "you have permission to operate on the data"

Describe a permission.

This occurs when a transaction rereads committed data and finds one or more new rows OR finds that rows are no longer there. A phantom read finds new/missing rows. A non-repeatable read finds new/missing data.

Describe a phantom read.

It is a lock that prevents other transactions from getting an exclusive lock on the object. A shared lock allows other transactions to get a shared lock but not an exclusive lock on the resource.

Describe a shared lock.

It is a checkpoint that is scheduled to occur every fixed number of DB updates. This works well if the database workload varies a great deal where in one 15- minute periods 10,000 updates could be done and in another 15 minute period 300,000 database updates could be done.

Describe an "activity based" checkpoint.

It prevents other users from accessing the database. No other tables or pages can be accessed because the entire DB has an exclusive lock. This is the default is you use Microsoft Access.

Describe an exclusive database lock.

It is a lock on an object that prevents any other transaction from gaining access to that object. Other transactions cant get an exclusive or shared lock on the resource while its in an exclusive lock.

Describe an exclusive lock

It prevents any other transaction from getting access to a "page" in a table. NOTE: A page is part of the table, not the entire table. A table is broken down into pages for efficient disk input/output operations. Think of the table like a book and the pages of the book are part of the entire table.

Describe an exclusive page lock.

This prevents any other transactions from getting an lock on the specific row.

Describe an exclusive row lock.

It prevents other transactions from getting access to that table.

Describe an exclusive table lock.

This locking assumes there will be few cases where dirty reads, non-repeatable reads, and phantom reads will occur. When they do, the system will identify the faulty transaction, undo the transaction, and restart the transaction. The theory is that the conflict that caused the faulty read will have been resolved by the time the transaction is restarted. This locking works best in lower volume databases where there is relatively low amount of database update activity.

Describe optimistic locking.

This locking assumes there will be many cases where faulty reads can occur. This style of locking will prevent these faulty reads from happening by locking objects as soon as a transaction starts on an object, then release the lock when the transaction is complete. This means any other transactions will have to wait if they want to use the object while its locked. This works well in a high-volume database where there is frequent conflict between transactions.

Describe pessimistic locking.

1) Remove the current damaged DB 2) reload the DB using the DB backup 3) reprocess the DB activity using the transaction history.

Describe the reprocessing recovery process.

This process reverses or undoes any database transaction that is currently in process but not yet completed (not yet committed).

Describe the rollback process.

This process redoes a committed transaction ensuring these are reapplied complete to the database.

Describe the rollforward process.

Typically this is processing time based, activity based, or recovery time based.

How do you tell a DB to take a checkpoint?

The DBMS will recognize the deadlock and kill and restart one of the transactions.

How is a deadlock (deadly embrace) resolved?

as many as needed

How many columns in a single table can have a non-clustered index?

No. You should never index multiple columns. It is done sometimes, but try to avoid it.

Is it good practice to index a combination of multiple columns?

Only these: INSERT, DELETE, UPDATE, START, COMMIT.

Only SQL statements that update the database or are associated with a transaction are recorded in the transaction log. What are these statements?

False - You can have only one clustered index per table.

T/F You can have any number of clustered indexes on a table.

True - They do speed up SELECT statements. However they also slow down INSERT, UPDATE, and DELETE activity in the DB.

T/F Indexes speed up SELECT statements.

False - A shared lock doesn't allow any other transactions from obtaining an exclusive lock.

T/F A shared lock allows other transactions to obtain a exclusive lock on the object.

true - The because the database will either be consistent after the transaction or if a statement in the transaction fails then the transaction will not be applied, leaving the DB stable.

T/F For short periods of time while a transaction is processing the database can become inconsistent. That is OK. The guarantee is that it will be consistent when the transaction ends.

True - they can be read or write lock.

T/F Locks can be shared (read lock) or exclusive (write lock).

False - Permissions are assigned to roles and a role is assigned to users. Roles are usually centered around job titles or job functions.

T/F Permissions are assigned to individual users.

1) They enable recovery from failures and keep a database consistent even in cases of failure. 2) They provide a mechanism where the work of multiple users accessing a database at the same time do not interfere with each other (data concurrency).

Why are SQL transactions important?

Because you want all the SQL statements to terminate successfully. If one fails then all the SQL statements in the transaction must be undone.

Why is it important to "wrap" the SQL statements into a transaction?

Because it forces the DB to process on transaction at a time. This will slow down the database in almost every scenario.

Why is it not a good choice to use Serializable isolation level?