MySQL select for update 加锁

背景

当多人操作同一个客户下账号的时候，希望顺序执行，某个时刻只有一个人在操作；当然可以通过引入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 = utf8create 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
fromjiankunking_account
inner join jiankunking_account_customer onjiankunking_account.id = jiankunking_account_customer.account_id
wherejiankunking_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 transactionat 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 transactionat 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 00: 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