------------------------
LATEST DETECTED DEADLOCK
------------------------
Database must always be consistent and ensure integrity to increase the level of confidence in the data holdings. The main database is critical because several processes and programs depends on this database.
The database is one of the organization core data and business processes are dependent on this databases
Many days and nights were spent reviewing logs, events, procedures, and queries. Studying Transactions, Storage Engines and the Binary Logs, Query Performance.
The occurence of deadlock on insert is bugging us , yes deadlock on insert can occur is called gap deadlock.
It is possible to cause deadlocks in mysql (Innodb) on concurrent insert statements, without there being any transactions in progress. Deadlocks are possible even when the inserts don't collide on any key.
The deadlocks occur due to gap locking done by mysql. There are several reasons for gap locking, and in this particular case, it has to do with preserving a unique key constraint on an index. The situation presents itself to us this way: There is a unique key constraint on a column and we are doing an insert. Mysql has to make sure that the lock it takes is sufficient to prevent another concurrent insert from adding a record with the same key, thus breaking the unique key constraint.
To address the problem of Gap Deadlock we have to adjust the isolation level to Read Committed .
The SQL standard defines four isolation levels, as follows:
1. Read Uncommitted
At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is seldom used in practical applications, because its performance is not much better than other levels. Reading uncommitted data is also called Dirty Read.
2. Read Committed
This is the default isolation level for most database systems (but not MySQL). It satisfies the simple definition of isolation: a transaction can only see changes made by a committed transaction. This isolation level also supports the so-called Nonrepeatable Read, because other instances of the same transaction may have new commit during the processing of the instance, so the same select may return different results.
3. Repeatable Read
This is MySQL's default transaction isolation level, which ensures that multiple instances of the same transaction will see the same data row when reading data concurrently. In theory, however, this leads to another thorny problem: Phantom Read. Simply put, hallucination refers to when a user reads a range of data rows, another transaction inserts new rows in the range, and when the user reads the range of data rows, new "hallucination" rows will be found. InnoDB and Falcon storage engines solve this problem through MVCC (Multiversion Concurrency Control) mechanism.
4. Serializable
This is the highest isolation level, which solves the hallucination problem by forcing transaction sorting to make it impossible to conflict with each other. In short, it adds a shared lock to each read data row. At this level, it may lead to a large number of timeouts and lock competition.
Here is how we do the Mariadb Setting for the isolation level
SET [GLOBAL | SESSION] TRANSACTION
transaction_property [, transaction_property] ...
transaction_property:
ISOLATION LEVEL level
| READ WRITE
| READ ONLY
level:
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
Isolation Level
To set the global default isolation level at server startup, use the --transaction-isolation=level option on the command line or in an option file. Values of level for this option use dashes rather than spaces, so the allowable values are READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE. For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mysqld] section of an option file:
[mysqld]
transaction-isolation = READ-COMMITTED
To determine the global and session transaction isolation levels at runtime, check the value of the tx_isolation system variable:
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
#Default isolation level
#set @@session.tx_isolation='read-uncommitted';
set @@session.tx_isolation='read-committed';
SET GLOBAL TRANSACTION ISOLATION LEVEL READ-COMMITTED
select @@session.tx_isolation;
transaction-isolation = READ-COMMITTED
[mysqld]
transaction-isolation = READ-COMMITTED
To determine the global and session transaction isolation levels at runtime, check the value of the tx_isolation system variable:
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
#Default isolation level
#set @@session.tx_isolation='read-uncommitted';
set @@session.tx_isolation='read-committed';
SET GLOBAL TRANSACTION ISOLATION LEVEL READ-COMMITTED
select @@session.tx_isolation;
transaction-isolation = READ-COMMITTED
References:
https://mariadb.com/kb/en/set-transaction/
