事务基础，数据库意外，隔离级别及jdbc配置事务

快来打我* 2022-03-06 08:56 144阅读 0赞

1.事物  
原子性，一致性，隔离性，持久性  
Atomicity:事物的所有操作要么全部成功，要么全部回滚  
Consistency:执行前后数据遵循完整约束  
Isolation：对于某一时间段中同时存在的事物，在某一个事物看来，该事物执行前后不存在其他事物  
Durability:一旦提交，永久改变  
2.几种数据库意外  
第一类丢失更新（lest update）  
![百度百度百度][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70]  
A撤销时将B的更改也撤销了  
标准定义的所有隔离界别都不允许第一类丢失更新发生  
不可重复读（unrepeatable read）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 1]  
在A两次查询间隙，B提交了更改事务，使得A查到的数据不同  
脏读（dirty read）  
![百度百度百度][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 2]  
由于B的回滚，A读到了无效数据  
幻读（phantom read）  
![百度百度百度][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 3]  
在A的两次查询间隙，B添加并提交了一条新记录  
第二类丢失更新（second lost update）  
![百度百度百度][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 4]  
由于A操作间隙中B的更改并提交，B的操作被覆盖了  
第二类丢失更新也是不允许的  
3.隔离级别  
![百度百度百度][20190318160418809.png]  
4.jdbc配置事务  
a.导入依赖

