[ General Information
| Other Pointers
[ Announcements ]
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.
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
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
Transaction Processing Lab: accounts, policies, FAQ, Sybase, JDBC, etc.
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.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.