Relational Database Management System has isolation levels. Alinous Elastic DB has 2 isolation levels, SERIALIZABLE and READ COMMITTED. They are different feature, and suitable usage.
The transaction of each isolation level executed different way in locking. Therefore their performance and way to keep consistency is different.
READ COMMITTED is the default isolation level, and it is best way to OLTP applications.
The main phase of a transaction starts from BEGIN SQL statement.
Distributed Relational Database Management Systems can commit transactions as parallel process. In order to implement that, implicit row lock is necessary.
When we start a transaction, and executing UPDATE, DELETE, and INSERT SQL Statements, they implicitly lock database objects.
Implicit row lock is done on executing UPDATE, DELETE Statement, after started a transaction before end of it.
Both SQL Statements has WHERE clause in it. It requests scanning to storage engines. Then update lock is done for filtered rows.
After getting result from storage engines, the transaction engine filters the result, again. Then filtered row's lock is released.
Distributed Database is to handle big data with some remote networks nodes, so how to reduce network data is necessary issue.
Especially using database for OLTP use, it is essential to reduce it.
The distributed database has storage engine architecture. Alinous Elastic DB has following one.
The storage engine is accessed by SQL table access coordinator, which is called Region Manager in this database. It request scanning table to the storage engine, then it returns the result.
When we update key's value of table partition on Remote Storage Node, it starts data transfer to the next remote node. Then the database does not stop, and it can process SQL statements.
In this article, I mention how to replicate and delete old data without stop.
This database is designed for dynamic table partitioning.
When rebuilding table partitions, it transfer data for next partitioning structure. Then it does not have to stop or lock the database.
Now, 2017/02/05. To make and manage partition table of database tables is essential to deal bigdata. Therefore I have to design the architecture now.
Whether dynamic maintenance of partitioning is possible or not is also up to the records format of storage engine.
Alinous Elastic DB has 2 layer table data partitioning. By using this function, you can manage cold and hot data in different cloud section.
For example, the main partition key is timestamp of the record created, and sub key is the region. The old data is managed in storages with big capacity, but not so fast. Hot data is manages in storages which are very fast, but not with so big capacity.
Traditional Enterprise Relational Database has Partitioning function. It has partitioning information in the schema metadata. Alinous Elastic DB also have it.
And it is designed for Dynamic Table Partition on the distributed remote nodes.
Generally, Relational Databases set partition information on creating schema. Actually most of them use CREATE TABLE Statement to set it.
But in this way, it is hard to update the partition keys.
Therefore, Alinous Elastic DB has the data in the remote storage node.
Read "How Dynamic Partition Table Data Grows Up" more
The Storage Engine of Distributed Database has records in it. The format have to resolve ACID status with the record's information.
Especially on using partitioning, the format is very important to make the performance high.
There are two types of policies in database records management supporting ACID. When the records are changed, one of following operations are done.
MySQL uses the first method, and PostgreSQL uses second one. The Alinous Elastic DB uses the second method like PostgreSQL.
The actual format is following.
Read "Storage Records Data Format for Partitioning" more
Now, on 2017/02/04, I am making INSERT SQL statement works on distributed database. Now making the framework of executing distributed queries.
The execution of INSERT SQL statement is done by following order.
At first, SQL Engine Starts Transaction. Then it access Trx Monitor to get current node clusters and scheme version. This versions make it possible to detect the change of schema and clusters.
The INSERT SQL Statement is handled without accessing other distributed nodes, the ACID logs in the Transaction Engine are changed before commit it.
Read "Making INSERT SQL on Remote Database" more
Today, Jan 29th 2017, I've created the CREATE TABLE SQL statement. Before that, I have been creating main framework of the distributed database.
It takes a lot of time, but finally, I come to be able to implement actual SQL functions.
The software framework is very important, especially, on making distributed database.
That is because it has a lot of bottle neck in performance.
MySQL's storage engine is like this database software, but mine has region manager to support TABLE LOCK between SQL Transaction Engine and Storage Nodes.
Read "Started to create SQL on remote nodes" more