Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ The need to avoid a gap of InnoDB lock     - Three easy open source firewall on the Linux platform (Linux)

- Oracle 10g after the failure to start the upgrade (Oracle instance terminated. Disconnection forced) (Database)

- Docker: Mirroring and container operations (Server)

- Linux startup and logon security settings (Linux)

- 20 Top Linux commands (Linux)

- Linux security configuration (Linux)

- Linux alpine use the command line to access Gmail (Linux)

- Java inheritance initialization problem (Programming)

- Ubuntu 14.04 LTS compiler installation R Source Code (Linux)

- Ubuntu under VirtualBox virtual machine serial port settings (Linux)

- ORA-14400: inserted partition key does not map to any partition (Database)

- Swift rewrite initialize method of navigation controller class (Programming)

- To install the latest version of the EPEL on CentOS 5.x or 6.x (Linux)

- Linux common network tools: batch scanning of nmap hosting service (Linux)

- HAproxy let IP recording back-end RS (Server)

- Java memory analysis tool uses detailed MAT (Programming)

- CentOS7 installation hardware monitoring for Zabbix enterprise applications (Server)

- The difference between vi and nano (Linux)

- Command filter MySQL slow query log (Database)

- Install Java 8 on Ubuntu using PPA (Linux)

 
         
  The need to avoid a gap of InnoDB lock
     
  Add Date : 2018-11-21      
         
       
         
  When a client exclude deadlock problem I encountered an interesting situation include InnoDB gap locking. For a WHERE clause does not match non-intrusive write any row, I expect that the transaction should not have a lock, but I was wrong. Let's look at this table and examples UPDATE.

mysql> SHOW CREATE TABLE preferences G
*************************** 1. row ******************** *******
      Table: preferences
Create Table: CREATE TABLE `preferences` (
  `NumericId` int (10) unsigned NOT NULL,
  `ReceiveNotifications` tinyint (1) DEFAULT NULL,
  PRIMARY KEY ( `numericId`)
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT (*) FROM preferences;
+ ---------- +
| COUNT (*) |
+ ---------- +
| 0 |
+ ---------- +
1 row in set (0.01 sec)
mysql> UPDATE preferences SET receiveNotifications = '1' WHERE numericId = '2';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

InnoDB status display UPDATE on the main index recorded an X lock held:

--- TRANSACTION 4A18101, ACTIVE 12 sec
2 lock struct (s), heap size 376, 1 row lock (s)
MySQL thread id 3, OS thread handle 0x7ff2200cd700, query id 35 localhost msandbox
Trx read view will not see trx with id> = 4A18102, sees <4A18102
TABLE LOCK table `test`.`preferences` trx id 4A18101 lock mode IX
RECORD LOCKS space id 31766 page no 3 n bits 72 index `PRIMARY` of table` test`.`preferences` trx id 4A18101 lock_mode X

This is why, Heikki did explain in its bug report, this makes sense, I know it is very difficult to repair, but slightly disgusted I hope it will be differences in treatment. To complete this article, let me prove deadlock under the above mentioned case, the following is the first session in mysql1, mysql2 is another order of the query is as follows:

mysql1> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql1> UPDATE preferences SET receiveNotifications = '1' WHERE numericId = '1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql2> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql2> UPDATE preferences SET receiveNotifications = '1' WHERE numericId = '2';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql1> INSERT INTO preferences (numericId, receiveNotifications) VALUES ( '1', '1'); - This one goes into LOCK WAIT
mysql2> INSERT INTO preferences (numericId, receiveNotifications) VALUES ( '2', '1');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Now you see how easy it lead to deadlock, so be sure to avoid this - INSERT part if the transaction resulted from a non-intrusive write operations may not match any rows, I do not do this, use REPLACE INTO or READ-COMMITTED transaction isolation.
     
         
       
         
  More:      
 
- Shell array: Define Shell array, the array length (Programming)
- About DataGuard three protected mode test (Database)
- numpy and SciPy installation under Python for scientific computing package (Linux)
- PostgreSQL vacuum principle of a function and parameters (Database)
- Installation salt-minion on RHEL5 (Linux)
- Java interface (Programming)
- Use web2py + uWSGI + Nginx Web server built on Linux (Server)
- Linux installation JDK1.6 rpm.bin assembly (Linux)
- To install Docker under CentOS7 (Linux)
- Systemd on RHEL7 (Linux)
- Zabbix monitors the status of TCP connections (Server)
- Unix average load average load calculation method (Server)
- Lazarus for Raspbian installation (Linux)
- Ubuntu 14.04 running ASP.NET Configuration Mono + Jexus (Server)
- Linux installation is larger than 2TB (UEFI interface) hard disk solution (Linux)
- Linux Network Security Tips Share (Linux)
- Ubuntu 14.04 can be used to create a WIFI hotspot for Android (Linux)
- Java abstract class instantiation (Programming)
- Necessity in Java packages (Programming)
- Extended VMware Ubuntu root partition size (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.