`
DavyJones2010
  • 浏览: 153777 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

MySQL: Transaction (Part I - Basic Concept)

阅读更多
 
 1. What is transaction? ----> It is a sequence of operations that should be regard as a whole and cannot be split. One operation in the sequence failed, then all the operation should be rollback.

 

    1) Take a common scenario for example:

         1) Money transfer in Bank. A want to transfer 5000$ to B.

         2) So we have to subtract 5000$ from A and subjoin 5000$ to B.

         3) The process of subtract and subjoin should be regarded as a whole.

    2) What if we subtract A but hasn't subjoin B, in this process the Bank has power off?

 

2. Example

# Create a table for example
create table account(
id int,
name varchar(20),
deposit int
) engine=innodb charset=utf8;

# Insert data into table
insert into account values(1, 'zhangsan', 3000);
insert into account values(2, 'lisi', 3000);
select * from account;
+----+----------+---------+
| id | name     | deposit |
+----+----------+---------+
|  1 | zhangsan |    3000 |
|  2 | lisi     |    3000 |
+----+----------+---------+

 

3. Properties of Transaction (ACID)

    1) Atomicity : Atomicity requires that each transaction is "all or nothing"

    2) Consistency: The consistency property ensures that any transaction will bring the database from one valid state to another. 

    3) Isolation: The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e. one after the other.

    4) Durability: Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.

 Translation in chinese:

    1) 原子性:一组操作要么都成功,要么都不成功

    2) 一致性:事务发生前和发生后,数据总量相匹配。参见下边例子。

    3) 隔离性:在一个事务中所有操作都执行完毕之前,其他会话是不可以看到中间改变的过程的。比如可能刚将张三的deposit减掉了100,但尚未给李四的deposit加上100。此时如果从外部看的话张三李四的钱应该是执行之前的数量,而不应该是张三减掉但李四未加上的状态。

    4) 持久性:事务产生的影响是不能够被撤销的,即使事务出现错误,也只能通过补偿性事务来弥补错误。例如,将钱款转入错误的账号,之后可能会再将钱款转回。但是这已经是两个事务了。第二个事务称为“补偿性事务”。

 

4. Example:

alter table account drop deposit;

# Set the type of deposit column to be unsigned tinyint whose range is 0~255
alter table account add deposit tinyint unsigned not null default 200;

# Make sure the change take effects
select * from account;
+----+----------+---------+
| id | name     | deposit |
+----+----------+---------+
|  1 | zhangsan |     200 |
|  2 | lisi     |     200 |
+----+----------+---------+

# Subtract deposit from zhangsan
update account set deposit=deposit-100 where id=1;

# Add deposit to lisi
update account set deposit=deposit+100 where id=2;
ERROR 1264 : Out of range value for column 'deposit' at row 2

# But error occurs because 300 is out of range.
# Take a look at rang check policy in MySQL 
show variables like '%mode%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| innodb_autoinc_lock_mode | 1                                                              |
| innodb_strict_mode       | OFF                                                            |
| slave_exec_mode          | STRICT                                                         |
| sql_mode                 | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+----------------------------------------------------------------+
# By default sql_mode is STRICT_TRANS_TABLES. Which will prevent out of range operation. But there is some other policies that enables to truncate the out of range data.
# So if we use such mode, the deposit of lisi would be 255 and not 300. So they sufferd a 45 bucks loss.
# This is called consistency issue.

 

分享到:
评论

相关推荐

    javax.transaction-api-1.2-API文档-中文版.zip

    赠送jar包:javax.transaction-api-1.2.jar; 赠送原API文档:javax.transaction-api-1.2-javadoc.jar; 赠送源代码:javax.transaction-api-1.2-sources.jar; 赠送Maven依赖信息文件:javax.transaction-api-1.2....

    javax.transaction-api-1.2-API文档-中英对照版.zip

    赠送jar包:javax.transaction-api-1.2.jar; 赠送原API文档:javax.transaction-api-1.2-javadoc.jar; 赠送源代码:javax.transaction-api-1.2-sources.jar; 赠送Maven依赖信息文件:javax.transaction-api-1.2....

    mha4mysql-manager-0.57-0.el7.noarch.rpm和mha4mysql-node-0.57-0.el7.noarch.rpm

    在0.57版本中,它可能已经包含了对MySQL 5.6和5.7版本的良好支持,可能也兼容了当时的最新特性,如GTID(Global Transaction Identifier),这使得基于事务ID的复制更加精确和安全。 在部署MHA时,首先需要在所有...

    apache commons 常用jar包 commons-validator commons-transaction commons-lang等

    jar包大小:93KB commons-transaction-1.2.jar jar包大小:141KB commons-scxml-0.6.jar jar包大小:254KB commons-primitives-1.0.jar jar包大小:60KB commons-pool-1.3.jar jar包大小:176KB commons-net-1.4.1.jar ...

    分布式简易事务回滚框架:biz-center-base-transaction

    分布式简易事务回滚框架:biz-center-base-transaction

    mysql-connector-java-5.1.38.jar

    9. **XA事务**:对于分布式事务处理,MySQL Connector/J提供了对X/Open Distributed Transaction Processing (DTP)模型的支持,实现XA协议。 10. **配置选项**:用户可以通过URL、系统属性或连接参数来配置连接行为...

    mysql-connector-java-8.0.30.tar

    MySQL Connector/J 8.0.30 是MySQL数据库与Java应用程序之间的重要桥梁,它是一个用于连接Java应用程序到MySQL服务器的JDBC驱动程序。这个压缩包`mysql-connector-java-8.0.30.tar`包含了运行Java应用并与MySQL进行...

    MySQL驱动 mysql-connector-net-6.6.1

    - 使用`BeginTransaction()`启动事务,`Commit()`提交事务,`Rollback()`回滚事务。 6. **参数化查询**: - 防止SQL注入,应使用`SqlParameter`添加参数到SQL命令中。 - 参数以`@paramName`形式定义,与`Sql...

    mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

    5. **复制功能升级**:MySQL 5.7的复制功能更加先进,包括GTID(Global Transaction Identifier)全局事务ID,实现了无主次服务器的概念,简化了故障转移和恢复过程。 6. **分区表改进**:分区表的性能和可用性得到...

    MySQL驱动 mysql-connector-net-6.4.4

    5. **事务处理**:MySQL Connector/NET支持事务处理,开发者可以通过`MySqlConnection`的`BeginTransaction`、`Commit`和`Rollback`方法来控制事务的生命周期,确保数据的一致性和完整性。 6. **参数化查询**:为...

    mysql-installer-community-8.0.26.0.msi

    MySQL是世界上最受欢迎的关系型数据库管理系统(RDBMS)之一,尤其在开源社区中备受推崇。本文将详细讨论“mysql-installer-community-8.0.26.0.msi”文件,这是MySQL为Windows操作系统提供的安装程序,兼容32位和64...

    MySQL驱动 mysql-connector-net-6.4.3

    6.事务处理:`MySqlConnection`支持事务,可以使用`BeginTransaction`、`Commit`和`Rollback`方法进行事务的开始、提交和回滚。 7. 数据库对象操作:`MySqlCommand`还支持创建、修改和删除数据库对象,如表、视图、...

    icon-kafka-worker:在ICON区块链上处理事件处理程序的工作程序,可与https:github.comgeometry-labsicon-api一起使用

    要在独立配置中使用: docker run icon-kafka-worker 或在docker-compose堆栈中: filter-worker-transaction :image : geometrylabs/icon-kafka-worker:latesthostname : kakfa-worker-transactionenvironment :

    mysql-connector-java-6.0.6.jar

    MySQL Connector/J 6.0.6还支持一些高级特性,比如JTA(Java Transaction API)事务管理、JPA(Java Persistence API)集成、以及MySQL特定的功能,如存储过程、触发器和用户定义函数(UDF)。了解这些特性可以帮助...

    Jmeter连接mysql数据库jar包mysql-connector-java-5.1.48-bin.zip

    - **Use Transaction**:是否在一个事务中执行所有请求,根据需求选择。 完成上述配置后,JMeter就可以通过JDBC Request组件执行SQL查询,获取或修改MySQL数据库中的数据。这在进行性能测试时非常有用,可以验证...

    mysql-connector-java-5.1.41

    MySQL Connector/J是MySQL数据库官方提供的Java数据库连接器(JDBC Driver),它允许Java应用程序通过Java平台的标准接口JDBC与MySQL数据库进行通信。标题中的"mysql-connector-java-5.1.41"指的是这个驱动的特定...

    银联所有接口规范文档,非常全面

    银联作为中国主要的银行卡组织,其接口规范文档对于从事金融支付系统开发的人员来说具有极高的参考价值。这份文档集合包含了银联多渠道平台的所有接口详细信息,旨在确保金融机构和商家能顺利接入并实现与银联系统的...

    arquillian-openejb-transaction-provider-1.7.0.jar

    标签:arquillian-openejb-transaction-provider-1.7.0.jar,arquillian,openejb,transaction,provider,1.7.0,jar包下载,依赖包

    arquillian-openejb-transaction-provider-1.7.1.jar

    标签:arquillian-openejb-transaction-provider-1.7.1.jar,arquillian,openejb,transaction,provider,1.7.1,jar包下载,依赖包

    arquillian-openejb-transaction-provider-1.5.1.jar

    标签:arquillian-openejb-transaction-provider-1.5.1.jar,arquillian,openejb,transaction,provider,1.5.1,jar包下载,依赖包

Global site tag (gtag.js) - Google Analytics