背景
当多人操作同一个客户下账号的时候，希望顺序执行，某个时刻只有一个人在操作；当然可以通过引入redis这种中间件实现，但考虑到并发不会很多，所以不想再引入别的中间件。
表结构
create table `jiankunking_account` (
`id` bigint(20) not null auto_increment COMMENT '',
`name` varchar(100) not null,
`email` varchar(255) default '' COMMENT '邮箱',
`phone_number` varchar(11) default '' COMMENT '手机号',
`last_login_at` timestamp NULL DEFAULT NULL COMMENT '最后登陆时间',
primary key (`name`),
unique key `id` (`id`),
unique key `account_name` (`name`)
using BTREE,
key `phone_number` (`phone_number`),
key `updated_at` (`updated_at`)
) engine = InnoDB auto_increment = 6786111 default CHARSET = utf8
create table `jiankunking_account_customer` (
`account_id` bigint(20) not null COMMENT '账户id',
`customer_id` varchar(40) not null default '' COMMENT '客户id',
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
primary key (`account_id`,
`customer_id`),
key `account_id` (`account_id`)
using BTREE,
key `customer_id` (`customer_id`)
using BTREE
) engine = InnoDB default CHARSET = utf8
数据库自动提交
先看下数据库自动提交有没有关闭
show variables like 'autocommit' ;
验证SQL
事务一、二 开两个终端或者在DBvear开两个窗口
事务一
START TRANSACTION; // 第一步
select // 第三步
jiankunking_account.id,
jiankunking_account.NAME,
jiankunking_account.phone_number,
jiankunking_account_customer.customer_id
from
jiankunking_account
inner join jiankunking_account_customer on
jiankunking_account.id = jiankunking_account_customer.account_id
where
jiankunking_account_customer.customer_id = '11' for
update;
commit;
事务二
START TRANSACTION;// 第二步
update jiankunking_account set last_login_at =now() where id ='2';//第四步
// delete from jiankunking_account where id='2';//删除这种情况也会夯住
// 这里操作 jiankunking_account_customer表中customer_id = '11'的数据也会被夯住
commit;
两个事务执行顺序按照SQL后面的指定，当指定到第三步的时候，能获取到具体数据
在执行第3步的时候会卡住
等到超时时间后，会提示错误
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1205] [40001]: Lock wait timeout exceeded; try restarting transaction
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:614)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:505)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:527)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:976)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4155)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:194)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5148)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:115)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:124)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:767)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:652)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
... 11 more
锁情况
查询在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
更新
[
{
"trx_id": "322316562",
"trx_state": "LOCK WAIT",
"trx_started": "2024-05-22 18:18:35",
"trx_requested_lock_id": "322316562:267:338:81",
"trx_wait_started": "2024-05-22 18:18:35",
"trx_weight": 2,
"trx_mysql_thread_id": 9612611,
"trx_query": "/* ApplicationName=DBeaver 24.0.5 - SQLEditor <Script-8.sql> */ update jiankunking_account set last_login_at =now() where id ='2'",
"trx_operation_state": "starting index read",
"trx_tables_in_use": 1,
"trx_tables_locked": 1,
"trx_lock_structs": 2,
"trx_lock_memory_bytes": 1136,
"trx_rows_locked": 1,
"trx_rows_modified": 0,
"trx_concurrency_tickets": 0,
"trx_isolation_level": "READ COMMITTED",
"trx_unique_checks": 1,
"trx_foreign_key_checks": 1,
"trx_last_foreign_key_error": null,
"trx_adaptive_hash_latched": 0,
"trx_adaptive_hash_timeout": 0,
"trx_is_read_only": 0,
"trx_autocommit_non_locking": 0
},
{
"trx_id": "322316561",
"trx_state": "RUNNING",
"trx_started": "2024-05-22 18:18:30",
"trx_requested_lock_id": null,
"trx_wait_started": null,
"trx_weight": 20,
"trx_mysql_thread_id": 9612580,
"trx_query": null,
"trx_operation_state": null,
"trx_tables_in_use": 0,
"trx_tables_locked": 2,
"trx_lock_structs": 20,
"trx_lock_memory_bytes": 3520,
"trx_rows_locked": 36,// 注意这里的行数比实际行数大，实际行数应该是18行，jiankunking_account 9行，jiankunking_account_customer9行
"trx_rows_modified": 0,
"trx_concurrency_tickets": 0,
"trx_isolation_level": "READ COMMITTED",
"trx_unique_checks": 1,
"trx_foreign_key_checks": 1,
"trx_last_foreign_key_error": null,
"trx_adaptive_hash_latched": 0,
"trx_adaptive_hash_timeout": 0,
"trx_is_read_only": 0,
"trx_autocommit_non_locking": 0
}
]
删除
[
{
"trx_id": "322316782",
"trx_state": "LOCK WAIT",
"trx_started": "2024-05-22 18:22:58",
"trx_requested_lock_id": "322316782:267:338:81",
"trx_wait_started": "2024-05-22 18:22:58",
"trx_weight": 2,
"trx_mysql_thread_id": 9612611,
"trx_query": "/* ApplicationName=DBeaver 24.0.5 - SQLEditor <Script-8.sql> */ delete from jiankunking_account where id='2'",
"trx_operation_state": "starting index read",
"trx_tables_in_use": 1,
"trx_tables_locked": 1,
"trx_lock_structs": 2,
"trx_lock_memory_bytes": 1136,
"trx_rows_locked": 1,
"trx_rows_modified": 0,
"trx_concurrency_tickets": 0,
"trx_isolation_level": "READ COMMITTED",
"trx_unique_checks": 1,
"trx_foreign_key_checks": 1,
"trx_last_foreign_key_error": null,
"trx_adaptive_hash_latched": 0,
"trx_adaptive_hash_timeout": 0,
"trx_is_read_only": 0,
"trx_autocommit_non_locking": 0
},
{
"trx_id": "322316781",
"trx_state": "RUNNING",
"trx_started": "2024-05-22 18:22:49",
"trx_requested_lock_id": null,
"trx_wait_started": null,
"trx_weight": 20,
"trx_mysql_thread_id": 9612580,
"trx_query": null,
"trx_operation_state": null,
"trx_tables_in_use": 0,
"trx_tables_locked": 2,
"trx_lock_structs": 20,
"trx_lock_memory_bytes": 3520,
"trx_rows_locked": 36,// 注意这里的行数比实际行数大，实际行数应该是18行，jiankunking_account 9行，jiankunking_account_customer9行
"trx_rows_modified": 0,
"trx_concurrency_tickets": 0,
"trx_isolation_level": "READ COMMITTED",
"trx_unique_checks": 1,
"trx_foreign_key_checks": 1,
"trx_last_foreign_key_error": null,
"trx_adaptive_hash_latched": 0,
"trx_adaptive_hash_timeout": 0,
"trx_is_read_only": 0,
"trx_autocommit_non_locking": 0
}
]
那这里的锁到底是什么锁？
SHOW ENGINE INNODB STATUS;
可以看到锁信息如下
---TRANSACTION 322359005, ACTIVE 19 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 9743399, OS thread handle 140157041190656, query id 1442147372 10.192.26.59 jkk updating
/* ApplicationName=DBeaver 24.0.5 - SQLEditor <Script-8.sql> */ update jiankunking_account set last_login_at =now() where id='2'
------- TRX HAS BEEN WAITING 19 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 267 page no 338 n bits 736 index id of table `jkk`.`jiankunking_account` trx id 322359005 lock_mode X locks rec but not gap waiting
Record lock, heap no 81 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80000000009c0fde; asc ;;
1: len 10; hex 38383030303030303031; asc 8800000001;;
------------------
---TRANSACTION 322359002, ACTIVE 23 sec
20 lock struct(s), heap size 3520, 36 row lock(s)
MySQL thread id 9742898, OS thread handle 140156937144064, query id 1442147268 10.192.26.59 jkk
--------
--------
如果jiankunking_account_customer用created_at字段（注意：没有索引）来过滤数据，继续上面的操作，在锁信息中可以看到，还是行锁，并不是网上说的表锁；如果有自己的应用场景还是要按照自己的业务场景验证下。
结论
通过简单的select for update 可以实现在并发不高的情况锁住数据。
官方文档：
- https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
- https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-trx-table.html
