目录
Mysql的TCL(Transaction Control Language)
事务特性:
- 原子性:强调事务的不可分割
- 一致性:强调的是事务执行的前后,数据的完整性要保持一致
- 隔离性:一个事务的执行不应该受到其他事务的干扰
- 持久性:事务一旦结束(提交/回滚)数据就持久保持到了数据库。
Mysql的TCL(Transaction Control Language)
- set antocommit=false; 设置手动提交;
- rollback 回滚;
- commit 提交。
代码如下:
mysql> use mybase;
Database changed
mysql> desc test;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| empno | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
| job | varchar(20) | YES | | NULL | |
| mgr | int(11) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | double(7,2) | YES | | NULL | |
| COMMIT | double(7,2) | YES | | NULL | |
| deptno | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> select * from test;
+-------+--------+--------+------+------------+----------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | COMMIT | deptno |
+-------+--------+--------+------+------------+----------+--------+--------+
| 1001 | 小白 | clerk | 1001 | 2020-07-25 | 5000.00 | 200.00 | NULL |
| 1002 | 白展堂 | clerk | 1001 | 1983-05-09 | 7000.00 | 200.00 | 10 |
| 1003 | 李大嘴 | clerk | 1002 | 1980-07-08 | 8000.00 | 100.00 | 10 |
| 1004 | 吕秀才 | clerk | 1002 | 1985-11-12 | 4000.00 | NULL | 10 |
| 1005 | 郭芙蓉 | clerk | 1002 | 1985-03-04 | 4000.00 | NULL | 10 |
| 2001 | 胡一菲 | leader | NULL | 1994-03-04 | 15000.00 | NULL | 20 |
| 2002 | 陈美嘉 | manger | 2001 | 1993-05-24 | 10000.00 | 300.00 | 20 |
| 2003 | 吕子乔 | clerk | 2002 | 1995-05-19 | 7300.00 | 100.00 | 20 |
| 2004 | 张伟 | clerk | 2002 | 1994-10-12 | 8000.00 | 500.00 | 20 |
| 2005 | 曾小贤 | clerk | 2002 | 1993-05-10 | 9000.00 | 700.00 | 20 |
| 3001 | 刘梅 | leader | NULL | 1968-08-08 | 13000.00 | NULL | 30 |
| 3002 | 夏冬梅 | manger | 3001 | 1968-09-21 | 10000.00 | 600.00 | 30 |
| 3003 | 夏雪 | clerk | 3002 | 1989-09-21 | 8000.00 | 300.00 | 30 |
| 3004 | 张一山 | clerk | 3002 | 1991-06-16 | 88000.00 | 200.00 | 30 |
+-------+--------+--------+------+------------+----------+--------+--------+
14 rows in set (0.00 sec)
mysql> insert into test (ename,job,commit) values('marry','clerk',300);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+-------+--------+--------+------+------------+----------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | COMMIT | deptno |
+-------+--------+--------+------+------------+----------+--------+--------+
| 1001 | 小白 | clerk | 1001 | 2020-07-25 | 5000.00 | 200.00 | NULL |
| 1002 | 白展堂 | clerk | 1001 | 1983-05-09 | 7000.00 | 200.00 | 10 |
| 1003 | 李大嘴 | clerk | 1002 | 1980-07-08 | 8000.00 | 100.00 | 10 |
| 1004 | 吕秀才 | clerk | 1002 | 1985-11-12 | 4000.00 | NULL | 10 |
| 1005 | 郭芙蓉 | clerk | 1002 | 1985-03-04 | 4000.00 | NULL | 10 |
| 2001 | 胡一菲 | leader | NULL | 1994-03-04 | 15000.00 | NULL | 20 |
| 2002 | 陈美嘉 | manger | 2001 | 1993-05-24 | 10000.00 | 300.00 | 20 |
| 2003 | 吕子乔 | clerk | 2002 | 1995-05-19 | 7300.00 | 100.00 | 20 |
| 2004 | 张伟 | clerk | 2002 | 1994-10-12 | 8000.00 | 500.00 | 20 |
| 2005 | 曾小贤 | clerk | 2002 | 1993-05-10 | 9000.00 | 700.00 | 20 |
| 3001 | 刘梅 | leader | NULL | 1968-08-08 | 13000.00 | NULL | 30 |
| 3002 | 夏冬梅 | manger | 3001 | 1968-09-21 | 10000.00 | 600.00 | 30 |
| 3003 | 夏雪 | clerk | 3002 | 1989-09-21 | 8000.00 | 300.00 | 30 |
| 3004 | 张一山 | clerk | 3002 | 1991-06-16 | 88000.00 | 200.00 | 30 |
| 3005 | marry | clerk | NULL | NULL | NULL | 300.00 | NULL |
+-------+--------+--------+------+------------+----------+--------+--------+
15 rows in set (0.00 sec)
mysql> set autocommit = false;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into helln test (ename,job,commit) values('marry','clerk',300);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
MySQL server version for the right syntax to use near 'test (ename,job,commit) v
',300)' at line 1
mysql> insert into test (ename,job,commit) values('helln','clerk',300);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+-------+--------+--------+------+------------+----------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | COMMIT | deptno |
+-------+--------+--------+------+------------+----------+--------+--------+
| 1001 | 小白 | clerk | 1001 | 2020-07-25 | 5000.00 | 200.00 | NULL |
| 1002 | 白展堂 | clerk | 1001 | 1983-05-09 | 7000.00 | 200.00 | 10 |
| 1003 | 李大嘴 | clerk | 1002 | 1980-07-08 | 8000.00 | 100.00 | 10 |
| 1004 | 吕秀才 | clerk | 1002 | 1985-11-12 | 4000.00 | NULL | 10 |
| 1005 | 郭芙蓉 | clerk | 1002 | 1985-03-04 | 4000.00 | NULL | 10 |
| 2001 | 胡一菲 | leader | NULL | 1994-03-04 | 15000.00 | NULL | 20 |
| 2002 | 陈美嘉 | manger | 2001 | 1993-05-24 | 10000.00 | 300.00 | 20 |
| 2003 | 吕子乔 | clerk | 2002 | 1995-05-19 | 7300.00 | 100.00 | 20 |
| 2004 | 张伟 | clerk | 2002 | 1994-10-12 | 8000.00 | 500.00 | 20 |
| 2005 | 曾小贤 | clerk | 2002 | 1993-05-10 | 9000.00 | 700.00 | 20 |
| 3001 | 刘梅 | leader | NULL | 1968-08-08 | 13000.00 | NULL | 30 |
| 3002 | 夏冬梅 | manger | 3001 | 1968-09-21 | 10000.00 | 600.00 | 30 |
| 3003 | 夏雪 | clerk | 3002 | 1989-09-21 | 8000.00 | 300.00 | 30 |
| 3004 | 张一山 | clerk | 3002 | 1991-06-16 | 88000.00 | 200.00 | 30 |
| 3005 | marry | clerk | NULL | NULL | NULL | 300.00 | NULL |
| 3006 | helln | clerk | NULL | NULL | NULL | 300.00 | NULL |
+-------+--------+--------+------+------------+----------+--------+--------+
16 rows in set (0.00 sec)
mysql> roollback;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
MySQL server version for the right syntax to use near 'roollback' at line 1
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
+-------+--------+--------+------+------------+----------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | COMMIT | deptno |
+-------+--------+--------+------+------------+----------+--------+--------+
| 1001 | 小白 | clerk | 1001 | 2020-07-25 | 5000.00 | 200.00 | NULL |
| 1002 | 白展堂 | clerk | 1001 | 1983-05-09 | 7000.00 | 200.00 | 10 |
| 1003 | 李大嘴 | clerk | 1002 | 1980-07-08 | 8000.00 | 100.00 | 10 |
| 1004 | 吕秀才 | clerk | 1002 | 1985-11-12 | 4000.00 | NULL | 10 |
| 1005 | 郭芙蓉 | clerk | 1002 | 1985-03-04 | 4000.00 | NULL | 10 |
| 2001 | 胡一菲 | leader | NULL | 1994-03-04 | 15000.00 | NULL | 20 |
| 2002 | 陈美嘉 | manger | 2001 | 1993-05-24 | 10000.00 | 300.00 | 20 |
| 2003 | 吕子乔 | clerk | 2002 | 1995-05-19 | 7300.00 | 100.00 | 20 |
| 2004 | 张伟 | clerk | 2002 | 1994-10-12 | 8000.00 | 500.00 | 20 |
| 2005 | 曾小贤 | clerk | 2002 | 1993-05-10 | 9000.00 | 700.00 | 20 |
| 3001 | 刘梅 | leader | NULL | 1968-08-08 | 13000.00 | NULL | 30 |
| 3002 | 夏冬梅 | manger | 3001 | 1968-09-21 | 10000.00 | 600.00 | 30 |
| 3003 | 夏雪 | clerk | 3002 | 1989-09-21 | 8000.00 | 300.00 | 30 |
| 3004 | 张一山 | clerk | 3002 | 1991-06-16 | 88000.00 | 200.00 | 30 |
| 3005 | marry | clerk | NULL | NULL | NULL | 300.00 | NULL |
温馨提示:编码格式为utf-8可能会出现乱码的情况,
解决办法之一:
打开mysql安装的地址,打开,my.ini. 并更改默认编码。查看汉字的话,建议用gBK
Jdbc事务控制
默认事务提交策略:一条命令自成一个完整事务
需求:各个逻辑单元要么一起成功,要么一起失败(比如银行转账)
手动控制事务的API
conn.setAutoCommit(false);将jdbc的事务提交改为手动提交
conn.commit();
conn.rollback();
注意:连接提交策略一经设置,永久改变。
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import util.jdbcUtil;
//jdbc控制事务
public class TestTrasaction {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstm1 = null;
PreparedStatement pstm2 = null;
//获得连接
try {
conn = jdbcUtil.getConnection();
//将jdbc事务设置成手动提交
conn.setAutoCommit(false);
//降职操作
String sql1 = "update test set job ='clerk' where ename='刘梅'";
pstm1 = conn.prepareStatement(sql1);//创建对象
pstm1.executeUpdate();//处理数据
//降奖金操作
String sql2 = "update test set commit=100 where ename = '刘梅'";
pstm2 = conn.prepareStatement(sql2);
pstm2.executeUpdate();
//提交事务
conn.commit();
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally{
try {
jdbcUtil.release(null, pstm1, null);//先关闭pstm1
jdbcUtil.release(null, pstm2, conn);//在关闭pstm2,conn
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
手动提交,好处就是可以回滚,然后,如果不一致,就无法进行操作,适用于银行转账等特定的操作。
不然你花了一w元,然而,银行账户上的钱没变化,那就糟糕了。
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/114781.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...