MySql innoDB自增锁解决方案

Question

我有一个项目，其中每秒向一个带有自动递增主键的表中插入大量信息，并使用多线程进行操作，这意味着有许多线程试图向该表中插入新行。由于对于插入查询，该表存在锁定，因此我无法同时执行插入操作，因此无法从线程中获得最大性能...

是否有一种方法可以克服这种锁定？

- kitsuneFox

---

平均每秒插入多少条数据？最大值是多少？你遇到死锁问题的频率有多高？ - Rick James

Answer 1

您可以在my.cnf中将innodb_autoinc_lock_mode设置为1。这样，Auto_increment将不会有锁。如果一个线程回滚，则值可能会存在空洞，具体如下所示：

MariaDB [test]> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

示例1

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into autoinc VALUES(NULL,'hello');
Query OK, 1 row affected (0.01 sec)



thread 2 ------------------>    MariaDB [test]> start transaction;
thread 2 ------------------>    Query OK, 0 rows affected (0.00 sec)

thread 2 ------------------>    MariaDB [test]> insert into autoinc VALUES(NULL,'world');
thread 2 ------------------>    Query OK, 1 row affected (0.00 sec)

thread 2 ------------------>    MariaDB [test]> commit;
thread 2 ------------------>    Query OK, 0 rows affected (0.00 sec)

thread 2 ------------------>    MariaDB [test]>


MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from autoinc;
+----+-------+
| id | d     |
+----+-------+
|  1 | hello |
|  2 | world |
+----+-------+
2 rows in set (0.00 sec)

MariaDB [test]>

带有回滚的示例2

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into autoinc VALUES(NULL,'Guten');
Query OK, 1 row affected (0.00 sec)

thread 2 ------------------>    MariaDB [test]> start transaction;
thread 2 ------------------>    Query OK, 0 rows affected (0.00 sec)

thread 2 ------------------>    MariaDB [test]> insert into autoinc VALUES(NULL,'Tag');
thread 2 ------------------>    Query OK, 1 row affected (0.00 sec)

thread 2 ------------------>    MariaDB [test]> commit;
thread 2 ------------------>    Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from autoinc;
+----+-------+
| id | d     |
+----+-------+
|  1 | hello |
|  2 | world |
|  4 | Tag   |
+----+-------+
3 rows in set (0.00 sec)

MariaDB [test]>

- Bernd Buffen

---

但是，如果我确定一旦插入一行后，除非出现数据库故障（我假设这种情况不应该经常发生），否则我不想回滚，那么将锁定设置为1并且可以假设空洞的数量非常低，这并不是一个非常糟糕的实践，对吗？ - kitsuneFox

正确，但首先查看 show variables like 'innodb_autoinc_lock_mode'; 的结果。默认值为1。请参见：https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_autoinc_lock_mode - Bernd Buffen

我正在使用 MySQL 5.7，出现了以下错误：com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction。 - kitsuneFox

当我在执行插入查询时使用同步代码块（Java代码），该错误不会出现。 - kitsuneFox

"无法获取锁"意味着_还有其他问题_. 请提供更多信息，包括自动提交和BEGIN...COMMIT的使用讨论。 - Rick James

Answer 2

我的案子。

vi /etc/mysql/mysql.conf.d/mysqld.cnf

# innodb_autoinc_lock_mode = 0

systemctl restart mysql

- seunggabi