MYSQL数据库重点：事务与锁机制

一、事务

一组连续的数据库操作，每一次操作都成功，整个事务就成功，只要有一步出错，整个事务就失败；

MySQL事务与存储引擎相关
　　1.MyISAM：不支持事务，用于只读程序提高性能
　　2.InnoDB：支持ACID事务、行级锁、并发
　　3.Berkeley DB：支持事务

 

mysql事务相关操作：

首先设置是否自动提交：

SET AUTOCOMMIT = 1  自动提交，每一条sql都是一个单独的事务；

SET AUTOCOMMIT = 0  非自动提交，则此后的sql组成一个事务直到出现COMMIT或者ROLLBACK

其次 明确开始一个事务：使用BEGIN或者  start transaction

第三：可以设置保存点：

SAVEPOINT pont1;
ROLLBACK TO SAVEPOINT pont1;

第四：COMMIT提交或者ROLLBACK回滚，同时将事务关闭，如果没有COMMIT直接掉线则之前的操作将无效

 

二、锁机制

1、锁

　　锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下：
　　　　　开销   加锁速度   死锁            粒度                并发性能
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

2、合理利用锁机制优化MySQL

MyISAM 表锁优化建议
由于锁定的颗粒度大，从而在较大程度上会降低并发处理能力。
1、缩短锁定时间
使用简单高效SQL、建立足够高效索引、控制字段类型
2、分离能并行的操作
MyISAM的存储引擎还有ConcurrentInsert（并发插入）的特性。
MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert，可以设置为0，1或者2。三个值的具体说明如下：
concurrent_insert=2，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行ConcurrentInsert;
concurrent_insert=1，当MyISAM存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行ConcurrentInsert;
concurrent_insert=0，无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许ConcurrentInsert。
3、合理利用读写优先级
mySQL的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。所以，如果我们可以根据各自系统环境的差异决定读与写的优先级。如果我们的系统是一个以读为主，而且要优先保证查询性能的话，我们可以通过设置系统参数选项low_priority_updates=1，将写的优先级设置为比读的优先级低，即可让告诉MySQL尽量先处理读请求。当然，如果我们的系统需要优先保证数据写入的性能的话，则可以不用设置low_priority_updates参数了。
这里我们完全可以利用这个特性，将concurrent_insert参数设置为1，甚至如果数据被删除的可能性很小的时候，如果对暂时性的浪费少量空间并不是特别的在乎的话，将concurrent_insert参数设置为2都可以尝试。当然，数据文件中间留有空域空间，在浪费空间的时候，还会造成在查询的时候需要读取更多的数据，所以如果删除量不是很小的话，还是建议将concurrent_insert设置为1更为合适。

Innodb 行锁优化建议
尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；
合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本；

 

参考：

http://blog.csdn.net/xifeijian/article/details/20313977   mysql锁详解

http://www.cnblogs.com/ggjucheng/archive/2012/11/14/2770445.html   mysql数据库锁定机制