图片 1

事务处理

一. 概述

  通常来说,死锁都是应用设计问题,通过调整业务流程,数据库对象设计,事务大小,以及访问数据库的sql语句,绝大部分死锁都可以避免,下面介绍几种避免死锁的常用
方法.
  1.
在应用中,如果不同的程序并发操作多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。按顺序对表进行操作,是很常用的一种避免死锁的操作。
比如:有二个不一样的存储过程,同时在对一个表进行复杂的删改操作。这种情况可以考虑先让一个执行完成,再让另一个在执行。
  2.
在程序中以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。比如常见的就是多线程下在程序中lock锁住,在进程下保持串行处理。
  3.
在事务中,如果要更新记录,应该直接申请足够级别的锁,即排它锁,而不是先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其它事务可能又已经获得了相同记录的共享锁,从而造成锁冲突。
我理解是在事务中首先将要更新的记录,以select .. for
update方式获得排它锁,
在事务里处理完逻辑后就可以直接更新而不用考虑锁冲突。 代码如下:

SET autocommit=0
-- 将要更新的数据先获得排它锁
SELECT * FROM city WHERE city_id=103 FOR UPDATE;
-- 逻辑处理  ....
-- 最后更新可以避免锁冲突
UPDATE city SET cityname='杭州' WHERE city_id=103;
COMMIT;

  4. 在默认级别Repeatable read下, 如果两个线程同时对相同条件记录用
select .. for update 加排它锁,在没有符合该条件记录情况下,两个线程都会加锁成功。当一个程序发现记录不存在,就试图插入一条新数据,如果两个线程都这么做,就会出现死锁。这是因为在Repeatable
read下产生了间隙锁。这种情况下,将隔离级别改成Read
commited,就可避免问题 如下图表格
贴出了二个隔离级别下产生锁的差异。

图片 1

  5. 当在Repeatable read下,如果两个线程都先执行select .. for update。
在判断是否存在符合条件的记录,如果没有,就插入记录,此时,只有一个线程能插入成功,另一个线程会出现锁等待,
当第1个线程提交后,第2个线程如因为主键值重复,会出现异常。但却获得了一个排它锁,
需要执行rollback释放排它锁。避免影响其它事务。
  总结:尽管通过上面介绍和sql
优化等措施,可以大大减少死锁,但死锁很难完全避免。因此。
在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。在程序异常里或commit或rollback。

事务

二. 检查死锁产生的原因

  如果出现死锁,可以用SHOW ENGINE INNODB STATUS
命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的sql语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等,以此分析死锁产生的原因和改进措施。

-- 查看最后一个死锁
SHOW ENGINE  INNODB STATUS;

LATEST DETECTED DEADLOCK
------------------------
2018-08-02 18:07:45 0x7f3a12209700
*** (1) TRANSACTION:
TRANSACTION 35489574, ACTIVE 114 sec STARTING INDEX READ
mysql TABLES IN USE 1, locked 1
LOCK WAIT 4 LOCK struct(s), HEAP size 1136, 2 ROW LOCK(s)
MySQL thread id 2634494, OS thread handle 139887387092736, QUERY id 109768880 172.168.18.202 root Sending DATA
-- 因为会话2 已获得排他锁, 些语句 等待
 SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 479 page NO 3 n bits 72 INDEX GEN_CLUST_INDEX of TABLE `test`.`cityNew` trx id 35489574 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 35489577, ACTIVE 8 sec STARTING INDEX READ, thread declared inside INNODB 5000
mysql TABLES IN USE 1, locked 1
4 LOCK struct(s), HEAP size 1136, 3 ROW LOCK(s)
MySQL thread id 2634624, OS thread handle 139887388956416, QUERY id 109768953 172.168.18.202 root statistics
-- 死锁
 SELECT * FROM city  WHERE city_id=103 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 479 page NO 3 n bits 72 INDEX GEN_CLUST_INDEX of TABLE `test`.`cityNew` trx id 35489577 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 477 page NO 3 n bits 80 INDEX PRIMARY of TABLE `test`.`city` trx id 35489577 lock_mode X LOCKS rec but NOT gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------

1.事务的概念:

事务是指逻辑上的一组操作,这组操作要么同时完成要么同时不完成。参考转账操作。

2.

如果你自己不去控制事务,数据库默认一条sql语句就处在自己单独的事务当中。

3.也可以使用命令去开启一个事务:

start
transaction;–开启事务,这条语句之后的sql语句将处在一个事务当中,这些sql语句并不会立即执行

