Chapter 14 Transactions and Locking SQL

The failure of an INSERT, UPDATE, or DELETE statement would ______ _______ _______.

Violate data integrity

At the more restrictive isolation levels, concurrency problems are __________ or __________. However, at the least restrictive levels, __________ __________ __________

reduced or eliminated; performance is enhanced

Oracle automatically executes a ________ statement after a DDL statement such as CREATE TABLE statement or if the application exits successfully.

commit

By default, Oracle doesn't __________ the changes made by your _______, _________, and __________ statements until you explicitly commit them. To do that, you code a __________ statement after one or more SQL statements.

commit; INSERT, UPDATE, DELETE; commit

Concurrency is a problem only when the ________ ________ ________ ________.

data is being modified

A __________ occurs when neither of two transactions can be committed because each transaction has a lock on a resource needed by another transaction.

deadlock

If concurrency problems could affect the data integrity of your system, you can change the __________ __________ __________ by setting the transaction isolation level.

default locking behavior

Save points are useful when a single transaction contains so many SQL statements that rolling back the entire transaction would be ___________.

inefficient

A _______ stops the execution of another transaction if it conflicts with a transaction that is already running.

lock

Oracle can automatically prevent some concurrency problems by using ___________

locks

How to prevent deadlocks: -(1)Don't allow transactions to __________ __________ for very long. -(2)Use the lowest possible __________ __________ __________. (3)Make large changes when you can be assured of nearly __________ __________. -Consider locking when coding your transactions: If you need to code two or more transactions that update the same resources, code the updates in the same order in each transaction.

(1) remain open; (2) transaction isolation level; (3)exclusive access (1): Keep transactions short; Keep SELECT statements outside of the transaction except when absolutely necessary; NEVER code requests for user input during a transaction. (2): The READ COMMITTED level, which is the default, is usually sufficient; Reserve the use of the SERIALIZED level for short transactions that make changes to data where integrity is vital. (3):If you need to change millions of rows in an active table, don't do so during hours of peak usage; If possible give yourself exclusive access to the database before making large changes.

In a large system with many users, you should expect __________ problems to occur. In general, you don't need to take any action except to anticipate the problem. In many cases, if the SQL statement is resubmitted, the problem goes away.

Concurrency

___________ is the ability of a system to support two or more transactions working with the same data at the same time.

Concurrency

________ ________ Occur when a transaction selects data that hasn't been committed by another transaction. For example, transaction A changes a row. Transaction B then selects the changed row before transaction A commits the change. If transaction A then rolls back the change, transaction B has selected a row that doesn't exist in the database.

Dirty reads

(TRUE or FALSE) On some systems, if two transactions overwrite each other, the validity of the database is compromised and resubmitting one of the transactions will eliminate the problem.

FALSE; On some systems, if two transactions overwrite each other, the validity of the database is compromised and resubmitting one of the transactions won't eliminate the problem. If you're working on such a system, you must anticipate these concurrency problems and account for them in your code.

________ _________ Occur when two transactions select the same row and then update the row based on the values originally selected. Since each transaction is unaware of the other, the later update overwrites the earlier update.

Lost updates

What are the four types of concurrency problems?

Lost updates, Dirty reads, Non-repeatable reads, Phantom reads.

________ ________ Occur when two SELECT statements that try to get the same data get different values because another transaction has updated the data in the time between the two statements. For example, transaction A selects a row. Transaction B then updates the row. When transaction A selects the same row again, the data is different.

Non-repeatable reads

________ ________ Occur when you perform an update or delete on a set of rows at the same time that another transaction is performing an insert or delete statement that affects one or more rows in that same set of rows. For example, transaction A updates the payment total for each invoice that has a balance due, but transaction B inserts a new, unpaid, invoice while transaction A is still running. After transaction A finishes, there is still an invoice with a balance due.

Phantom reads

See picture in definition for transaction Isolation levels.

Pic

Oracle does doesn't support the __________ __________ or __________ __________ transaction isolation levels. As a result, you must choose between the __________ level and the __________ __________ level (which is the default).

READ UNCOMMITTED, REPEATABLE READ; SERIALIZED, READ COMMITTED.

You can use the ___________ ___________ statement to rollback a transaction to the specified save point (i.e: [________] [__________] SAVEPOINT <save_point_name>).

ROLLBACK TO

To create a save point you code the _________ keyword followed by the name of the save point. (i.e: [___________] <save_point_name>)

SAVEPOINT

When two or more _________ statements read the same data, the _________ statements don't affect each other.

SELECT

A _______ is one or more SQL statements that perform a logical unit of work.

Transaction

If one or more SQL statements within a PL/SQL block encounters an error, you can code a ___________ statement to undo the changes. You can code the __________ statement in the exception block to undo changes that caused an error.

rollback

Oracle automatically executes a ___________ statement if the application crashes.

rollback

When you use ________ ________, you can rollback a transaction to the beginning of the transaction or to a particular _______ _______.

save points; save point

Write a statement that sets the transaction isolation level to Oracle's default.

set transaction isolation level read committed;

Write a statement that sets the transaction isolation level to serializable

set transaction isolation level serializable;

Syntax of the SET TRANSACTION ISOLATION LEVEL statement.

set transaction isolation level {read committed | serializable}

The __________ __________ __________ controls the degree to which transactions are isolated from one another.

transaction isolation level