Select Into Example

- EXEC SQL SELECT pop_id, potype INTO :id, :potype FROM users
      WHERE users_id = :user;

- The into clause stores the data from a select returning one row into variables in the host programming language.

- When host variables are used in embedded SQL, they are prefixed with colons.

- Care must be taken with values that could be null.

Declare Cursor Example

- To retrieve multiple rows in embedded SQL, you need to

declare a cursor:

    EXEC SQL DECLARE csr103 CURSOR FOR
      SELECT list_id, ref_count   FROM imembers
      WHERE member_id = :lid AND member_type = 'LIST';

- The scoping of the cursor name is poorly defined. In general, only use it within the current function.

Open and Fetch

- EXEC SQL OPEN csr103; /* Check for errors would go here.*/
    while (1) { EXEC SQL FETCH csr103 INTO :id, :ref; }

- fetch returns an error on the last row.

- Some errors may be returned at open, others at first fetch.

- Again, beware of nulls.

Perl and DBI

my $cursor_a = $dbh->prepare(``select mode,ino,name from ?'');
$cursor_a->execute('/usr');
 my @row = $cursor_a->fetchrow();

Simplistic, Incorrect Procedure for Checking Out

Book

- Confirm copy is not currently checked out.

- Remember the number of checkouts for the book.

- Update the copies table indicating that the book is out to the appropriate member.

- Update the checkouts in the books table to be the number remembered plus one.

Problems with Simplistic Procedure

- A book may be checked out between the time that the procedure confirms it isn’t checked out and the copies table is updated. We could argue this isn’t a problem since a copy should only exist at one checkout station.

- If two copies of the book are checked out at the same time, the checkouts will be updated incorrectly.

- Both these problems could be fixed by atomic use of update:

update books set checkouts = checkouts + 1
    where constraint

Why does Atomic Update Work?

- The final result of checkouts is independent of order.

- The update of copies is idempotent by using where out to is null; if executed multiple times, only the first update will take effect.

- The checkout program can check to see if the update took effect or the copy was checked out by someone else by looking at the out to field by performing a select.

- Not all concurrency problems can be solved by simple atomic operations.

Incorrect Procedure for Fining Member

- Select available fine credit into C for member being fined. Let f be amount of fine.

- Insert fine of f − c into fines table if f − c > 0.

- Set fine credit to the maximum of 0 and c − f.

Problem with Incorrect Fining Procedure

Assume the member starts with $2.00 fine credit; Librarian A wants to fine them $3.00 while at the same time Librarian B wants to fine them $1.00:

Librarian A  Librarian B

set C = 2

set C = 2

set f = 3

set f = 1

insert fine of f − c = 1

f − c < 0 so insert no fine

c − f < 0 so set credit to 0 c − f = 1 so set credit to 1

Final state is credit $1, outstanding fine of $1; not only should these cancel, but the member is only down $2 total, not $4.

Transactions

- Transactions allow multiple operations to be treated atomically.

- In general, no change made in the transaction is visible to any other session until that transaction commits.

- If a transaction rolls back, then no change it made is ever externally visible^{∗}.

∗You can read uncommitted data if you try, but we ignore this.

Transaction Isolation Leveles

- OFten, you want to guarantee that there is effectively a serial order of transaction execution. That is, there is an ordering of transactions so that from external appearances, one transaction was completed before the next started.

- Other isolation levels exist: read committed, repeatable read and read uncommitted.

- The fine procedure is correct if executed inside a serial transaction.

Using Transactions

- Normally, all SQL statements should be in a transaction. Some implementations require you to give a statement to start a transaction.

- The commit work statement commits the transaction and saves all data.

- The rollback statement removes any changes made by the current transactions.

- The set transaction isolation level serializable guarantees

serial execution order.

How are Transactions Implemented

- As rows are updated, they are locked.

- Oracle and many other databases tend to avoid read locks.

- Deadlocks are possible; often deadlock detection or timeouts are implemented.

- Serialization requires either read locks or writers to get random serial failures.

When Additional Locking is Required

- If the entire transaction is not serialized or if your database does not have sufficient granularity for serialization

- If the module desiring locking is called from some larger application, it may not be able to require serialized transactions.

Handling Concurrency Without Full Serialization

- If new information doesn’t introduce integrity problems, then less isolation may work.

- Oracle and other databases provide primitives to lock a table.

- Other locking facilities inside or outside the database.

Indexes

- Indexes allow a database to perform queries faster.

- create index index name on table(col1, col2, ...);

- Queries that select a few rows based on the values of indexed columns can be executed more quickly with the index.

- If used, indexes can slow down queries that touch a large number of rows.

- Oracle automatically creates unique indexes for unique and primary keys.

Joins and Indexes

- A nested-loops join uses indexes to provide quick joins between tables.

- Merge join will sort two tables and merge the results.

- Hash join probes a hash of one table with another.

- Nested-loops much faster for small joins , but much slower for larger joins.

Exercise: Library Inventory

For the annual Inventory, the library would like to produce a Shelfdex: a list of all the book copies that should currently be in the library, sorted by where they are on the shelves.

First, circulating copies should be separated from non-circulating. Copies that are currently checked out should be ignored.

Within each of these categories, books should be alphabetized by author. If the book has multiple authors, we want only the first one (alphabetically).

Next, the books should be sorted alphabetically by series; if the book is not in a series, then its title should be used here.

Next, the books should be sorted by series position; position-less books may be placed either before or after the others.

Next, those books with equivalent series and position should be sorted by title.

Output the resulting ordered list of bookid, circ, author, series, position, title, and of course number of identical copies.