[ General Information
| Lectures
| Handouts
| Other Pointers
| Requirements
]
[ 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.
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".
Handouts
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
Requirements
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.