|
DBTech EXT* |
|
|
DBTech EXT Concurrency Labs (CCLabs) CCLab1: Isolation Levels and Concurrency Technologies It is a common and crucial misunderstanding that all you need to know on accessing databases is just the basic SQL commands independently of the DBMS to be used. It is also not enough to know on transaction processing only the COMMIT and ROLLBACK commands. SQL transactions are the basic unit of data integrity, durability, and recoverability in a database, and the ACID principle (of Atomicity, Consistency, Isolation, and Durability) defines the ideal SQL transactions. SQL standard does not say anything on concurrency control techniques, but hides the details by the concepts of isolation and isolation levels compromising the ideal isolation for the practical performance reasons. Database vendors have developed various concurrency control mechanisms, such as optimistic concurrency, locking schemes, and multiversioning to implement what they consider practical isolation levels of concurrent transactions. In our SQL Concurrency Technologies tutorial we introduce the basic concurrency concepts as usually presented in database literature, then presenting what kind of concurrency services the real RDBMS systems used by the industry provide, we reveal that there ain’t any 2PL DBMS systems of which you can read in the books. As application developer or database administrator, on reliable solutions you are on your own – you need to know your DBMS. CCLab2: TPCA2 Benchmark (under construction) In this lab, based on the old TPC-A benchmark of Transaction Processing Council (www.tpc.org), we will experiment with workload of concurrent transactions by multiple client sessions on accessing a simplified database of a bank application using the tutorial TPCA2. We can experiment with
• data access pattern of accessing the server with separate SQL commands, or having the commands implemented as a stored procedure. The original TPC-A benchmark was defined by Jim Gray in 1985. We have adapted our TPCA benchmark from the book "Client /Server Programming with JAVA and CORBA" by Orfali and Harkey. CCLab3: Data Access Patterns and RVV Discipline (under construction) Database textbooks and product manuals often forget the real data access needs of applications on implementing user transactions. A user transaction, based on use case requirements, will often need an interrelated sequence of SQL transactions, which may need to use different isolation levels. In this lab based on our "RVV Paper" we will focus on use case transaction programming discipline of row version verifying, avoiding so called Blind Writes. CCLab4: Distributed Transactions (to be developed in future) Local transactions in multiple DBMS servers (resource managers, RM) can be combined as a "global" transaction, in which all local transactions need to get committed or all rolled back as an atomic unit of work. The atomicity of the distributed transaction is coordinated by a global Transaction Manager (TM). In case the servers are built of similar DBMS editions and releases, the distributed transaction is called homogenous, otherwise heterogeneous. The local transactions may use different isolation levels. We will show that it is possible to apply this for implementation of user transaction with RVV Discipline, but this will require careful planning. * 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. |