Skip to Content

SAP HANA Concurrency Control

Responsibilities of Concurrency Control

The influence of each transaction may lead to inconsistent state of database when transactions run together even though every transaction can ensure correct state. As shown below, how the SAP HANA ensure the consistent state when many transactions write and read the shared data in database.

The inconsistency that concurrency transactions lead to includes:

  1. Lost update

When two or more transactions read and update the same tuple, every transaction did not know there are other transactions exist. The last update will overwrite other transaction’s update. It will lead to lost update.

  1. Dirty read

After transaction 1 updated tuple, transaction 2 read this tuple. But transaction 1 roll back because of some reasons, and the tuple has been restored. The data that transaction 2 read is dirty.

  1. Unrepeated read

After T1 read a tuple, T1 update the tuple. That lead to T1 cannot read original value of the tuple.

  1. Ghost data

After T1 gets some results by some search conditions, T2 inserts or deletes some tuples. When T1 search data by the same conditions, some data disappear or appear.

Serial schedule

To solve those problems, we need find the requirement that concurrent transactions keep the database consistent. The schedule that does not interleave the actions of different transactions is a serial schedule. The schedules like serial schedules are consistent. But in practice, this kind of schedules cannot reach aim of the concurrent processing. And SAP HANA cannot make use of the multiple cores if it use serial schedule.

Serializable schedule

For any database state, the effect (on the set of objects in the database) of executing the first schedule is identical to the effect of executing the second schedule. We call schedule a serializable schedule that is equivalent to some serial execution of the transactions. To realize serializable schedule, there are many methods such as two-phase locking, serialization graph checking, timestamp ordering and so on.

Multi version concurrency control

SAP HANA use multi version concurrency control to make schedule serializable. Each user connected to the database sees a snapshot of the database at a particular instant in time. Any changes made by a writer will not be seen by other users of the database until the changes have been completed (or, in database terms: until the transaction has been committed.

When an MVCC database needs to update an item of data, it will not overwrite the old data with new data, but instead mark the old data as obsolete and add the newer version elsewhere. Thus there are multiple versions stored, but only one is the latest. This allows readers to access the data that was there when they began reading, even if it was modified or deleted part way through by someone else. It also allows the database to avoid the overhead of filling in holes in memory or disk structures but requires (generally) the system to periodically sweep through and delete the old, obsolete data objects.

As shown below, transaction 1 creates the initial version V1 of data item D and is then committed. Transaction T3 updates data item D, which creates version V2. Since transactions T2 and T4 started before T3 is committed, both of them still see version V1 of the data item. The changes done by T3 are not visible to them, even if they read it after T3 is committed. For transactional isolation purposes, data version V1 must be kept at least until the last reading transaction is closed which can access this version. In the example this is transaction T4 (T5 starts when version V2 is already committed). After T4 is closed, version V1 can be removed by the version consolidation process (see 0).

Write Locks and Deadlock

Multi version concurrency control ensures consistent read operations. However, it does not prevent concurrent write operations on the same data and the associated inconsistencies (dirty write, lost updates). To prevent concurrent write operations on the same data record, the SAP HANA database uses exclusive write locks at row level. For each write access, a row-level write lock is obtained. Concurrent transactions which need to write the same record have to wait until the lock is released.

A deadlock situation occurs, if two transactions both need to update two records R1 and R2, and one transaction is given the lock for R1 and the other one the lock for R2. The transaction manager detects the deadlock and aborts one of the transactions. We can prove it through an experiment as shown below. There are two store procedures P1 and P2, P1 and P2 all need to update a table which content 10000 records. P1 updates the table from the last record, and P2 from the first record. They run almost at the same time, one procedure will be aborted.

Write Conflicts

With snapshot isolation, lost updates would occur if transactions were allowed to write records for which new versions were committed concurrently. Figure below shows an example with transaction level snapshot isolation. Transaction T1 sees only version V1 of data record R1, which was the committed version when T1 started. A concurrent transaction T2 modifies record R1 (version V2) and commits (which releases the record level write lock). However, because of transaction level snapshot isolation transaction T1 is not able to read the new version V2. It still sees V1 which was committed at the time T1 started. If T1 would be allowed to modify R1 and create version V3, the update made by transaction T2 (i.e. version V2) would be lost. To prevent this situation, T1 is not allowed to write record R1. The attempt to write R1 will fail with a serialization error.

To prove the write conflict:

  1. Open two SQL console C1 and C2, modify the properties of the C1 and C2 as below.

2. Execute SQL in C1:

select NAME from "TEST"."TEST1" WHERE ID = 1;

result:

3. Execute SQL in C2:

select NAME from "TEST"."TEST1" WHERE ID = 1;

update "TEST"."TEST1" SET name = 'C2' WHERE ID = 1;

COMMIT;

4. After finishing the step 3, execute SQL in C1

select NAME from "TEST"."TEST1" WHERE ID = 1;

update "TEST"."TEST1" SET name = 'C1' WHERE ID = 1;

Come out write conflict, the SAP HANA system do not allow to update, and throw error:

Tags:
Former Member