[Mysql 行锁-表锁] 以及show profile分析sql

表锁:(偏读)

• 
  

当表 上锁以后,其他客户会话就不能访问 ,就会产生阻塞,除非解锁

[如何分析表锁定]:

• 可以通过检查table_locks_waited 和 table_locas_immediate状态变量来分析系统上的表锁定;
• SQL: show status like ‘table%’
  图例:
  
  图中有二个状态变量来记录Mysql内部表级锁定的情况, 如下:
• table_locas_immediate:产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取值加1;
• table_locks_waited :出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次锁值加1);此值高则说明存在着较严重的表级锁争用情况;

此外:

Myisam的读写锁调度是 写 优先,这也是myisam不适合作为主表引擎;因为写锁以后,其他线程不能做任何的操作,大量的更新会使查询很难得到锁,从而造成永远的阻塞. table_locks_waited 值越高存在着严重锁竞争情况.

行锁:(偏写)

特点:偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;
锁定粒度小,发生锁冲突概率最低,并发度也最高.

事务(Transaction) 和 ACID 属性:

并发事务处理带来的问题:

• 更新丢失

• 

• 脏读

• 

• 不可重复读

• 

• 幻读

• 

---

Mysql事务隔离级别来解决并发出现的问题:

• 

查看Mysql当前数据库的事务隔离级别默认级别:

数据不能出现脏读,不可重复读,但是可以出现幻读;

测试建表sql;

• 

数据库版本5.5以后大多数默认InnoDB是数据库的引擎.

• 行锁基本演示:
  有了索引,就自动成为行锁;默认行锁

• set autocommit=1;开启自动提交

  set autocommit=0;关闭自动提交 需要手动 commit 提交

---

读己之所写:
查询只能查到自己修改的值,

图中我们可以发现 当我们把自动提交关闭以后,客户1 ,客户2(session-2)都关闭以后, 我客户1修改数据以后,查询这个表 发现修改成功, 但是另一个 客户查询 发现数据没有更改, 这就是 读己之所写

当二个客户 commit提交以后 客户2就能查看客户1修改的数据了 :

在二个客户修改同一个数据的时候,必须要commit,不然不能更新, 如果客户1先更新了 没有commit,客户二就会堵塞, 直到客户1commit 以后 客户二才能 修改成功并且 需要coomit提交 才能覆盖 客户1修改的值

---

[如何分析行锁定]:
通过检查InnodDB_row_lock状态变量 来分析系统上的行锁争夺情况;
mysql>show status like’innodb_row_lock%’;

1. 
   
   
   show profile;

2. 使用show profiles分析SQL性能 Show
   profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

3. 查看数据库版本 mysql> select version();

   profile默认是不打开的 mysql> show profiles; Empty set (0.02 sec)

   验证修改后的结果 mysql> show variables like “%pro%”; 可以看到profiling 默认是OFF的。

   开启profile，然后测试 开启profile mysql> set profiling=1;

   获取profile的帮助 help profile;

---

如何锁定一行数据,防止串改;
select * from test_innodb_lock where 条件 for update;
这个是锁定这一行数据;比如你在线上环境发现一条数据有误,想要修改,但是要防止别人修改,这时候 你可以先锁定, 锁定以后 你没有commit 其他人 就会堵塞.