MySQL中的隔离级别和悲观锁及乐观锁示例
1,MySQL的事务支持
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关:
- MyISAM:不支持事务,用于只读程序提高性能
- InnoDB:支持ACID事务、行级锁、并发
- Berkeley DB:支持事务
2,隔离级别
隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性
ANSI标准定义了4个隔离级别,MySQL的InnoDB都支持:
Java代码
- READ UNCOMMITTED:最低级别的隔离,通常又称为dirty read,它允许一个事务读取还没commit的数据,这样可能会提高性能,但是dirty read可能不是我们想要的
- READ COMMITTED:在一个事务中只允许已经commit的记录可见,如果session中select还在查询中,另一session此时insert一条记录,则新添加的数据不可见
- REPEATABLE READ:在一个事务开始后,其他session对数据库的修改在本事务中不可见,直到本事务commit或rollback。在一个事务中重复select的结果一样,除非本事务中update数据库。
- SERIALIZABLE:最高级别的隔离,只允许事务串行执行。为了达到此目的,数据库会锁住每行已经读取的记录,其他session不能修改数据直到前一事务结束,事务commit或取消时才释放锁。
可以使用如下语句设置MySQL的session隔离级别:
- 1. SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
MySQL默认的隔离级别是REPEATABLE READ,在设置隔离级别为READ UNCOMMITTED或SERIALIZABLE时要小心,READ UNCOMMITTED会导致数据完整性的严重问题,而SERIALIZABLE会导致性能问题并增加死锁的机率
3,隔离级别
乐观所和悲观锁策略:
悲观锁:在读取数据时锁住那几行,其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所:读取数据时不锁,更新时检查是否数据已经被更新过,如果是则取消当前更新
一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁
悲观锁的例子:
- CREATE PROCEDURE tfer_funds
- (from_account INT, to_account INT,tfer_amount NUMERIC(10,2),
- OUT status INT, OUT message VARCHAR(30))
- BEGIN
- DECLARE from_account_balance NUMERIC(10,2);
- START TRANSACTION;
- SELECT balance
- INTO from_account_balance
- FROM account_balance
- WHERE account_id=from_account
- FOR UPDATE;
- IF from_account_balance>=tfer_amount THEN
- UPDATE account_balance
- SET balance=balance-tfer_amount
- WHERE account_id=from_account;
- UPDATE account_balance
- SET balance=balance+tfer_amount
- WHERE account_id=to_account;
- COMMIT;
- SET status=0;
- SET message='OK';
- ELSE
- ROLLBACK;
- SET status=-1;
- SET message='Insufficient funds';
- END IF;
- END;
乐观锁的例子:
- CREATE PROCEDURE tfer_funds
- (from_account INT, to_account INT, tfer_amount NUMERIC(10,2),
- OUT status INT, OUT message VARCHAR(30) )
- BEGIN
- DECLARE from_account_balance NUMERIC(8,2);
- DECLARE from_account_balance2 NUMERIC(8,2);
- DECLARE from_account_timestamp1 TIMESTAMP;
- DECLARE from_account_timestamp2 TIMESTAMP;
- SELECT account_timestamp,balance
- INTO from_account_timestamp1,from_account_balance
- FROM account_balance
- WHERE account_id=from_account;
- IF (from_account_balance>=tfer_amount) THEN
- -- Here we perform some long running validation that
- -- might take a few minutes */
- CALL long_running_validation(from_account);
- START TRANSACTION;
- -- Make sure the account row has not been updated since
- -- our initial check
- SELECT account_timestamp, balance
- INTO from_account_timestamp2,from_account_balance2
- FROM account_balance
- WHERE account_id=from_account
- FOR UPDATE;
- IF (from_account_timestamp1 <> from_account_timestamp2 OR
- from_account_balance <> from_account_balance2) THEN
- ROLLBACK;
- SET status=-1;
- SET message=CONCAT("Transaction cancelled due to concurrent update",
- " of account" ,from_account);
- ELSE
- UPDATE account_balance
- SET balance=balance-tfer_amount
- WHERE account_id=from_account;
- UPDATE account_balance
- SET balance=balance+tfer_amount
- WHERE account_id=to_account;
- COMMIT;
- SET status=0;
- SET message="OK";
- END IF;
- ELSE
- ROLLBACK;
- SET status=-1;
- SET message="Insufficient funds";
- END IF;
- END$$
相关推荐
36谈谈MySQL支持的事务隔离级别,以及悲观锁和乐观锁的原理和应用场景?
主要介绍了MySQL中悲观锁与乐观锁的相关资料,帮助大家更好的理解和学习MySQL数据库,感兴趣的朋友可以了解下
06-VIP-深入理解Mysql事务隔离级别与锁机制.pdf
不要把他们与mysql中提供的锁机制(表锁,行锁,排他锁,共享锁)混为一谈。 一、悲观锁 顾名思义,就是对于数据的处理持悲观态度,总认为会发生并发冲突,获取和修改数据时,别人会修改数据。所以在整个数据处理过程...
java,乐观锁,悲观锁详解释
乐观锁和悲观锁式并发控制主要采用的技术手段 悲观锁 在关系数据库管理系统中,悲观并发控制(悲观锁,PCC)是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作的每...
主要给大家介绍了关于Mysql悲观锁和乐观锁使用的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
Mysql的四种隔离级别 Mysql的四种隔离级别 Mysql的四种隔离级别
主要介绍了SpringBoot整合MyBatis实现乐观锁和悲观锁的示例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
所谓乐观锁,重视假设最好的情况,每次去拿数据都认为别人不会修改,所以不会上锁,但是会在更新时判断一下在此期间这个数据有没有更改,使用版本号机制和CAS算法实现,乐观锁适用于多读的应用类型,这样可以提高...
MySQL事务隔离级别详解,这个面试经常会面到,必会呀,哈哈
NULL 博文链接:https://cuishuangjia.iteye.com/blog/964885
查询:默认事务隔离级别 mysql> select @@tx_isolation;当前会话的默认事务隔离级别 mysql> select @@session.tx_isolation;当前会话的默认事务隔离级别 mysql> select @@global.tx_isolation;全局的事务隔离级别
详解Mysql事务隔离级别与锁机制.doc
深入理解Mysql事务隔离级别与锁机制.pdf