SQL Database Assignment: Transactions, Locking, and SQL Code Examples

This assignment presents a comprehensive case study on SQL database transactions. It provides SQL code for various scenarios, including credit purchases and payments, using BEGIN TRANSACTION and COMMIT statements to ensure data integrity. The assignment also covers transaction logging, detailing the actions performed during transactions. Furthermore, it explores pessimistic locking and the two-phase locking protocol, illustrating the chronological sequence of locking, unlocking, and data manipulation activities. The solutions address both scenarios with and without the two-phase locking protocol, offering a detailed understanding of database concurrency control mechanisms. The assignment covers the use of INSERT, UPDATE statements, and also focuses on locking mechanisms at different granularities.