Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ The need to avoid a gap of InnoDB lock     - Git uses Details (Linux)

- Zabbix monitors Nginx status (Server)

- MySQL Tutorial: Some optimized for I / O memory areas (Database)

- Ubuntu 14.04 compile and install Apache (Server)

- Python image processing library (PIL) to install and simple to use (Linux)

- Run two MySQL service on one server (Database)

- Sorting algorithm of dichotomy (binary) insertion sort algorithm (Programming)

- HDFS Hadoop Distributed File System Works (Server)

- Ceph distributed storage system is installed on a CentOS 7.1 (Server)

- Ubuntu 15.04 using the Eclipse 4.4, Java 8 and WTP (Linux)

- JavaScript is implemented without new keywords constructor (Programming)

- ctop: monitor container performance Linux command line artifact (Linux)

- True and false in Perl (Programming)

- Linux system security settings after installation (Linux)

- Performance issues under CentOS 6.5 VLAN devices (Linux)

- Ubuntu 14.04 after the restart the default maximum screen brightness solutions (Linux)

- Lua4.0 interpreter documents (Programming)

- Security Knowledge: How to hide a backdoor PHP file tips (Linux)

- cursor_sharing induced error ORA-00600 (Database)

- Boost notes --Asio - (1) a simple small example of synchronous communication (Programming)

 
         
  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:      
 
- How to network to share files between Windows, MAC and Linux (Linux)
- Ubuntu under shadowsocks configuration instructions (Linux)
- CentOS7 install and configure Nagios (Server)
- Linux software firewall ACL match point optimization (Linux)
- C language keywords Comments (Programming)
- Linux environment installation of rvm and ruby (Linux)
- ASM required disk format process in Solaris platforms (Linux)
- Composition and dynamic memory allocation C program (Programming)
- Java singleton mode (Singleton mode) (Programming)
- apt-get and apt-cache show command examples (Linux)
- The Java utility, JavaMail (Programming)
- Ubuntu Server (Ubuntu 14.04 LTS 64-bit) installation libgdiplus 2.10.9 error solution (Linux)
- CentOS 5.10 installed Oracle 11G R2 (Database)
- MySQL fuzzy query: LIKE and REGEXP pattern mode (Database)
- iOS used in the development --UITabBarController tag controller (Programming)
- JavaScript function closures Quick Start (Programming)
- Ubuntu System Log Configuration / var / log / messages (Linux)
- How to forcibly change the Linux system password on a regular basis (Linux)
- Installation and Configuration Munin monitoring server on Linux (Server)
- Use the command line MySQL database backup and recovery (Database)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.