\documentclass[landscape]{slides} \usepackage{alltt} \setlength{\topmargin}{-1.25in} \addtolength{\textheight}{1.25in} \def\slist{\list{-}{ {\setlength{\parsep}{0ex plus0.2ex} \setlength{\itemsep}{0.2ex plus0.2ex minus0.2ex} } }} \let\endslist\endlist \def\salltt{\vspace{-\baselineskip}\alltt} \let\endsalltt\endalltt \def\stitle#1{ \begin{center} \large #1 \end{center} } \begin{document} \pagestyle{plain} \special{papersize=11in,8.5in} \begin{slide} \stitle{Select Into Example} \begin{slist} \item \begin{salltt} EXEC SQL SELECT pop_id, potype INTO :id, :potype FROM users WHERE users_id = :user; \end{salltt} \item The \texttt{into} clause stores the data from a select returning one row into variables in the host programming language. \item When host variables are used in embedded SQL, they are prefixed with colons. \item Care must be taken with values that could be null. \end{slist} \end{slide} \begin{slide} \stitle{Declare Cursor Example} \begin{slist} \item To retrieve multiple rows in embedded SQL, you need to declare a cursor: \begin{salltt} EXEC SQL DECLARE csr103 CURSOR FOR SELECT list_id, ref_count FROM imembers WHERE member_id = :lid AND member_type = 'LIST'; \end{salltt} \item The scoping of the cursor name is poorly defined. In general, only use it within the current function. \end{slist} \end{slide} \begin{slide} \stitle{Open and Fetch} \begin{slist} \item \begin{salltt} EXEC SQL OPEN csr103; /* Check for errors would go here.*/ while (1) \{ EXEC SQL FETCH csr103 INTO :id, :ref; \} \end{salltt} \item \texttt{fetch} returns an error on the last row. \item Some errors may be returned at open, others at first fetch. \item Again, beware of nulls. \end{slist} \end{slide} \begin{slide} \stitle{Perl and DBI} \begin{salltt} my $cursor_a = $dbh->prepare(``select mode,ino,name from ?''); $cursor_a->execute('/usr'); my @row = $cursor_a->fetchrow(); \end{salltt} \end{slide} \begin{slide} \stitle{Simplistic, Incorrect Procedure for Checking Out Book} \begin{slist} \item Confirm copy is not currently checked out. \item Remember the number of checkouts for the book. \item Update the copies table indicating that the book is \texttt{out\_to} the appropriate member. \item Update the checkouts in the books table to be the number remembered plus one. \end{slist} \end{slide} \begin{slide} \stitle{Problems with Simplistic Procedure} \begin{slist} \item 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. \item If two copies of the book are checked out at the same time, the checkouts will be updated incorrectly. \item Both these problems could be fixed by atomic use of update: \begin{salltt} update books set checkouts = checkouts + 1 where \textit{constraint} \end{salltt} \end{slist} \end{slide} \begin{slide} \stitle{Why does Atomic Update Work?} \begin{slist} \item The final result of checkouts is independent of order. \item The update of copies is idempotent by using \texttt{where out\_to is null}; if executed multiple times, only the first update will take effect. \item 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 \texttt{out\_to} field by performing a select. \item Not all concurrency problems can be solved by simple atomic operations. \end{slist} \end{slide} \begin{slide} \stitle{Incorrect Procedure for Fining Member} \begin{slist} \item Select available fine credit into $C$ for member being fined. Let $f$ be amount of fine. \item Insert fine of $f-c$ into fines table if $f-c > 0$. \item Set fine credit to the maximum of 0 and $c-f$. \end{slist} \end{slide} \begin{slide} \stitle{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: \begin{tabular}{ll} Librarian A & Librarian B \\ \hline 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 \\ \end{tabular} Final state is credit \$1, outstanding fine of \$1; not only should these cancel, but the member is only down \$2 total, not \$4. \end{slide} \begin{slide} \stitle{Transactions} \begin{slist} \item Transactions allow multiple operations to be treated atomically. \item In general, no change made in the transaction is visible to any other session until that transaction \textit{commits}. \item If a transaction \textit{rolls back}, then no change it made is ever externally visible\footnote{You can read uncommitted data if you try, but we ignore this.}. \end{slist} \end{slide} \begin{slide} \stitle{Transaction Isolation Leveles} \begin{slist} \item 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. \item Other isolation levels exist: read committed, repeatable read and read uncommitted. \item The fine procedure is correct if executed inside a serial transaction. \end{slist} \end{slide} \begin{slide} \stitle{Using Transactions} \begin{slist} \item Normally, all SQL statements should be in a transaction. Some implementations require you to give a statement to start a transaction. \item The \texttt{commit work} statement commits the transaction and saves all data. \item The \texttt{rollback} statement removes any changes made by the current transactions. \item The \texttt{set transaction isolation level serializable} guarantees serial execution order. \end{slist} \end{slide} \begin{slide} \stitle{How are Transactions Implemented} \begin{slist} \item As rows are updated, they are locked. \item Oracle and many other databases tend to avoid read locks. \item Deadlocks are possible; often deadlock detection or timeouts are implemented. \item Serialization requires either read locks or writers to get random serial failures. \end{slist} \end{slide} \begin{slide} \stitle{When Additional Locking is Required} \begin{slist} \item If the entire transaction is not serialized or if your database does not have sufficient granularity for serialization \item If the module desiring locking is called from some larger application, it may not be able to require serialized transactions. \end{slist} \end{slide} \begin{slide} \stitle{Handling Concurrency Without Full Serialization} \begin{slist} \item If new information doesn't introduce integrity problems, then less isolation may work. \item Oracle and other databases provide primitives to lock a table. \item Other locking facilities inside or outside the database. \end{slist} \end{slide} \begin{slide} \stitle{Indexes} \begin{slist} \item Indexes allow a database to perform queries faster. \item \begin{salltt} create index \textit{index\_name} on \textit{table}(\textit{col1}, \textit{col2}, ...); \end{salltt} \item Queries that select a few rows based on the values of indexed columns can be executed more quickly with the index. \item If used, indexes can slow down queries that touch a large number of rows. \item Oracle automatically creates unique indexes for unique and primary keys. \end{slist} \end{slide} \begin{slide} \stitle{Joins and Indexes} \begin{slist} \item A nested-loops join uses indexes to provide quick joins between tables. \item Merge join will sort two tables and merge the results. \item Hash join probes a hash of one table with another. \item Nested-loops much faster for small joins , but much slower for larger joins. \end{slist} \end{slide} \begin{slide} \stitle{Exercise: Library Inventory} \small 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. \end{slide} \end{document}