相亲网站拉人做基金,建设银行兴安支行网站,网站的建设与运营,商丘做网站推广的公司背景
当多人操作同一个客户下账号的时候#xff0c;希望顺序执行#xff0c;某个时刻只有一个人在操作#xff1b;当然可以通过引入redis这种中间件实现#xff0c;但考虑到并发不会很多#xff0c;所以不想再引入别的中间件。
表结构
create table jiankunking_accoun…背景
当多人操作同一个客户下账号的时候希望顺序执行某个时刻只有一个人在操作当然可以通过引入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 id2;//删除这种情况也会夯住
// 这里操作 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: /* ApplicationNameDBeaver 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: /* ApplicationNameDBeaver 24.0.5 - SQLEditor Script-8.sql */ delete from jiankunking_account where id2,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
/* ApplicationNameDBeaver 24.0.5 - SQLEditor Script-8.sql */ update jiankunking_account set last_login_at now() where id2
------- 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.htmlhttps://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-trx-table.html
