Concurrency

Physical modelMaintaining the integrity of a database and ensuring the data stored within the database never becomes inconsistent is a major concern for the DBMS. This topic covers:

  • What is a transaction?: provides an overview of transactions and their role in ensuring data integrity
  • Controlling concurrent access: looks at techniques used to ensure that the results of multiple transactions do not interfere with each other

What is a transaction?

Textbook reading
Ch7 pp 348-349

Two readings are provided to explain transactions and Oracle:

  1. The first, 1-page reading from your textbook Ensuring Transaction
    Integrity gives an overview of transactions
  2. The second, online reading Transaction Management is from the Oracle
    manual and details how Oracle implements transactions.
Web link
Transaction Management
Remember
Transactions are atomic, that is all the statements in the transaction must complete successfully or none of the statements should be executed.

 

If you wish, you can now complete the Commit and Rollback tab of Getting your hands dirty.

 

Controlling concurrent access

Textbook reading
Ch11 pp 527-533

Having seen how to ensure the integrity of an individual transaction, the next step is to ensure that simultaneous transactions do not interfere with each other. The reading, Controlling Concurrent Access, details the problems that can arise with concurrent access and some of the possible solutions.

Have a go: concurrency control

 

 

Getting your hands dirty


Commit and Rollback

Log on to SQL*Plus and try the following out on the INVOICE or LIBRARY database:

Using ROLLBACK

Read about COMMIT and ROLLBACK in the Oracle manual. Ignore the references made to distributed transactions.

  1. Make a change to the database such as adding a new record (a SELECT statement is not a modification).
  2. View your new record e.g. use a SELECT statement.
  3. ROLLBACK the change.
  4. Rerun your SELECT statement from step 2 to see your new record

What do you see? View the answer

The database is in its original state, that is the record you added is no longer there.

Using COMMIT

  1. Add the record from step 1 above
  2. View your change
  3. COMMIT the change
  4. ROLLBACK the change.
  5. Rerun your SELECT statement to view the change

What do you see? View the answer

The database is changed as the record you added is there. Once a transaction has been committed it can not be undone with the ROLLBACK statement.

Using SAVEPOINT

Read about SAVEPOINTS in the Oracle manual.

  1. Make a change to your database e.g. add another record
  2. View the change
  3. Add a SAVEPOINT
  4. Make a second change i.e. add a second new record to the same table
  5. View both changes
  6. ROLLBACK to the SAVEPOINT
  7. Rerun your SELECT statement to view the change

What do you see? View the answer

Only the first change is there as the second change has now been undone.

 

Concurrent access

Form yourself into pairs to investigate how Oracle handles concurrent access.

One reader, one writer

  1. Using the GRANT command, give your partner write access to one of your tables.
  2. At the same time view the table, that is issue a SELECT query simultaneously. Can you both view the table?
  3. One of you now update the table (e.g. execute an UPDATE statement). Do not COMMIT the changes.
  4. Both try and view the change. Can you both view the change?
  5. COMMIT the change.
  6. Both try and view the change. Can you both view the change?

Two writers

1. Both try and update the table at the same time. Do not COMMIT the changes.
What happens? Why?
2. COMMIT the change.
What happens? Why?

Locking level

With your partner, work out what locking level Oracle has in place when a user tries to update one row. If necessary you may need to review the section on Locking Level p. 520 in your textbook.

Remember
Have one partner update a row in the table while the other partner tries successively to update another table, another row in the same table, another field in the same row and finally the same field. From this you can deduce the locking level.