Rule Definition
Not managing transactions can lead to data corruption and incorrect behaviour.
Remediation
Add transaction management into the Stored Procedures.
Check the transaction count to avoid imbricated transactions.
Violation Code Sample
update mytable set col1 = 123
where col2 = 456
Fixed Code Sample
Declare @OpenedTran bit
Declare @Error int
/*See if a transaction is already opened and possibly open a new one*/
If @@trancount>0
Select @OpenedTran = 1
Else
Begin
Select @OpenedTran = 0
Begin Tran
End
update mytable set col1 = 123
where col2 = 456
Select @Error = @@error
GTRAN:
/*Close or rollback the transaction if it has been opened in this procedure*/
If @OpenedTran = 0
Begin
If @Error = 0 /* no error occurs*/
Commit Tran
Else
Rollback Tran
End
/*If this procedure is not called from another one, Raise an error*/
If @@nestlevel = 1
Begin
If @Error !=0
Raiserror xxx
End
/*return the error value*/
Return @Error
Related Technologies
Microsoft T-SQL
Sybase T-SQL
Technical Criterion
Architecture - Multi-Layers and Data Access
About CAST Appmarq
CAST Appmarq is by far the biggest repository of data about real IT systems. It's built on thousands of analyzed applications, made of 35 different technologies, by over 300 business organizations across major verticals. It provides IT Leaders with factual key analytics to let them know if their applications are on track.