mysql 学习

表级锁
开销小，加锁快，不会出现死锁。锁冲突概率高，并发度低

行级锁
开销大，加锁慢；会出现死锁，锁冲突概率低，并发度高。

页级锁
开销和加锁时间介于上面两种之间。会出现死锁。

myisam 只支持表级锁。

show status like 'table%';
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 43    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)
Table_locks_waited   表级锁的争用情况

表锁的两种情况：表共享读锁，表独占写锁。
读锁不会阻塞对同一表的读请求。但是会阻塞对同一表的写请求。
写锁会阻塞对同一表的读和写的请求。
lock table boss_info write|read;获得boss_info表的写|读锁
lock table boss_info read local 支持锁定表后，其他用的并发写入。
unlock tables; 解锁

从上面可看出，myisam读写串行的。一定条件下myisam支持查询和写入的并行。
系统变量concurrent_insert
0 不允许并行插入
1 表中间没有被删除的空洞时，允许并行插入
2 不论表中有有无空洞，都允许并行插入

可以将concurrent_insert设置为2，并定时进行optimize table整理空间碎片。

myisam读写串行。在同时有读写锁定请求时，先获取到写锁。所以myisam不适合做大量更新操作和查询操作应用的
原因。大量的更新操作会造成查询操作的阻塞。不过可以通过设置来条件myisam锁调度。
set low_priority_updates = 1 降低更新请求的优先级。
set low_priority_inserts = 1
set low_priority_deletes = 1
set low_priority_selects = 1

innodb的锁问题

更新丢失: 最后的事务更新的覆盖了其他事务的更新。
脏读：一个事务更新数据，还未提交；其他事务读取同一条记录。update select
不可重复读： 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，发现数据发生了更新。select delete select
幻读：一个事务根据条件读取某些数据后，重新按照条件读取以前检索过的数据，其他事务插入了满足查询条件的
新数据。select insert select

如何解决并发事务带来的问题：
1、读取数据前，对其加锁，阻止其他事务对数据进行修改
2、不加锁，生成一个数据请求时间点的一致性数据快照，并用这个快照提供一定级别的一致性读取。
就像提供了同一个数据的多个版本。因此也叫多版本并发控制MVCC或MCC

事务的隔离级别：
未提交读： 可能脏读|不可重复读|幻读
已提交读： 不会脏读|不可重复读|幻读
可重复读： 不会脏读|可重复读|幻读
可序列化： 不会脏读|可重复读|不会产生幻读

show status like 'innodb_row_lock%';
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)
如果Innodb_row_lock_waits和Innodb_row_lock_time_avg比较高，则锁争用情况比较严重；

可以设置InnoDB Monitors进一步观察发生锁冲突的表、数据行等，并分析争用原因
create table innodb_monitor (a int) engine=innodb;
show innodb status
drop table innodb_monitor;

打开监视器后，平均15秒会向日志中记录监控的内容，如果长时间打开监控，会导致.err文件
变得非常巨大。

innodb 行锁
共享锁：允许一个事务读取一行，阻止其他事务获得相同数据集的排他锁。
排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

innodb 表锁：
意向共享锁：事务在给数据行加行共享锁前必须获得表的意向共享锁
意向排他锁：事务在给数据行加行排他锁前，必须获得表的意向排他锁。

获得共享锁：
select * from table where ... in share mode;
获得排他锁：
select * from table where ... for update;

innodb行锁是通过给索引上的索引项加锁来实现的。oracle是通过对数据块中的数据行加锁来实现的。
所以，只有通过索引条件检索数据时，innodb才会使用行级锁。否则将会使用表锁。

1、使用相同索引，虽然是对不同记录行操作，也会出现锁冲突。
2、表中有多个索引时，不同事务可以使用不同的索引锁定不同的行。
3、索引字段的类型与参数的类型是否一致，是否会进行类型转换。