在SSIS/DTSx中使用事务管理 Using Transaction in SSIS DTSx package
Categories: Database; Tagged with: DTSX • ETL • SSIS • Transaction; @ December 9th, 2012 11:32之前有研究过一个container中某一个component失败后的事务管理:http://liguoliang.com/2012/using-transaction-in-ssis/
今天测试某一行数据出错时的事务管理.
需求: DataFlow转移数据时非0即1, 一旦遇到失败, 回滚到操作前的状态.
解决: 使用Transaction.
测试:
准备数据:
表1 Source: tbUser: userID, userName, emai, age(varchar)
表2, Destination: 结构与表1相似, 但age 类型为 int.
(用for loop insert一堆数据:
DECLARE @i int
SET @i = 0
WHILE @i < 1000000
BEGIN
SET @i = @i + 1
INSERT INTO guoliangDB.dbo.tbUser (userName, email) values (‘name’ + CAST(@i as varchar(8)), ’email@’ + CAST(@i as varchar(8)))
END
制造一条问题数据:
update tbUser set age = ‘fakeage’ where userID = 980000;
设计DTSx:
在数据跑到980,000时会因为数据转换而出现错误:
运行结束, 错误信息:
OnError,,9/12/2012 11:17:58 AM,9/12/2012 11:17:58 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Invalid character value for cast specification”.
OnError,,9/12/2012 11:17:58 AM,9/12/2012 11:17:58 AM,-1071607780,0x,There was an error with input column “age” (54) on input “OLE DB Destination Input” (38). The column status returned was: “The value could not be converted because of a potential loss of data.”.
此时Select Destination的数量为: 979998. 剩余的数据不会再被导入.
修改Dataflow transaction属性为: required
在遇到错误之后, Select destination数量, 逐渐减少, 直至rollback到0.
结论: 如需要事务支持, require transaction.
Using Transaction in SSIS
Categories: Database; Tagged with: SSIS • Transaction; @ October 30th, 2012 18:07Transaction is a build-in feature in SSIS, all containers(i.e, package, sequence container), and components support transaction. Support Level: NotSupported, Supported, and Required.
-
Required indicates that the container starts a transaction, unless one is already started by its parent container. If a transaction already exists, the container joins the transaction. For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.
-
Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.
-
NotSupported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to not support transactions. For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.
— http://msdn.microsoft.com/en-us/library/ms137690.aspx
For example:
Here is a sequence container, 3 steps: ‘Excute SQL Task’ to clear destination data, ‘Data Flow Task’ load data from source and insert data into destination, ‘Script Task’ just a script task which will fail always.
Without transaction:
config the sequence container property: TransactionOption = NotSupported, all components TransactionOption keep default(Supported), then run the package:
the first two component run successfully, the destination table has been cleared and the new data inserted, even through the last task failed.
Using transaction
Change ‘Sequence Container’ TransactionOption=Required, then run the package.
The last one failed, because they are in one transaction, so the destination will keep no change.
Transaction management using Spring and Hibernate
Categories: Java; Tagged with: Hibernate • Java • Spring • Transaction • TransactionTemplate; @ June 26th, 2012 22:07Spring doesn’t directly manage transactions, it comes with a selection of transaction managers, such as: DataSourceTransactionManager, HibernateTransactionManager, JpaTransactionManager etc,.
There are two way to manage transaction in Spring: based on programming or configuration. no matter which way we choose, we always need to define the beans needed:
Declaring transactions
Here we define a transactionManager using hibernateTransactionManager, and a transaction template:
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="globalRollbackOnParticipationFailure" value="false" />
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="txTemplate" class="org.springframework.transaction.support.TransactionTemplate">
<property name="transactionManager" ref="transactionManager"></property>
</bean>
‘sessionFactory’ bean configuration: http://liguoliang.com/2012/using-spring-jdbc-template/
And now, I’ll show you the the two transaction management ways:
1. by Programming
@Autowired
private TransactionTemplate txTemplate;
/**
* Insert new user using transactionTemplate.
* @param user
*/
public void insertUserByTxTemplate(final User user) {
txTemplate.execute(new TransactionCallback() {
@Override
public Void doInTransaction(TransactionStatus txStatus) {
try {
Session session = sessionFactory.getCurrentSession();
session.save(user);
throw new RuntimeException("Exception throwed!");
} catch (Exception e) {
txStatus.setRollbackOnly();
}
return null;
}
});
}
Manage transaction using txTemplate.execute().
2. by Configuration/Declaring
using annotation
/**
* Insert new user using transaction manager.
* @param user
*/
@Transactional(propagation=Propagation.REQUIRED, readOnly=false, rollbackFor=RuntimeException.class)
public void insertUser(User user) {
Session session = sessionFactory.getCurrentSession(); // SessionFactoryUtils.openSession(sessionFactory);
user.setLogin_name(user.getName());
session.save(user);
// throw new RuntimeException("RunTimeException for Transaction testing...");
}
If you are using STS(SpringSource tool suite) you can see there is an indicator:
About transaction rollback
- by default: if there is a runtime exception (UnChecked exception), will rollback;
- If rollback failed, check the @Transactional, and the method: if you get session by SessionFactoryUtils.openSession(sessionFactory),, rollback will failed – I think only sessionFactory.getCurrentSession() can work.
Links:
Spring Transaction – automatic rollback of previous db updates when one db update failes
Spring’s @Transactional does not rollback on checked exceptions
Transaction strategies: Understanding transaction pitfalls
JDBC Transaction
Categories: Java; Tagged with: JDBC • Transaction; @ February 19th, 2009 10:12默认下JDBC是Auto-commit的. 但在必要情况下, 我们需要手动控制事务, 此时:
con.setAutoCommit(false); //关闭自动Commit
PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 50);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate();//执行Update Sales操作
PreparedStatement updateTotal = con.prepareStatement(
"UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");
updateTotal.executeUpdate();//执行Update Total操作, 注意, 此二者必须同时执行成功 或同时失败.
con.commit(); //commit以上两个操作, 同时成功或失败
con.setAutoCommit(true); //将AutoCommit恢复为true