mysql ACID、隔离级别-子查询锁表

阅读数:50 评论数:0

跳转到新版页面

分类

数据库

正文

事务的ACID

1、Atomicity原子性

Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely, or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.

事务中所有操作作为一个整体,像原子一样不可分割,要么全部成功,要么全部失败。

2、Consistency一致性

Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct.

事务开始前和结束后,数据库的完整性约束没有被破坏,都是合法的数据状态。

3、Isolation隔离性

Transactions are often executed concurrently (e.g., reading and writing to multiple tables at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. Isolation is the main goal of concurrency control; depending on the method used, the effects of an incomplete transaction might not even be visible to other transactions.

并发执行的事务不会相会影响,其对数据库的影响和它们串行执行时一样。

4、Durability持久性

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.

事务一旦提交,其对数据库的更新就是持久的,任务事务或故障都不会导致数据丢失。

总结:

在事务的ACID特性中,一致性是事务的追求,而对数据库的一致性破坏主要来自两个方面:

(1)事务的并发执行

(2)事务故障或系统故障。

隔离级别

脏读:一个事务对数据进行增删改,但并没有提交,但另一个事务却能读到未提交的数据。

不可重复读:一个事务对数据进行更新或删除操作,另一个事务两次查询的数据不一致。(行级锁)

幻读:一事务对数据进行新增操作,另一事务两次查询结果不一致。(表级锁)

数据库事务的隔离级别有四个:

1、read uncommitted

存在脏读、不可重复读、幻读

2、read commited

存在不可重复读、幻读

3、repetable read

存在幻读

4、serializable

mysql默认是repetable read,所以为了保证事务中每个select是可以重复读的,select 的表会被锁住,这样当使用

update ..select * from 
insert into ...select * from 

都会锁住子表,造成死锁,可以改变mysql的隔离级别

show global variables like '%isolation%';
set global transaction_isolation='read-commited';

mysql日志

1、binlog

binlog常用来进行数据恢复、数据复制。

2、redo log

当执行更新语句时,redo log会首先写入log buffer,在事务提交前,只要将redo log持久化即可,不需要将数据持久化,当mysql重启进行恢复时会重新执行redo log记录的sql进行数据恢复。

3、undo log

在操作任何数据之前,首先将数据备份到undo log,用于数据的回滚。