|
DBTech EXT* |
|
|
CCLab1: Isolation Levels and Concurrency Technologies To access the most up-to-date version of this VLW (in the form of a single ".pdf" file) click here. Part A: Pre-teaching material Study the Relevant Bibliography View Download Part B: Tutorial (Theoretical) Introduction Step-B1: SQL Concurrency Technologies Traditional concurrency control theory is covered in many textbooks, but the latest developments in the mainstream DBMS systems have not been covered properly in this literature. More accurate theory and usage descriptions can be found in the user manuals of these software products, and luckily these manuals are available for free as PDF editions in the Web. Based on these and our own experimentations, as we have written as theory part the paper "SQL Concurrency Technologies", which we encourage you to read at this step and try yourself the examples below. Don't take our word, but verify the topics yourself. Introduction-ACID or ACiD Transactions View Download Concurrency Control Techniques View Download Concurrency Technologies of Real DBMS Systems View Download Advanced Single-Conection Transaction models View Download Step-B2: Review Questions View Download Step-B3: Review Questions and model answers View Download Part C: Self-paced Lab practicing Following concurrency scenarios may lead to concurrency conflicts, and the typical lesson in textbooks is “Don’t do that!”. However, our pedagogical point is to experiment with these conflicts, so that we better understand these cases. There are many other potential scenarios for concurrency conflicts than those which we present below, and we need to cope with them. Step-C1: Appendix 1: On Behavior of Optimistic Concurrency Control (OCC) View Download Step-C2: Appendix 2: Concurrency Tests using SQL Server 2008 View Download Step-C3: Appendix 3: Concurrency Tests using DB2 LUW 9.7 View Download Step-C4: Appendix 4: Concurrency Tests using Oracle 11g 1 View Download Step-C5: Appendix 5: A Transaction Retry Model View Download Part D: Hands-On Laboratory Step-D1: This Lab is based on the tutorial. It introduces single-connection flat SQL transactions focusing on transaction concurrency problems and solutions View Download Step-D2: Concurrency Control Lab1-SQL Server Report View Download Best practices for transaction programming: Transactions should be as short as possible. Transactions should be idempotent. It means that transaction should not have any side effects on reruns. Especially a transaction should not include any interactions with the user of the application. To avoid deadlocks, it has been recommended that accessing resources in the database should follow the same order (see comments above). The effect of accessing order can be tested using our bank transfer example in Appendix 5. However, this rule cannot always be applied. Even if RBMS systems allow dynamic changes in the database structures, DDL commands concurrently with transactions may lead to blocking since DDL commands update the system tables. SQL Server uses special schema locks to manage concurrency with DDL commands. Oracle does not allow DDL commands inside transactions. Part E: Self-assessment MCQ Quiz* Multiple-choice questions on SQL Concurrency Technologies * Laboratory instructors may request for a copy of the MCQ quiz answers to be sent to them via private e-mail communicate with the VLW developers. * The DBTech EXT project has been funded with support from the European Commission. This publication reflects the views only of the author(s), and the Commission cannot be held responsible for any use which may be made of the information contained therein |