What are the ways to code efficient transactions?Should not allow input from users during a transaction.
- All the information that is required from the user should be gathered before the transaction starts. If some additional information is required rollback the transaction and restart the transaction after the input is provided.
Transactions should not be opened while browsing through data.
- Till all the preliminary data analysis is not completed the transactions should not be started.
Keep the transaction as short as possible.
- After the modifications are made start the transaction and execute the modified statements and immediately rollback or commit and do not open the transaction unless it is required.
Try to use lower transaction isolation levels.
A system with a low probability of concurrent updates there is an occasional error “somebody else changed your data after you read it” can be much lower than the overhead of always locking rows as they are read.
Accessing the least amount of data possible while in a transaction.
The smaller the amount of data that is accessed in the transaction the fewer the number of rows that will be locked by reducing the contention between the transactions.