Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ The need to avoid a gap of InnoDB lock     - printf PHP string operations () built-in function usage (Programming)

- CentOS 7 How to connect to a wireless network (Linux)

- How to create a bootable USB disk to use MultiSystem on Ubuntu (Linux)

- ORA-30926 and MERGE tables empty the temporary occupation problem (Database)

- To install and deploy Java applications under CentOS 6.5 (Linux)

- See how --nand flash timing diagram of a read operation Comments (Programming)

- Firewall chapter of Linux server security configuration (Linux)

- JVM garbage collector and memory allocation strategy (Programming)

- After installing Ubuntu 15.04, to do a few things (Linux)

- Ubuntu install virtual machine software VirtualBox 4.3.10 (Linux)

- Github with .gitignore ignore specified file (Linux)

- Ubuntu Gitolite management Git Server code base permissions (Server)

- How x2g0 install Remote Desktop on Linux VPS (Server)

- ApacheDS configuration of users and user groups to achieve SSO (Server)

- Tune in high resolution to 1280x800 in Fedora 14 (Linux)

- C ++ 11 feature: decltype keywords (Programming)

- Hadoop 1 and 2.x installation notes (Server)

- To use yum install VLC under CentOS (Linux)

- Hadoop virtualization performance comparison and tuning experience (Server)

- Use $ BASH ENV variable to mention the right way under Linux (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:      
 
- The multiplexed signal driving IO (Programming)
- Install the free open source financial software GnuCash 2.6.6 under Ubuntu (Linux)
- DataGuard Standby backup error RMAN-06820 ORA-17629 to solve (Database)
- CentOS 6.5 Telnet SecureCRT use management tools (Linux)
- Dom4j change XML coding (Programming)
- Some problems and countermeasures Linux system calls exist (Linux)
- OpenJDK 7 compiled under Ubuntu 14.04.3 64-bit (Linux)
- Cancel Root Command History under Linux (Linux)
- Implement firewall function on a closed Linux machine (Linux)
- Learning UNIX good habits (Linux)
- Linux (CentOS) SSH login without password authentication (Linux)
- SecureCRT 7.0 Log Ubuntu 12.04 server via SSH service under Vmware (Server)
- Development environment to build MEAN In Ubuntu 15.10 (Server)
- Ubuntu 14.04 LTS installed Hadoop 1.2.1 (distributed cluster mode) (Server)
- Apache site default home page settings (Server)
- MYSQL root password for the database user passwords are weak attack analysis (Linux)
- MongoDB collection data migration to MySQL database (Database)
- Linux system using the command line shutdown or restart (Linux)
- Scala REPL Shell call (Programming)
- CentOS 6.6 permanent method to modify the DNS address (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.