October 1 2016

transcation & lock

transcation & lock

transcation
一组数据操作执行步骤,这些步骤视为一个工作单元
--用于对多个语句进行分组
--可以在多个客户机并发访问同一个表中的数据时使用
所有步骤都成功或都失败
如果所有步骤正常,则执行
如果步骤出现错误或不完整,刚取消
遵从acid
acid (数据库事务正确执行的四个基本要素的缩写)
ACID,指数据库事务正确执行的四个基本要素的缩写。包含:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。一个支持事务(Transaction)的数据库,必需要具有这四种特性,否则在事务过程(Transaction processing)当中无法保证数据的正确性,交易过程极可能达不到交易方的要求。

原子性
整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性
一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态,不管在任何给定的时间并发事务有多少。
也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性
隔离性
隔离状态执行事务,使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务,运行在相同的时间内,执行相同的功能,事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化,为了防止事务操作间的混淆,必须串行化或序列化请求,使得在同一时间仅有一个请求用于同一数据。
持久性
在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
由于一项操作通常会包含许多子操作,而这些子操作可能会因为硬件的损坏或其他因素产生问题,要正确实现ACID并不容易。ACID建议数据库将所有需要更新以及修改的资料一次操作完毕,但实际上并不可行。

事务流程图
开始事务

从帐户1中取款 (否) --》回滚
(是)
在帐户2中存款 (否) --》回滚
(是)
提交(永久记录更改)
结束事务

事务sql控制语句
start transction(或 begin):显式开始一个新事务
savepoint 分配事务过程中的一个位置,以供将来引用
commit 永久记录当前事务所做的更改
rollback 取消当前事务所做的更改
rollback to sacepoint 取消在savepoint之后执行的更改
release savepoint 删除savepoint标识符
set autocommit 为当前连接禁用或雇用默认autocommit模式

autocommit模式
确定开始新事务的方式和时间
默认情况下 autocommit模式处于雇用状态
--作为一个事务隐式提交每个语句
在一个选项文件中奖autocommit模式设置为0
或者
set global autocommit=0
SET SESSION AUTOCOMMIT=0;
SET @@AUTOCOMMIT :=0;

如果禁用autocommit,刚默认情况下,事务会跨越多个语句
可以使用commit或rollback结束事务
使用select检查autocommit设置
select @@AUTOCOMMIT;

隐式提交
隐式提交会终止当前事务
用于隐式提交的sql语句
--start transaction
--set autocommit=1

导致提交的非事务语句

数据定义语句(alter /create /drop)
管理语句(grant /revoke /set password)
锁定语句(lock tables /unlock tables)

transaction engines
事务存储引擎
使用show engines列出引擎特征

mysql> SHOW ENGINES\G
********************* 2. row *********************

Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking,
and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
********************* 1. row *********************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO

mysql> show engines
-> ;
+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+

事务隔离问题
三个常见问题
脏读
--一个事务读取另一个未提交的事务所做的更改
不可重复读
--另一未提交的事务所做的更改导致先前读取操作的不可重复
虚读(或虚行)
--某行以前从未在同一事务中显示,而现在显示出来

事务隔离级别
mysql的事务隔离级别一共有4种 oracle则可以参照:http://www.ucjmh.com/oracle的事务隔离级别/

• READ UNCOMMITTED
– 允许事务查看其他事务所进行的未提交更改
• READ COMMITTED
– 允许事务查看其它事务所进行的已提交更改
• REPEATABLE READ
– 确保每个事务的的SELECT输出一致
– InnoDB 的默认级别
• SERIALIZABLE
– 将一个事务的结果与其他事务完全隔离

隔离级别问题

隔离级别 “脏”读 不可重复读 虚读
读取未提交 可以 可以 可以
读取已提交 不可以 可以 可以
可重复读 不可以 不可以 可以*
可序列化 不可以 不可以 不可以

*对于innodb不可以,innodb对于“可重复读” 将使用快照

设置隔离级别

--在mysqld命令中使用 --transaction-isolation选项
--或者,在配置文件中设置 transaction-isolation:

[mysqld]
transaction-isolation =

使用set transaction isolation level语句为正在运行的服务器设置
语法如下:
SET GLOBAL TRANSACTION ISOLATION LEVEL ;
SET SESSION TRANSACTION ISOLATION LEVEL ;
SET TRANSACTION ISOLATION LEVEL ;

全局的隔离级别
需要super特权

mysql> SELECT @@tx_isolation;
+—————–+
| @@tx_isolation |
+—————–+

mysql> SELECT @@global.tx_isolation, @@session.tx_isolation;
+———————–+————————+
| @@global.tx_isolation | @@session.tx_isolation |
+———————–+————————+
| READ-UNCOMMITTED | REPEATABLE-READ |
+———————–+————————+

mysql使用多线程体系结构
--多个客户机访问一个表时会出现问题
--有必要对客户机进行协调
锁定是一种防止出现并发问题的机制
--由服务器管理
--锁定供一个客户机访问,限制其他客户
锁定类型:
--共离锁
--互斥锁

显示行锁

innodb支持两种类型的行锁定
lock in share mode
--使用共享锁锁定每一行
select * from country where code=’aa’ LOCK IN SHARE MOD \g
for update
--使用互斥锁锁定每一行
SELECT counter_field INTO @@counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = @@counter_field + 1;

死锁
如果多个事务都需要访问数据,而另一个事务已经以互斥方式锁定该数据,刚会发生死锁
在两个或更多事务之间发生循环依赖性时
例如,t1等待由t2锁定的资源,而t2等待由t3等待的资源,同时t3又等待t1锁定的资源。
innodb会检测并中止(回滚)其中一个事务,并允许另一个事务完成

隐式锁
mysql服务器会根据所发出的命令以及使用的存储引擎来锁定表(或行)
操作 innodb myisam
select 无锁* 表级别共享锁
update/delete 行级别互斥锁 表级别互斥锁
alter table 表级别共享锁 表级别共享锁

* 无锁 除非使用了serializable级别、lock in shared mode 或 for update



Copyright 2019. All rights reserved.

Posted 2016年10月1日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *