表读锁

读锁大家都可读，不可写

表写锁

写锁大家不可写不可读

表锁分析

查看当前表锁状态
mysql> show open tables;
+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| mysql              | time_zone_transition_type                    |      0 |           0 |
| performance_schema | events_waits_summary_global_by_event_name    |      0 |           0 |
| performance_schema | setup_timers                                 |      0 |           0 |
| performance_schema | events_waits_history_long                    |      0 |           0 |
| mysql              | time_zone_transition                         |      0 |           0 |
| performance_schema | mutex_instances                              |      0 |           0 |
| performance_schema | events_waits_summary_by_instance             |      0 |           0 |
| mysql              | tables_priv                                  |      0 |           0 |
| mysql              | procs_priv                                   |      0 |           0 |
| mysql              | func                                         |      0 |           0 |
| performance_schema | events_waits_history                         |      0 |           0 |
| mysql              | time_zone_name                               |      0 |           0 |
| mysql              | user                                         |      0 |           0 |
| performance_schema | setup_consumers                              |      0 |           0 |
| performance_schema | file_instances                               |      0 |           0 |
| performance_schema | cond_instances                               |      0 |           0 |
| mysql              | plugin                                       |      0 |           0 |
| mysql              | db                                           |      0 |           0 |
| mysql              | proxies_priv                                 |      0 |           0 |
| mysql              | time_zone                                    |      0 |           0 |
| db1                | user                                         |      0 |           0 |
| performance_schema | events_waits_current                         |      0 |           0 |
| mysql              | event                                        |      0 |           0 |
| mysql              | columns_priv                                 |      0 |           0 |
| performance_schema | performance_timers                           |      0 |           0 |
| performance_schema | threads                                      |      0 |           0 |
| mysql              | host                                         |      0 |           0 |
| performance_schema | events_waits_summary_by_thread_by_event_name |      0 |           0 |
| performance_schema | file_summary_by_event_name                   |      0 |           0 |
| mysql              | time_zone_leap_second                        |      0 |           0 |
| db1                | student                                      |      0 |           0 |
| performance_schema | setup_instruments                            |      0 |           0 |
| mysql              | servers                                      |      0 |           0 |
| performance_schema | file_summary_by_instance                     |      0 |           0 |
| performance_schema | rwlock_instances                             |      0 |           0 |
+--------------------+----------------------------------------------+--------+-------------+
35 rows in set (0.00 sec)

查看数据库中表锁竞争状态

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 62    |
| Table_locks_waited    | 1     |
+-----------------------+-------+
2 rows in set (0.00 sec)

Table_locks_immediate：能够立即获得表级锁的锁请求次数（查询|更新都会++）

Table_locks_waited：不能立即获取表级锁而需要等待的锁请求次数（写入操作被表锁阻塞此项++），此项数据大，表示被阻塞的更新表次数多，系统需优化。

事务

1. 事务及其属性ACID

   1. A：原子性
   2. C：一致性
   3. I：  隔离性
   4. D：持久性

2. 并发带来的问题

   1. 更新丢失，覆盖
   2. 脏读，读取了未提交的数据
   3. 不可重复读，再次读取发现数据被修改了（删除|修改）
   4. 幻读，再次读取读取了其他事务已提交的数据（新增）

3. 事务的隔离级别

   1. R_UNC    级别最低一般不用           脏、不、幻
   2. R_C         语句级                             不、幻
   3. R_R         事务级                             幻
   4. S              序列化事务级                  /

行锁

1. 行锁

2. 索引失效导致行锁变表锁（查询条件字符型输入为数字型索引失效，当其他客户端更新其他行数据会阻塞）

3. 间隙锁危害（范围查找索引字段更新数据，默认范围内索引字段行全部加索，当有其他事务插入此范围内索引字段刚好不存在的数据时候就会出现表锁）

4. 手动加一行锁

   mysql> begin;
   Query OK, 0 rows affected (0.00 sec)
   -- 执行完此命令行被锁住，除了加索客户端任何人无法操作此行数据，直至提交。
   mysql> select * from user where id = 199984 for update;
   
   mysql> update user set name = 'zs' where id = 199984;
   Query OK, 1 row affected (0.10 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   mysql> commit;
   Query OK, 0 rows affected (0.01 sec)
   

    

5. 行锁分析

   mysql> show status like 'innodb_row_lock%';
   +-------------------------------+--------+
   | Variable_name                 | Value  |
   +-------------------------------+--------+
   | Innodb_row_lock_current_waits | 0      |
   | Innodb_row_lock_time          | 182481 |
   | Innodb_row_lock_time_avg      | 45620  |
   | Innodb_row_lock_time_max      | 51220  |
   | Innodb_row_lock_waits         | 4      |
   +-------------------------------+--------+
   5 rows in set (0.00 sec)
   

   对于各个状态说明如下：

   Innodb_row_lock_current_waits:当前正在等待锁的数量；

   Innodb_row_lock_time:从系统启动到现在锁定总时间长度；

   Innodb_row_lock_time_avg：每次等待所花平均时间；

   Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间长度；

   Innodb_row_lock_waits:系统启动到现在总共等待的次数；

   对于这5个状态变量，比较重要的是：

   Innodb_row_lock_time_avg，Innodb_row_lock_waits，Innodb_row_lock_time。

   尤其是当等待次数很高，而且每次等待时长也很大的时候，我们就要分析系统中为什么有这么多的等待，然后根据分析结果来制定优化。

   此时就要用show prifile；进行分析；分析整个sql的生命周期；