You can choose to skip this section if you are not a professional programmer. This won’t affect your learning about the other contents of this Tutorial.
Database Connection Management introduces how to commit and rollback transaction. This section will discuss in detail the execution of the database transactions under different transaction isolation levels.
SQL is an abbreviation for Structured Query Language. Functionally, the SQL statements are divided into four categories:
(1) Data definition language（DDL）
(2) Data manipulation language（DML）
(3) Data control language（DCL）
(4) Transaction control language（TCL）
DDL statements define or describe the different structures in a database by letting you create, modify and remove a database, a database table, a database index, etc. The statements include CREATE DATABASE, DROP TABLE, and so on. Since DDL statements cause changes to the database definition, they may need to be disabled in projects requiring high security.
DML statements and Data Query Language statements (DQL) query and manipulate database data, like SELECT, INSERT, DELETE and UPDATE. DML statements are the most used when accessing and querying data in a database.
DCL statements give or take back privilleges to access data in a database, including GRANT, DENY, and REVOK, etc. Generally the statements are useless in esProc.
TCL statements manage transactions in a database, including SAVEPOINT, ROLLBACK, and COMMIT, etc. In esProc, the statements are expressed by a set of functions.
A transaction isolation level determines whether the changes made by one data access operation become visible when there is concurrent execution of transactions trying to update the data in a database. esProc defines a transaction as the execution of a set of SQL statements within one database connection.
Generally data will be committed or rolled back only when a corresponding TCL statement is executed in a transaction. But when a DDL statement is executed in a transaction, a COMMIT will be automatically executed to update the unstored data into the database. For instance, a COMMIT operation will be automatically executed for a CREATE TABLE statement creating a table in the database.
There are four levels of transaction isolation:
(1) Read committed. This is the default isolation level supported by most of the databases. It allows a transaction to read the committed data.
(2) Read uncommitted. The isolation level allows each transaction to read the uncommitted data of any other running transaction. The data read by a transaction may not be the final result, which is called dirty reads.
(3) Repeatable read. The isolation level allows multiple parallel instances in one transaction to read the same data. These instances may influence each other and lead to phantom reads.
(4) Serializable. This is the highest isolation level. It guarantees a serializable execution of the concurrent transactions, preventing dirty reads or phantom reads as well as having the lowest efficiency because of time override or competition for the shared lock.
esProc supports using options in the connect function to specifiy different transation isolation levels - @ c for read committed, @u for read uncommitted, @r for repeatable read, and @s for serializable.
esProc also provides db.isolate() function to get the transaction isolation level in an existing database connection, or to change the isolation level by adding different options.