トランザクション
C#
共通メソッド
private void AddParameter(SqlCommand cmd)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@AddressID", SqlDbType.Int, 3),
new SqlParameter("@City", SqlDbType.NVarChar, 20),
new SqlParameter("@PostCD", SqlDbType.NVarChar, 30),
new SqlParameter("@Name", SqlDbType.NVarChar, 25)
};
paras[0].Value = "101";
paras[1].Value = "東京";
paras[2].Value = "125-0001";
paras[3].Value = "Andy";
foreach (var para in paras)
{
cmd.Parameters.Add(para);
}
}
ストアド（トランザクションあり）
CREATE PROCEDURE dbo.sp_Tran
(
@City nvarchar(20),
@AddressID int,
@PostCD nvarchar(30),
@Name nvarchar(25)
)
AS
BEGIN TRANSCATION tran;
UPDATE
Address
SET
City = @City,
PostalCode = @PostalCode,
Name = @Name
WHERE
AddressID = @AddressID;
IF(@@error = 0)
COMMIT TRANSCATION tran;
ELSE
ROLLBACK TRANSCATION tran;
GO
ストアド（トランザクションなし）
CREATE PROCEDURE dbo.sp_NoTran
(
@City nvarchar(20),
@AddressID int,
@PostCD nvarchar(30),
@Name nvarchar(25)
)
AS
UPDATE
Address
SET
City = @City,
PostalCode = @PostalCode,
Name = @Name
WHERE
AddressID = @AddressID;
GO
１．SQL Transaction
効率よいが、複雑な業務に適用できない。
using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "sp_Tran";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
conn.Open();
this.AddParameter(cmd);
cmd.ExecuteNonQuery();
}//using
２．ADO.NET Transaction
簡単だが、一つのDBしか使えない。
using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
using (SqlTransaction tran = conn.BeginTransaction())
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "sp_NoTran";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Transaction = tran;
conn.Open();
this.AddParameter(cmd);
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
throw;
}//try-catch
}//using
３．TransactionScope
分散トランザクション
※MSDTCサービスが開始
net start msdtc
netsh firewall set allowedprogram %windir%\system32\msdtc.exe MSDTC enable
//TransactionScopeはSystem.Transactions.dll中
using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
using (TransactionScope ts = new TransactionScope())
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "sp_NoTran";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
conn.Open();
this.AddParameter(cmd);
try
{
cmd.ExecuteNonQuery();
ts.Complete();
}
catch (Exception)
{
throw;
}//try-catch
}//using
４．COM+ Transaction
分散型設計で複数DBに対するトランザクション
//ServiceConfigはSystem.EnterpriseServices.dll中
using (SqlConnection conn = new SqlConnection(CONNECTION_STRING))
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "sp_NoTran";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
ServiceConfig sc = new ServiceConfig()
{
Transaction = TransactionOption.Required,
TrackingEnabled = true
};
ServiceDomain.Enter(sc);
conn.Open();
this.AddParameter(cmd);
try
{
cmd.ExecuteNonQuery();
ContextUtil.SetComplete();
}
catch (Exception)
{
ContextUtil.SetAbort();
throw;
}
finally
{
ServiceDomain.Leave();
}//try-catch-finally
}//using
Java
プログラミングモデル
１．Java API
単一データベースに向け
java.sql.Connection
setAutoCommit commit rollback
Connection conn = getConnection();
conn.setAutoCommit(false);
boolean isSuccess = doSomething();
if(isSuccess){
conn.commit();
}else{
conn.rollback();
}
２．JTA(Java Transaction API)
分散データベースに向け
JPA実現
java.transaction.UserTransaction
begin commit rollback setRollbackOnly setTransactionTimeout getStatus
InitialContext ctx = new InitialContext();
UserTransaction ut = (UserTransaction)
ctx.lookup("javax.transaction.UserTransaction");
ut.begin();
boolean isSuccess = doSomething();
if(isSuccess){
ut.commit();
}else{
ut.rollback();
}
Spring実現
org.springframework.transaction.PlatformTransactionManager
getTransaction commit rollback
JTAを含む(JtaTransactionManager)
ApplicationContext context = getApplicationContext();
TransactionTemplate transactionTemplate =
(TransactionTemplate)context.getBean("TransactionTemplate");
transactionTemplate.execute(new TransactionCallback(){
public Object doInTransaction(TransactionStatus status){
boolean isSuccess = doSomething();
if(isSuccess){
...
}else{
status.setRollbackOnly();
}
return isSuccess;
}
});
宣言モデル
XML設定ファイル
Anotation
SpringのAOP
<bean id="xxxService" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
<property name="transactionManager" ref="transactionManager"/>
<property name="target" ref="xxxServiceTarget"/>
<property name="transactionAttributes">
<props>
<prop key="*">PROPAGATION_SUPPORTS</prop>
<prop key="update*">PROPAGATION_REQUIRED</prop>
</props>
</property>
★S2AOPによるトランザクション制御
j2ee.diconに定義済みのインターセプター
トランザクション設定
<component class="example.dao.SampleDao">
<aspect pointcut="insert.*,update.*,delete.*">
j2ee.requiredTx
</aspect>
</component>
例外はストーしてもトランザクションはコミットしたい場合の設定
j2ee.dicon
<components namespace="j2ee">
<include path="jta.dicon" />
<include path="jdbc.dicon" />
<component name="requiredTx" class="org.seasar.extension.tx.RequiredInterceptor">
<initMethod name="addCommitRule">
<arg>@example.exception.CommitException@class</arg>
</initMethod>
</component>
...
</components>
１アクションで複数のトランザクションを実行する
public class SampleAction {
@Resource
protected AService aService;
@Resource
protected BService bService;
@Execute(validator=false)
public String index() {
aService.insert();
bService.update();
...
}
}
public class AService {
@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void insert() {
...
}
}
トランザクションの手動制御
public class SampleAction {
@Resource
protected UserTransaction userTransaction;
@Execute(validator=false)
@TransactionAttribute(TransactionAttributeType.NEVER)
public String index() {
userTransaction.begin();
try {
...
} catch(Exception e) {
// 例外をスローせず
userTransaction.setRollbackOnly();
} finally {
if(userTransaction.getStatus() = Status.STATUS_ACTIVE) {
userTransaction.commit();
return "index.jsp";
}
userTransaction.rollback();
return "error.jsp";
}
}
}
★Spring事务失效的具体场景
1.注解@Transactional配置的方法非public权限修饰
2.注解@Transactional所在类非Spring容器管理的bean
3.注解@Transactional所在类中，注解修饰的方法被类内部方法调用
4.业务代码抛出异常类型非RuntimeException，事务失效
5.业务代码中存在异常时，catch语句块没有throw new RuntimeExecption异常
6.注解@Transactional中Propagation属性值设置Propagation.NOT_SUPPORTED
7.mysql关系型数据库，且存储引擎是MyISAM而非InnoDB，则事务会不起作用