Commit–提交事务,一旦提交事务,事务中的所有sql语句才会执行。

Rollback — 回滚事务,将之前所有的sql取消。

conn.setAutoCommit(false);

conn.commit();

conn.rollback();

conn.setSavePoint();

conn.rollback(sp);

4.事务的四大特性ACID

(1)原子性:事务的一组操作是原子的不可再分割的,这组操作要么同时完成要么同时不完成。

(2)一致性:
事务在执行前后数据的完整性保持不变。数据库在某个状态下符合所有的完整性约束的状态叫做数据库具有完整性。在解散一个部门时应该同时处理员工表中的员工保证这个事务结束后,仍然保证所有的员工能找到对应的部门,满足外键约束。

(3)隔离性:当多个事务同时操作一个数据库时,可能存在并发问题,此时应保证各个事务要进行隔离,事务之间不能互相干扰。

(4)持久性:持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,不能再回滚。

5.事务的隔离性导致的问题(所有的问题都是在某些情况下才会导致问题)

~脏读:一个事务读取到了另一个事务未提交的数据。

1 | a    |  1000

2 | b    |  1000

b—>a

start transaction;

update account set money=money-100 where name=’b’;

update account set money=money+100 where name=’a’;

rollback;

select * from account where name = ‘a’;1000 1000

~不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同.

start transaction:

活期存款:1000

定期存款:1000

固定资产: 2000


开启事务

取走获取存款1000

提交事务


总资产:3000

~幻读(虚读):一个事务读取到了另一个事务插入的数据(已提交)

a 2000

b 2000

c 2000

start transaction;

select sum(money) from account;6000


开启事务

创建一个账户并存入1000块钱

提交了事务


select count(*)from account;4

avgMoney = allMoney/count;6000/4=1500

6.数据库的隔离级别

~Read
uncommitted:如果将数据库设定为此隔离级别,数据库将会有脏读、不可重复度、幻读的问题。

~Read
committed:如果将数据库设定为此隔离级别,数据库可以防止脏读,但有不可重复度、幻读的问题。

~Repeatable read:
如果将数据库设定为此隔离级别,数据库可以防止脏读、不可重复度,但是不能防止幻读。

~Serializable:将数据库串行化,可以避免脏读、不可重复读、幻读。

安全性来说:Serializable>Repeatable read>Read committed>Read
uncommitted

效率来说:Serializable<Repeatable read<Read committed

通常来说,一般的应用都会选择Repeatable read或Read
committed作为数据库隔离级别来使用。

mysql默认的数据库隔离级别为:REPEATABLE-READ

如何查询当前数据库的隔离级别?select @@tx_isolation;

如何设置当前数据库的隔离级别?set [global/session] transaction
isolation level …;

~此种方式设置的隔离级别只对当前连接起作用。

set transaction isolation level read uncommitted;

set session transaction isolation level read uncommitted;

~此种方式设置的隔离级别是设置数据库默认的隔离级别

set global transaction isolation level read uncommitted;

7.锁机制:

共享锁:共享锁和共享锁可以共存。

排他锁:排他锁和所有锁都不能共存。

在非串行化下,所有的查询都不加锁,所有的修改操作都会加排他锁。

在串行化下,所有的查询都加共享锁,所有的修改都加排他锁。

死锁

8.更新丢失

如果多个线程操作,基于同一个查询结构对表中的记录进行修改,那么后修改的记录将会覆盖前面修改的记录,前面的修改就丢失掉了,这就叫做更新丢失。

Serializable可以防止更新丢失问题的发生。其他的三个隔离级别都有可能发生更新丢失问题。

Serializable虽然可以防止更新丢失,但是效率太低,通常数据库不会用这个隔离级别,所以我们需要其他的机制来防止更新丢失:

乐观锁和悲观锁不是数据库中真正存在的锁,只是人们在解决更新丢失时的不同的解决方案,体现的是人们看待事务的态度。

悲观锁:

隔离级别不设置为Serializable,防止效率过低。

在查询时手动加上排他锁。

如果数据库中的数据查询比较多而更新比较少的话,悲观锁将会导致效率低下。

乐观锁:

在表中增加一个version字段,在更新数据库记录是将version加一,从而在修改数据时通过检查版本号是否改变判断出当前更新基于的查询是否已经是过时的版本。

如果数据库中数据的修改比较多,更新失败的次数会比较多,程序需要多次重复执行更新操作。