CSE/ISE315 (Spring `06)
Database Transaction Processing Systems

[ General Information | Lectures | Handouts | Other Pointers | Requirements ]
[ Announcements ]

General Information

Course description: This course is for students interested in learning about (1) properties of transactions including serializability, recoverability, atomicity, and durability and their implications for system behavior and performance, (2) models of transactions and how they influence the design of applications involving transactional access to a database, (3) implementation and support of transaction processing in modern relational and non-relational database transaction processing systems, such as concurrency control and logging, and (4) architecture of modern transaction processing systems and how communication, security, and replication are implemented in distributed transaction processing systems. | Prerequisites: CSE/ISE305. | Credits: 3.

Instructor: Annie Liu | Email: liuATcsDOTsunysbDOTedu | Office: Computer Science 1433 | Phone: 632-8463.

TA: Pramod Adiddam | Email: padiddamATcsDOTsunysbDOTedu

Hours: Tue Thu 9:50-11:10AM, Tue in CS 2129, Thu in CS 2311 (no more Cheold 116).
Annie's office hours: Tue 9-9:50AM, 12:30-1PM, Thu 11:10AM-12:30PM, in CS 1433.
Pramod's office hours: Fri 12-2PM, in CS 2110.

Textbook: Database Systems: An Application-Oriented Approach by Michael Kifer, Arthur Bernstein, and Philip M. Lewis. Addison Wesley, 2006. In addition, taking some good notes during the lectures can help you better understand the materials.

Grading: Homework/project assignments, a midterm, and a final, each worth 30%, 30%, and 40%, respectively, of the grade. Reduced credit for late submissions, 20% per day.

Course homepage: http://www.cs.sunysb.edu/~liu/cse315/, containing all course related information.

Lectures Outline

Properties and models of transactions
Lecture 1 (01/24/06): Overview: transactions, consistency, atomicity, durability, and isolation. Reading: Ch.18. Assignment 1.
Lecture 2 (01/26/06): Models of transactions: flat transactions, savepoints, distributed transactions, nesting, chaining. Reading: Ch.19-19.3.1.
Lecture 3 (01/31/06): Models of transactions: review; compensation, declarative demarcation, multilevel transactions. Reading: Ch.19.3.2-4.
Lecture 4 (02/02/06): Models of transactions: review; recoverable queues, uses; workflow, control and data flows, properties. Reading: Ch.19.3.5-6. Assignment 2.

Implementing isolation in relational and non-relational systems
Lecture 5 (02/07/06): Implementing isolation: concurrency control, serializable schedule, conflict and view equivalence, strict schedule. Reading: Ch.20-20.2.
Lecture 6 (02/09/06): Concurrency control: review; immediate-update pessimistic: no conflict among active transactions, locking implementation, two-phase locking. Reading: Ch.20.3-5.
Lecture 7 (02/14/06): Immediate-update pessimistic control: review; objects and semantic commutativity, recoverability, locking implementation of structured transaction models. Reading: Ch.20.6-20.8.4.
Lecture 8 (02/16/06): Implementation of multilevel transactions; timestamp-ordered concurrency control; optimistic concurrency control (to finish). Reading: Ch.20.8.5-20.9. Assignment 3.
Lecture 9 (02/21/06): Concurrency control review; isolation in relational databases: phantom, predicate locking. Reading: Ch.21-21.1.
Lecture 10 (02/23/06): SQL isolation levels, other isolation levels, locking implementation; anomalies: dirty read, lost update, nonrepeatable read, phantom, bad and good examples. Reading: Ch.21.2.
Lecture 11 (02/28/06): Granular locking, intension locks, index locks; multi-version concurrency control: read-only, read-consistency, snapshot isolation. Reading: Ch.21.3-5.

Implementing atomicity and durability
Lecture 12 (03/02/06): Logs for immediate update systems, update/begin/commit/abort records, checkpoints, write-ahead; log buffers and cache, force policy for commit. Reading: Ch.22-22.2.1. Assignment 4.
Lecture 13 (03/07/06): No-force policy for commit, fuzzy checkpoints, logical and physiological logging; recovery in deferred-update systems; recovery from media failure. Reading: Ch.22.2.2-22.4.

Lecture 14 (03/09/06): Midterm review: topics covered and emphasis, sample problems and solutions. Reading: Preparation for Midterm Exam.
Midterm exam (03/14/06): In-class exam. You can prepare one hand-written personal "crib sheet".

Architecture of transaction processing systems
Lecture 16 (03/16/06): Centralized vs distributed TP systems, 2-tiered and 3-tiered models, sessions and context, queued TP; TP monitor overview, transaction manager. Reading: Ch.23-23.4.
Lecture 17 (03/21/06): Communication services: remote procedure call and transactional RPC, peer-to-peer and syncpoint, event comm and event broker; storage architectures. Reading: Ch.23.5-8.
Lecture 18 (03/23/06): TP on the internet: 2,3,4-tiered; J2EE, EJB: entity/session/msg-driven beans; deployment descriptor: persistence, transaction, authorization. Reading: Ch.23.9-23.10.1. Assignment 5.
Lecture 19 (03/28/06): EJB container: implement deployment descriptors for beans under interfaces; container managed persistence and transactions; using Java beans. Reading: Ch.23.10.2-3.

Implementing distributed transactions
Lecture 20 (03/30/06): Distributed transactions; atomic commit protocol, two-phase commit: prepare, vote, commit/abort, done, failures: timeout and restart protocols. Reading: Ch.24-24.3.
Lecture 21 (04/04/06): Variants of global commit: linear commit and others; global deadlock detection and prevention; global isolation with 2-phase locking and 2-phase commit. Reading: Ch.24.4-6.
Lecture 22 (04/06/06): Replication: replica control, read one/write all, mutual consistency; synchronous update: quorum consensus; asynchronous: primary copy and variations. Reading: Ch.24.7-8. Assignment 6.

Spring break: April 10-14. Have a nice break!

Security and electronic commerce
Lecture 23 (04/18/06): TPS and internet security; encryption, secrete-key cryptography, DES, public-key cryptography, RSA, digital signatures, msg digest; authentication, Kerberos key server. Reading: Ch.26-26.4.1.
Lecture 24 (04/20/06): Kerberos protocol, Kerberos single sign-on, nouces; authorization, ACLs; authenticated RPC; E-commerce; secure socket layer (SSL) certification authority. Reading: Ch.26.4.1-26.8.
Lecture 25 (04/25/06): SSL protocol; single sign-on, Passport; private credit card numbers, PayPal; secure electronic transaction (SET) protocol; goods atomicity, certified delivery, escrow agent. Reading: Ch.26.8-12.
Lecture 26 (04/27/06): Electronic cash: money atomicity, tokens, properties, creating and spending, anonymous e-cash protocol; review of security and e-commerce. Reading: Ch.26.13.
Lecture 27 (05/02/06): "Pop" quiz; Web service security: XML Encryption, XML Signatures, WS-Security; Security Assertion Markup Lang (SAML): stmts for authentication, attributes, authorization. Reading: Ch.26.14.

Lecture 28 (05/04/06): Final review: topics covered and emphasis, sample problems and solutions. Reading: Preparation for Final Exam.
Final Exam (05/16/06): 8:00-10:30AM, in CS2129. You can prepare two hand-written personal "crib sheets".


Handout Q: Questionnaire

Handout Q2: Questionnaire 2

Slides for Ch.18: ACID Properties of Transactions

Slides for Ch.19: Models of Transactions

Slides for Ch.20: Implementing Isolation

Slides for Ch.21: Isolation in Relational Databases

Slides for Ch.22: Implementing Atomicity and Durability

Slides for Ch.23: Architecture of Transaction Processing Systems

Slides for Ch.24: Implementing Distributed Transactions

Slides for Ch.26: Security and Electronic Commerce

Handout A1: Assignment 1: A Transaction Processing System

Handout A2: Assignment 2: Savepoints, Nested Transactions, and Chained Transactions

Handout A3: Assignment 3: Stored Procedures

Handout A4: Assignment 4: Isolation Levels, Lost Updates, and Deadlocks

Handout A5: Assignment 5: Phantoms

Handout A6: Assignment 6: Indexing and Performance

Handout P1: Pop Quiz 1

Handout P2: Pop Quiz 2

Handout P3: Pop Quiz 3

Handout E1: Preparation for Midterm Exam

Handout E2: Midterm Exam

Handout E3: Solutions to Midterm Exam

Handout E4: Preparation for Final Exam

Handout E5: Final Exam

Handout E6: Solutions to Final Exam

Other Pointers

Transaction Processing Lab: accounts, policies, FAQ, Sybase, JDBC, etc.

Sybase Adaptive Server Enterprise 12.5 Collection (Core Documentation Set):
Transact-SQL User's Guide
Java in Adaptive Server Enterprise


You should learn all information on the course homepage. Check the homepage periodically for Announcements.

Do all course work. The assignments are integral parts of the course as they provide concrete experiences with the basic concepts and methods covered in the class.

Your handins, whether on paper or in electronic form, should include the following information at the top: your name, student id, course number, assignment number, and due date.

Your work should be submitted in a neat and organized fashion; for handins on papers, if your handwriting is hard to read, then your work needs to be typed.

Your approach to solving problems is as important as your final solutions; you need to show how you arrived at your solutions and include appropriate explanations.

If you feel your grade was assigned incorrectly, please bring it up no later than two weeks after the assignment was returned to the class.

All assignments must be done individually, unless specified otherwise; you may discuss with others and look up references, but you must write up your solutions independently and credit all sources that you used. Any plagiarism or other forms of cheating will result in an F or worse.

Disability: If you have a physical, psychological, medical or learning disability that may have an impact on your ability to carry out assigned course work, please contact the staff in the Disabled Student Services office (DSS), Room 133 Humanities, 632-6748/TDD. DSS will review your concerns and determine with you what accommodations are necessary and appropriate. All information and documentation of disability are confidential.

Annie Liu