Concurrency is the most important feature of OLTP databases, but concurrency involves obtaining, sharing, and locking resources. On-line Transaction Processing (OLTP)

 

Summary: The biggest advantage is that the input data can be processed in real time, timely response. Also known as Real time System.

OLTP is jointly completed by the foreground, application, and database. The processing speed and degree depend on the database engine, server, and application engine.

OLTP databases are designed to enable transactional applications to write only as much data as they need to process individual transactions as quickly as possible.

 

Concurrency and parallellism are:

  1. Explanation 1: Parallelism is when two or more events occur at the same time. Concurrency is when two or more events occur at the same time interval.
  2. Explanation 2: Parallelism is multiple events on different entities, and concurrency is multiple events on the same entity.
  3. Explanation 3: Multiple tasks “simultaneously” on one processor, multiple tasks on multiple processors at the same time. Such as Hadoop distributed cluster

So the goal of concurrent programming is to make full use of every core of the processor to achieve maximum processing performance.

Concurrency is mainly for the server. The key to concurrency is whether user operations affect the server. So events that interact with the server at the same time interval.

 

Oracle is different from mysql

1. Concurrency

Mysql: Mysql uses table-level locks to lock resources in a large granularity. If a session locks a table for a long time, other sessions cannot update the data in the table.

Although InnoDB engine tables can use row-level locking, the row-level locking mechanism depends on the index of the table. If the table does not have an index, or SQL statements do not use indexes, table locking is still used. Oracle: Oracle uses row-level locking. The granularity of resource locking is much smaller. Only the resources required by SQL are locked, and the locks are placed on rows in the database, independent of indexes. So Oracle supports concurrency much better.

 

Supplement:

Page level: engine BDB. Table level: engine MyISAM, which locks the entire table and can read and write simultaneously. Row level: engine INNODB, which locks a single row of records

At table level, the entire table is locked so that no other process can write to the table while you are locked. If you write a lock, other processes do not allow row-level reading, and only the specified records can be locked, so that other processes can still operate on other records in the same table. Page-level and table-level locks are fast but have many conflicts, while row-level locks have few but slow conflicts. So a compromise page level is taken, locking adjacent sets of records at a time.

Locking is the mechanism by which the computer coordinates multiple processes or threads to access a certain resource concurrently. The locking mechanism of different databases is almost the same. Because database resources are shared by many users, how to ensure the consistency and effectiveness of concurrent access is a problem that all databases must solve. Lock conflict is also an important factor that affects the performance of concurrent access. Understanding the locking mechanism can not only make us more effective development and utilization of database resources, but also enable us to better maintain the database, thus improving the performance of the database.

 

The characteristics of the above three locks can be summarized as follows: 1) table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low. 2) Row-level locking: high overhead and slow locking; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest. 3) page lock: the cost and lock time are between table lock and row lock; Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.

Each of the three locks has its own characteristics. From the perspective of locking, table-level locking is more suitable for the applications that mainly focus on query and only update data according to index conditions, such as WEB applications. Row-level locking is more suitable for applications with a large number of concurrent updates of a small amount of different data based on index conditions and concurrent queries, such as some online transaction processing (OLTP) systems.

 

 

MySQL table level lock has two modes:

Table Read Lock and Table Write Lock.

That is, when the MyISAM table is read, it will not block other users to the same table read requests, but will block the same table write operations;

Write operations on MyISAM tables will block other users’ read and write operations on the same table.

 

InnoDB has two modes of row locking:

1) Shared lock: allows one transaction to read a row, preventing other transactions from acquiring an exclusive lock on the same data set. ( Select * from table_name where …… lock in share mode)

2) Exclusive lock: Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks of the same data set. (select * from table_name where….. for update)

 

 

Row-level locks do not exist independently. When a transaction acquires row-level locks on some rows, the transaction also acquires table-level locks on the table to which the row belongs, because table-level locks prevent concurrent DDL operations in the system from being affected by concurrent DDL operations in the current transaction.

 

 

 

Oracle: Oracle supports the Serializable isolation level, which can achieve the highest level of read consistency. Only after each session commits can other sessions see the committed changes. Oracle implements read consistency by creating multi-version blocks in the Undo tablespace. For each session query, if the corresponding block changes, Oracle will create the old block in the Undo tablespace for that session. Mysql: mysql does not have a mechanism similar to Oracle for constructing multiple versions of data blocks, and only supports the read Commited isolation level. When one session reads data, other sessions cannot change the data, but they can insert data at the end of the table. When a session updates data, an exclusive lock is added to prevent other sessions from accessing the data. Transactions Oracle has fully supported transactions for a long time. Mysql supports transactions only in the case of innoDB storage engine row-level locks. Data persistence Oracle guarantees that the submitted data can be recovered because oracle writes the submitted SQL operation line to the online log file and keeps it on disk. If the database or host is restarted abnormally, Oracle can use the online log to restore the submitted data. Mysql: Commit SQL statements by default, but may lose data if db or host restart occurs during update. By default, oracle does not automatically submit data. You need to manually submit data. Mysql commits automatically by default. Logical Backup Data is not locked during logical backup and is backed up consistently. Data must be locked during logical backup of mysql to ensure consistency of backup data, which affects DML usage of services. Hot backup Oracle has a mature hot backup tool, RMAN, hot backup does not affect the use of the database. Even if the databases backed up are inconsistent, a consistent reply can be made at recovery time through archived logs and online redo logs. Mysql: when the mysqlHostCopy engine of myISam is used for hot backup, read locks must be added to tables, affecting DML operations. Innodb engine, which backs up innoDB tables and indexes, but not.FRM files. When using ibBackup, there is a log file to record the data changes during the backup, so you do not need to lock the table and do not affect other users to use the database. But this tool is for a fee. Innobackup is a script used in conjunction with ibBackup, which assists in backing up. FRM files. Mysql has many useful and convenient extensions for SQL statements, such as limit, insert, select, and select. Oracle feels a little more sedate in this regard. 9. Oracle replication: Both push and pull traditional data replication and Dataguard two-machine or multi-machine DISASTER recovery. If the primary database fails, the secondary database can be switched to the primary database automatically, but the configuration management is complicated. Mysql: The replication server configuration is simple, but when the primary database fails, the cluster database may lose some data. Manually switch the cluster library to the master library. Oracle has a variety of mature performance diagnostic tuning tools that can implement many automatic analysis and diagnosis functions. For example, awR, ADDM, SQLTrace, tkProof and other mysql diagnostic tuning methods are few, mainly slow query log. Mysql users are related to hosts, which makes no sense. In addition, hosts and IP are more likely to be counterfeited. Oracle’s concept of permissions and security is traditional and well-behaved. Partitioned tables and indexes Oracle’s partitioned tables and indexes are mature and can improve the user experience of accessing DB. Mysql’s partition table is not yet mature and stable. Oracle has a variety of mature command line, graphical interface, web management tools, as well as many third-party management tools, management is extremely convenient and efficient. Mysql management tools are few, and the installation of management tools under Linux sometimes requires the installation of additional packages (phpMyadmin, etc), which is somewhat complicated.