月度归档:2013 年十一月

InnoDB锁

共享锁(S)、排他锁(X)见:事务的几个概念

意向共享(IX)、意向排他(IX)

InnoDB提供了非锁定读:不需要等待访问行上的锁释放,读取行的一个快照

InnoDB有三种行锁的算法:

1,Record Lock:单个行记录上的锁。(查询的索引含有唯一属性的时候,Next-Key Lock 会进行优化,将其降级为Record Lock,即仅锁住索引本身,不是范围。)

2,Gap Lock:间隙锁,锁定一个范围,但不包括记录本身。

3,Next-Key Lock:1+2,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。(查询的索引无唯一属性的时候出现,InnoDB存储引擎还会对辅助索引下一个键值加上gap lock),锁定半闭区间;

显式的关闭Gap Lock:

1:把事务隔离级别改成:Read Committed,提交读、不可重复读。SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2:修改参数:innodb_locks_unsafe_for_binlog 设置为1。(当这个参数设置为true的时候,将不会对select的结果集加锁,但是这样的设置将可能带来非常严重的隐患。如果使用这个binlog进行从数据库的恢复,或者进行主数据库的灾难恢复,都将可能和主数据库的执行效果不同。)

InnoDB各隔离级别下的锁(非唯一属性索引上):

create table tb_test(id int,name char(50),key idx_1(id));

select * from tb_test where id=8 for update;
Record Lock Gap Lock Next-Key Lock
REPEATABLE READ
READ COMMITTED
READ UNCOMMITTED
SERIALIZABLE

mysql的innodb锁的其他介绍见:mysql事务隔离机制

参考:Innodb锁机制:Next-Key Lock 浅谈

锁的优化

hibernate的乐观锁和悲观锁

悲观锁:

正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)

在Hibernate使用悲观锁十分容易,但实际应用中悲观锁是很少被使用的,因为它大大限制了并发性:

session.get(class,id,lockMode)

乐观锁:

大多是基于数据版本(Version)记录机制实现。何谓数据版本?即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个”version”字段来实现。

Hibernate为乐观锁提供了3中实现:

1. 基于version

2. 基于timestamp

3. 为遗留项目添加添加乐观锁

基于配置文件:

在class内添加:

<version name="version" column="version" type="integer"></version>
<timestamp name="updateDate" column="updateDate"></timestamp>
<class name="com.xxx" table="xxx" optimistic-lock="all">

基于annotation:

@Version
@Column(name="version",nullable=false,unique=true)
private int version;

参考:

Hibernate事务与并发问题处理(乐观锁与悲观锁)

hibernate annotation 并发操作不冲突

mysql临时表

在sql优化的时候,当有多级子查询等复杂的sql时,建议先生成临时表,再做数据查询,

mysql的临时表建立:

1,创建临时表:

CREATE temporary table tmp_test (
      name varchar(10),
      value int
  )

2,直接将查询结果导入临时表

3,另外还可以在内存中建立临时表:
1CREATE TEMPORARY TABLE tmp_table (
 name VARCHAR(10) NOT NULL,
 value INTEGER NOT NULL
 ) TYPE = HEAP

但是我开始时测试不成功,提示 TYPE=HEAP 语法错误;type改为
engine='heap'
后测试通过;这个是mysql的版本问题,在mysql5.1以后使用engine,type仍然在MySQL 5.1中被支持,现在engine是首选的术语。

engine=‘heap’ 是将存储引擎设置为:memory存储引擎,提供“内存中”表,这样速度更快

查看创建的表的引擎等信息:

show create table tablename;

临时表在一次连接内有效,断开连接数据库自动删除临时表

 

MySQL服务器使用内部临时表

在某些情况下,mysql服务器会自动创建内部临时表。查看查询语句的执行计划,如果extra列显示“using temporary”即使用了内部临时表。内部临时表的创建条件:

*  group by 和 order by中的列不相同

*  order by的列不是引用from 表列表中 的第一表

*  group by的列不是引用from 表列表中 的第一表

*  使用了sql_small_result选项

*  含有distinct 的 order by语句

初始创建内部myisam临时表的条件:

*  表中存在text、blob列

*  在group by中的 列 有超过512字节

*  在distinct查询中的 列 有超过512字节

*  在union、union all联合查询中,select 列 列表中的 列 有超过512字节的

 

参考:高手详解SQL性能优化十条经验

MySQL临时表

mysql存储引擎和表类型

mysql事务隔离机制

四种事务隔离级别:(写锁/共享锁;读锁/排它锁)

1. 未提交读(Read uncommitted)。

写操作加写锁,读操作不加锁。禁止第一类丢失更新,但是会出现所有其他数据并发问题。

2.提交读(Read committed)。

写操作加写锁,读操作加读锁。禁止第一类丢失更新和脏读。这是大部分关系数据库的默认 隔离级别。

3.可重复读(Read repeatable)。, 对于读操作加读锁到事务结束,其他事务的更新操作只能等到事务结束之后进行。和提交 读的区别在于,

提交读的读操作是加读锁到本次读操作结束,可重复读的锁粒度更大。禁止两类丢失更新,禁止脏读和不可 重复度,但是可能出现幻读.

4.序列化(Serializable)。

读操作加表级读锁至事务结束。可以禁止幻读。会导致大量超时和锁竞争现象

mysql设置隔离级别:

1.全局修改,修改mysql.ini配置文件,在最后加上
1 #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
2 [mysqld]
3 transaction-isolation = REPEATABLE-READ
这里全局默认是REPEATABLE-READ,其实MySQL本来默认也是这个级别

命令修改:

set global transaction solation level repeatable read;

2.对当前session修改,在登录mysql客户端后,执行命令:

set session transaction solation level repeatable read;

查看当前隔离级别:

select @@global.tx_isolation;

或者:

select @@tx_isolation;

mysql加锁:关于锁的描述见:数据库的几个概念

1,加共享锁(s锁):

select * from tb_test where id=9 lock in share mode

2,加排它锁(x锁):

select * from tb_test where id=1 for update;

当指定了明确的id时候,会加行锁;

当没有指定明确id时,将会加表锁,如:

锁状态监控:
1
mysql&amp;gt; show status like 'Innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 665452 |
| Innodb_row_lock_time_avg | 24646 |
| Innodb_row_lock_time_max | 51839 |
| Innodb_row_lock_waits | 27 |
+-------------------------------+--------+

解释如下:
Innodb_row_lock_current_waits:当前等待锁的数量
Innodb_row_lock_time:系统启动到现在、锁定的总时间长度
Innodb_row_lock_time_avg:每次平均锁定的时间
Innodb_row_lock_time_max:最长一次锁定时间
Innodb_row_lock_waits:系统启动到现在、总共锁定次数

参考:MySQL数据库事务隔离级别(Transaction Isolation Level)

MySQL InnoDB 锁机制详解