Default transaction isolation level in MySQL 5.6 is REPEATABLE-READ. This setting can cause contention issues and has impact on both read and write queries.

As a good practice you can set the default transaction isolation level to READ-COMMITTED as this is less expensive in terms of performance compared to REPEATABLE-READ.

If you have long running queries, depending on your use case you can set the transaction isolation level to READ UNCOMMITTED (allows dirty reads or uncommitted data).

To check contention issues, please follow these instructions:

mysql> SHOW ENGINE INNODB STATUS;

Output

LATEST DETECTED DEADLOCK
------------------------
2019-07-22 01:25:18 7f37800ec700
*** (1) TRANSACTION:
TRANSACTION 629516266, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 4
MySQL thread id 294750, OS thread handle 0x7f377aed7700, query id 4390211 10.0.1.120 demo update
INSERT INTO Response(
       EntityID,
    EntityType,
    Decision,
    Created,
    OperatorID
)
VALUES(
       p_EntityID,
    p_EntityType,
    p_Decision,
    NOW(),
    p_OperatorID
)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1377765 n bits 104 index `PRIMARY` of table `demo`.`Response` trx id 629516266 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
*** (2) TRANSACTION:
TRANSACTION 629516202, ACTIVE 2 sec fetching rows
mysql tables in use 29, locked 29
3246 lock struct(s), heap size 357928, 293256 row lock(s), undo log entries 6
MySQL thread id 294685, OS thread handle 0x7f37800ec700, query id 4389638 10.0.1.120 demo Sending data
CREATE TEMPORARY TABLE Temp_AppointmentStakeholder
(INDEX IX_Entity(EntityID,EntityType))
 
SELECT DISTINCT
        EntityStakeholder.ID AS ID,
        EntityStakeholder.EntityID AS EntityID,
        EntityStakeholder.EntityType AS EntityType,
        EntityStakeholder.StakeholderType AS StakeholderType,
        EntityStakeholder.StakeholderTypeID AS StakeholderTypeID,
        EntityStakeholder.StakeholderID AS StakeholderID,
        EntityStakeholder.StakeholderRoleID AS StakeholderRoleID,
        StakeholderRole.Title AS StakeholderRole,
        Stakeholder.OrganisationID AS OrganisationID,
        Stakeholder.BusinessID AS BusinessID,
        Business.Name AS BusinessName,
        Organisation.OrganisationName AS OrganisationName,
        EntityStakeholderContact.PersonID AS PersonID,
        CONCAT(Person.GivenNames,
                ' ',
                Person
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1377765 n bits 104 index `PRIMARY` of table `demo`.`Response` trx id 629516202 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1425700 n bits 152 index `PRIMARY` of table `demo`.`Appointment` trx id 629516202 lock mode S waiting
Record lock, heap no 79 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 0: len 4; hex 8000a8af; asc     ;;
 1: len 6; hex 00002585a7ea; asc   %   ;;
 2: len 7; hex a10017003b0110; asc     ;  ;;
 3: len 4; hex 80012e0a; asc   . ;;
 4: len 19; hex 456e74697479576f726b666c6f775374616765; asc EntityWorkflowStage;;
 5: len 0; hex ; asc ;;
 6: len 4; hex 74657374; asc test;;
 7: len 4; hex 80000000; asc     ;;
 8: len 3; hex 8fc6e7; asc    ;;
 9: len 3; hex 800000; asc    ;;
 10: len 3; hex 800000; asc    ;;
 11: len 4; hex 80001a3d; asc    =;;
 12: len 6; hex 416374697665; asc Active;;
 13: len 8; hex 8000125d04d03565; asc    ]  5e;;
 14: SQL NULL;
 15: len 4; hex 8000000f; asc     ;;
 16: len 1; hex 80; asc  ;;
 
*** WE ROLL BACK TRANSACTION (1)

To change the transaction isolation level, please follow these instructions:

mysql>  SELECT @@global.tx_isolation; 
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
|  REPEATABLE-READ      |
+-----------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
mysql>  SELECT @@global.tx_isolation; 
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
|  REPEATABLE-COMMITTED      |
+-----------------------+
1 row in set (0.00 sec)