事务与锁表

手工锁表、释放锁

• lock table table_name read/write
• unlock table

锁的类型

表锁

• show status like 'table%'查看表锁的竞争情况
  • Table_locks_waited 表示表级锁的争用情况

行锁

页面锁

myisam 锁机制

myisam 更新的sql语句执行优先级优于查询语句，一旦大量的更新操作就会阻塞表，导致死锁。锁myisam引擎不适合大量更新的表。

调节myisam调度机制

• 通过启动参数设定 low-priority-updates
• 命令行： set LOW_PRIORITY_UPDATES = 1
• sql语句中指定 insert update delete low_priority 属性

辅助机制

通过设置max_write_lock_count设置合适的值避免一直查询不到数据

innodb 锁机制

innodb行锁是通过给索引上的索引项加锁来实现，只有通过索引条件检索数据，innodb才使用行级锁，否则使用表锁

查看innodb行锁竞争情况

• show status like 'innodb_row_lock%' InnoDB_row_lock_waits和我InnoDB_row_lock_avg的值比较高，锁竞争严重

手动在sql语句中指定锁

• 共享锁 select * from tbl_name where ... lock in share mode
• 排他锁 select * from tbl_name where ... for update

innodb行锁使用注意事项

• 再不通过索引条件查询时，innodb使用的是表锁并非行锁
• 多列索引时，如果使用相同的索引键（即同时使用索引1的同一行记录），会出现索引冲突
• 索引是否会被使用，取决于mysql的执行计划，如果小表可能全表扫描比索引更快
• 尽量减少使用范围的条件

non-deterministic 不确定的sql

两种方式都会对oldtab 增加间隙阻止更oldtab数据

• insert into newtab select * form oldtab
• create newtab select * from oldtab
  使用这两种方式创建表时要注意，oldtab是否有在使用， 是否能让其他请求等待时间

相关变量

• innodb_lock_wait_timeout innodb锁等待超时时间

事务

1. 开启事务：start transaction | begin
2. 释放事务：

• commit and release / chain; release 提交事务，并释放事务; chain 提交并开启同一性质的事务
• rollback and release / chain;

3. savapoint test;
4. rollback to test;

小结

对于MyISAM的表锁，主要讨论了以下几点：

• 共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。

• 在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。

• MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。

• 由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，本章主要讨论了以下几项内容。

• InnoDB的行锁是基于锁引实现的，如果不通过索引访问数据，InnoDB会使用表锁。
• 介绍了InnoDB间隙锁（Next-key)机制，以及InnoDB使用间隙锁的原因。
• 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。
• MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。
• 锁冲突甚至死锁很难完全避免。

在了解InnoDB锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁，包括：

• 尽量使用较低的隔离级别；
• 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响；
• 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。