mysql lock && tx.

Lock

https://tool.4xseo.com/a/24067.html

锁分类：

对数据操作的粒度分

1. 表锁：操作时，会锁定整个表
2. 行锁：操作时，会锁定当前行
3. 间隙锁：操作时，锁定范围条件内的记录

对数据的操作类型分

1. 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行不会互相影响

2. 写锁（排他锁，也叫独占锁）：当前操作没有完成之前，会阻塞其他写锁或读锁

在开启事务的且自动提交为关闭的状态下`

select for update 和 update 会创建 排他锁,
如果带有索引查询，则会加行锁，如果查询整个表，则会加间隙锁

lock table

session 1

lock table my_table read;

select * from my_table;
unlock tables;

sessoin 2

select * from my_table;

update my_table set id = 1; -- will blocked until session 1 unlock tables

lock record

session 1

-- session  a
set autocommit = 0;
begin;
select TRX_ID from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID();
select * from stu_teacher where teacher_id = 1 for update;
select TRX_ID from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID();
COMMIT;

session 2

set @@autocommit=0; 
SHOW VARIABLES LIKE 'autocommit';
BEGIN;
select * from stu_teacher where teacher_id = 1 for update;
update stu_teacher set teacher_name = 'test112' where teacher_id = 1;
COMMIT;

session 1 先执行的话 session 2的查询for update 更新会被锁住

Tx

steps

set autocommit = 0;

start transaction -- or begin;

update something set id = 1;

commit;

ISOLATION

READ_UNCOMMITED

READ_COMMITED

REPEATABLE-READ 可重复读，但会幻读（读取到新加入的行）

READ_PHANTOM

serialization

LOCK