<dependency>
    			<groupId>junit</groupId>
    			<artifactId>junit</artifactId>
    			<version>4.0</version>
    			<scope>test</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-aop</artifactId>
    			<version>${spring-version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-tx</artifactId>
    			<version>${spring-version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.aspectj</groupId>
    			<artifactId>aspectjweaver</artifactId>
    			<version>1.8.14</version>
    </dependency>

2.配置一个tx事务代理 jdbc

<bean id="txManager"
    		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		<property name="dataSource" ref="dataSource" />
    	</bean>
    
    
    <tx:advice id="txAdvice" transaction-manager="txManager"> 
    		<tx:attributes>
    			<tx:method name="find*" read-only="true"/>         
    			<tx:method name="add*" propagation="REQUIRED"/>      
    			<tx:method name="update*" propagation="REQUIRED"/>
    			<tx:method name="del*" propagation="REQUIRED"/>
    		</tx:attributes>
    </tx:advice>

3.添加事务切入点

<context:component-scan
    		base-package="com.hxzy.spring.aop ">
    	</context:component-scan>
    	<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
    	<bean id="bxAop" class="com.hxzy.spring.aop.BxAop"></bean>
    	
    	<aop:config>
    		<aop:aspect ref="bxAop">
    			<aop:pointcut id="bxcut"
    				expression="execution(* com.hxzy.spring.service.impl.*.*(..))"></aop:pointcut>
    			<aop:before method="BxBefore" pointcut-ref="bxcut"></aop:before>
    			<aop:after method="BxAfter" pointcut-ref="bxcut"></aop:after>
    		</aop:aspect>
    	</aop:config>

1.  注入jdbctemplate

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    			<constructor-arg ref="dataSource"/>
    	</bean>
    	
    	private JdbcTemplate jdbcTemplate;
    	@Resource
    	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    		this.jdbcTemplate = jdbcTemplate;
    	}

5.jdbcTemplate.execute(sql)

===========================================================================  
使用注解

<tx:annotation-driven />

注意：id的名字必须要为transactionManager 否则找不到  
在类上面加一个  
@Component

在你要操作的方法上面添加一个

@Transactional

===================================

如果tx的属性设置为readonly = true  
那么在mysql下执行插入更新和删除操作是会报错

oracle底层暂时不进行支持

====================================  
额外资料  
propagation=Propagation.REQUIRED 参数设置  
isolation=Isolation.DEFAULT 参数设置  
![百度百度百度][20190318193616706.png]  
下附上具体代码

pom-xml
    <project xmlns="http://maven.apache.org/POM/4.0.0"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    
    	<groupId>com.spring</groupId>
    	<artifactId>affair</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<packaging>jar</packaging>
    
    	<name>affair</name>
    	<url>http://maven.apache.org</url>
    
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<spring-version>4.0.0.RELEASE</spring-version>
    	</properties>
    
    	<dependencies>
    	<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-beans</artifactId>
    			<version>${spring-version}</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-context</artifactId>
    			<version>${spring-version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-jdbc</artifactId>
    			<version>${spring-version}</version>
    		</dependency>
    		<dependency>
    			<groupId>com.oracle</groupId>
    			<artifactId>jdbc</artifactId>
    			<version>11.2.0.1.0</version>
    		</dependency>
    		<dependency>
    			<groupId>commons-dbcp</groupId>
    			<artifactId>commons-dbcp</artifactId>
    			<version>1.4</version>
    		</dependency>
    		<dependency>
    			<groupId>junit</groupId>
    			<artifactId>junit</artifactId>
    			<version>4.0</version>
    			<scope>test</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-aop</artifactId>
    			<version>${spring-version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-tx</artifactId>
    			<version>${spring-version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.aspectj</groupId>
    			<artifactId>aspectjweaver</artifactId>
    			<version>1.8.14</version>
    		</dependency>
    	</dependencies>
    </project>

beans-xml
    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xmlns:context="http://www.springframework.org/schema/context"
    	xmlns:aop="http://www.springframework.org/schema/aop"
    	xmlns:tx="http://www.springframework.org/schema/tx"
    	xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context.xsd
            http://www.springframework.org/schema/aop 
            http://www.springframework.org/schema/aop/spring-aop.xsd
            http://www.springframework.org/schema/tx
            http://www.springframework.org/schema/tx/spring-tx.xsd
            ">
    	
    	<context:annotation-config></context:annotation-config>
    	
    	
    	<context:component-scan
    		base-package="com.hxzy.affair" scoped-proxy="interfaces"></context:component-scan>
    	<bean name="userDaoImpl" class="com.hxzy.affair.dao.impl.UserDaoImpl"
    		scope="singleton" autowire="byType"></bean>
    	<bean name="userService"
    		class="com.hxzy.affair.service.impl.UserServiceImpl" scope="singleton"
    		autowire="byType"></bean>
    	
    	
    	<import resource="spring-aop.xml" />
    	
    	
    	<bean id="dataSource"
    		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    		<property name="driverClassName"
    			value="${jdbc.driverClassName}" />
    		<property name="url" value="${jdbc.url}" />
    		<property name="username" value="${jdbc.username}" />
    		<property name="password" value="${jdbc.password}" />
    	</bean>
    	<context:property-placeholder
    		location="jdbc.properties" />
    	
    	
    	<bean id="txManager"
    		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		<property name="dataSource" ref="dataSource" />
    	</bean>
    	
    	
    	<tx:advice id="txAdvice" transaction-manager="txManager">
    		<tx:attributes>
    			<tx:method name="find*" read-only="true" />
    			<tx:method name="add*" propagation="REQUIRED" />
    			<tx:method name="update*" propagation="REQUIRED" />
    			<tx:method name="del*" propagation="REQUIRED" />
    		</tx:attributes>
    	</tx:advice>
    	
    	
    	<bean id="jdbcTemplate"
    		class="org.springframework.jdbc.core.JdbcTemplate">
    		<constructor-arg ref="dataSource" />
    	</bean>
    	
    </beans>
    aop-xml
    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xmlns:context="http://www.springframework.org/schema/context"
    	xmlns:aop="http://www.springframework.org/schema/aop"
    	xsi:schemaLocation="http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/spring-context.xsd
            http://www.springframework.org/schema/aop 
            http://www.springframework.org/schema/aop/spring-aop.xsd
            ">
    	
    	<context:annotation-config></context:annotation-config>
    	
    	
    	<context:component-scan
    		base-package="com.hxzy.affair.aop ">
    	</context:component-scan>
    	<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
    	<bean id="bxAop" class="com.hxzy.affair.aop.BxAop"></bean>
    	
    	<aop:config>
    		<aop:aspect ref="bxAop">
    			<aop:pointcut id="bxcut"
    				expression="execution(* com.hxzy.affair.service.impl.*.*(..))"></aop:pointcut>
    			<aop:before method="BxBefore" pointcut-ref="bxcut"></aop:before>
    			<aop:after method="BxAfter" pointcut-ref="bxcut"></aop:after>
    		</aop:aspect>
    	</aop:config>
    	
    	
    </beans>
    测试代码
    public class Test1 {
    	@Test
    	public void tefs() {
    		User user = new User();
    		user.setUserName("bx");
    		user.setPassword("dgv");
    		BeanFactory bean = new ClassPathXmlApplicationContext("./spring-bean.xml");
    		UserServiceImpl userServiceImpl = (UserServiceImpl) bean.getBean("userService");
    		userServiceImpl.addUser(user);
    		List<User> list = new ArrayList<User>();
    		list = userServiceImpl.findUser();
    		System.out.println("adminname\t\tpassword");
    		for(User u :list) {
    			System.out.println(u.getUserName()+"\t\t\t"+u.getPassword());
    		}
    	}
    }
    
    dao层
    @Configuration
    @ImportResource("classpath:spring-bean.xml")
    public class UserDaoImpl implements UserDao {
    	private JdbcTemplate jdbcTemplate;
    	private DataSource dataSource;
    
    	public DataSource getDataSource() {
    		return dataSource;
    	}
    
    	/**
    	 * 数据源
    	 */
    	@Resource
    	public void setDataSource(DataSource dataSource) {
    		this.dataSource = dataSource;
    	}
    
    	public JdbcTemplate getJdbcTemplate() {
    		return jdbcTemplate;
    	}
    
    	/**
    	 * jdbc代理
    	 */
    	@Resource
    	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    		this.jdbcTemplate = jdbcTemplate;
    	}
    
    	@Override
    	@Transactional
    	public void addUser(User user) {
    		// sql
    		String sql = "insert into admin (adminname,password) values('" + user.getUserName() + "','" + user.getPassword()
    				+ "')";
    		System.out.println("addUser");
    		jdbcTemplate.execute(sql);
    	}
    
    	@Override
    	public List<User> findUser() {
    		// TODO Auto-generated method stub
    		List<User> list = this.jdbcTemplate.query("select * from admin", new RowMapper<User>() {
    			public User mapRow(ResultSet rs, int rowNum) throws SQLException {
    				User user = new User();
    				user.setUserName(rs.getString("adminname"));
    				user.setPassword(rs.getString("password"));
    				return user;
    			}
    		});
    		return list;
    	}
    }

结果集  
![百度百度百度][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 5]  
![百度百度百度][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 6]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70]: /images/20220303/31de33a9cb65421a859bdaa7306b3482.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 1]: /images/20220303/143c0609d0a64f23b8fb430c8027ab35.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 2]: /images/20220303/c13d608eb55a43e58df2dcadc5c545ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 3]: /images/20220303/62412a7f90ab49abbd6af9d8b13e903a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 4]: /images/20220303/1cfc1f8a9c004a94a9d270710c0b998d.png
[20190318160418809.png]: /images/20220303/862206297a684024b103d8829c07b3d4.png
[20190318193616706.png]: /images/20220303/1f20a58963c749f7bcae7f484102192d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 5]: /images/20220303/045a060d26d34c0e8cd27c82becbb50b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nWXVlSHVu_size_16_color_FFFFFF_t_70 6]: /images/20220303/b192b635b09d447ba178adb4d32e1ada.png