posted time Created time: 2017-02-15 Last updated time:

Implement INERT SQL Statement

This page is about internal implementation procedure of INSERT SQL statement in both Local & Remote SQL Mode.

On Transaction Engine

On transaction phase, from executing BEGIN to before COMMIT or ROLLBACK statement, the transaction engine uses record cache.

The cache is used to store the updates of records. It is available from the transaction.

About record cache engine, please take a look at Execute Transaction Concurrently with Record Cache.

INSERT into Record Cache

The records in the INSERT statement is added to the cache. No locks are necessary, that is because it is transactionally independent.

READ COMMITTED and SERIALIZABLE isolation level uses same logic. And it is same in both Local and Remote SQL mode.

Oid of the Records

The temporary Oids are published when the records are added into record cache. The cached records have Oids with minus values.

The temporary Oids are published locally. That is because the cache is transactionally independent.

Locks new Record's Primary Key

When the transaction uses READ COMMITTED isolation level, the transaction engine uses Unique Exclusive Lock to the record's primary key. In SERIALIZABLE isolation level, it  also use locks.

This lock locks primary key, when UPDATE statement changes primary key of records, the keys are also locked.

If the transaction locks same primary key twice, it fails.

The Unique Exclusive Lock fails if other transaction already locked the primary key. Then, the INSERT statement fails because of that.

Local SQL Mode

In Local SQL Mode, the Transaction Engine uses the Unique Exclusive Lock.

Remote SQL Mote

In Remote SQL Mode, the Region Manager uses the Unique Exclusive Lock. If the Region Manager is scalable, the locks become distributed lock.

Commit Phase

On Commit Phase, it actually inserts cached records into storage engines. Both the READ COMMITTED and SERIALIZABLE isolation level use same procedure.

Check If Storage Engines already have Duplicated Key

Before insert records, check the storage engines do not have records with duplicated primary key. The Unique Exclusive Lock is to check records which will newly be added by transactions.

Local SQL Mode

Simply check the table records in the local storage.

Remote SQL Mode

Two Phase Commitment

In remote SQL, it does following procedure.

  1. Create records collections & check condition collections, for each partition node
  2. Send PREPARE request to the nodes

The PREPARE request is for two phase commitment. If the request fails, that means there are duplicated primary keys.

Publish Actual Oids for Each Records

Database Record Structure with Oid

The records has temporary Oids. Therefore, it have to publish actual Oids.

Local SQL Mode

Local Oid publisher publish Oids and set them to records.

Remote SQL Mode

In prepare phase of internal two phase commitment, the storage engine requests publishing Oids to Transaction Monitor.

Insert into Storage Engines

Actually insert cached records into the storage engine. In this phase the records are independent, because the Unique Exclusive Lock & storage check rejects contention of primary keys.

Local SQL Mode

Simply insert them into local storage engine.

Remote SQL Mode

Send commit request of two phase commitment to the storage. Than actually the records are inserted into the table.

Release Lock

After inserted the records, release all locks.

Go to Top