Bad Transaction Designs
Posted by decipherinfosys on February 7, 2007
Here is a compilation of the bad transaction designs that we have seen in our experience in the field. Poorly written transactions are the biggest reason for experience blocking as well as data corruption issues.
- Not choosing the right transaction isolation level. This differs from application to application and also varies between different RDBMS. Oracle for example supports two transaction isolation levels where as SQL Server and DB2 LUW support the whole set of ANSI isolation levels (and SQL 2005 infact adds additional ones to simulate the multi-version concurrency behavior of Oracle).
- Transaction designs that ask for user input in the middle of a transaction. Suppose a transaction takes a lock on a resource and then asks the end user for input and say the end user goes on a break…that lock will be held till the user decides to enter a value or his session is killed.
- If you have an OLTP system, the transactions should have queries that are optimal and are preferrably SET based queries rather than cursor based.
- Not handling cancelled queries appropriately within a transaction. Suppose that there is a transaction and within that transaction, a SQL statement fails because of say a lock timeout error. The application then needs to issue the rollback or commit statements. Most developers assume that if a statement within a transaction fails, the engine will automatically rollback the transaction. That is not the case. In case you have an ORM (Object Relational Mapper) layer, then you can code is such that in such scenarios, it issues a rollback.
- Not having proper error handling and error logging.
- In the case of SQL Server, when you have nested transactions, the transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed i.e. the rollback rollsback all the transactions where as the outermost commit is the only one that commits all the transaction. So, do not assume that the inner transaction results are saved even if the outermost transaction fails.
- In the case of SQL Server or DB2, use locking/query hints carefully. Bad locking hints can cause excessive blocking since lock escalation issues can arise.
- This is more of a good SQL coding practice rather than good transaction design practice – use bind variables (also known as parameterized queries).
- Use client side code to check for and eliminate any bad data issues. The reason why this is important is because it may cause an application to form a query that is resource intensive like a user enters a wild-card for example for the first_name column and the query formed is thus sub-optimal.
- Use savepoints judiciously – use them in scenarios where the chances of encountering an error is less.
Sorry, the comment form is closed at this time.