5.5 How should I build transactions?
Key points
When building transactions, keep the following in mind:
- A transaction is a unit of work.
- Keep operations that are required to
maintain logical data integrity within a single transaction. If a row should
be added to Table2 only if a specific row exists in Table1, then
SELECT on
Table1 and INSERT into Table2 in the same transaction.
This may reduce concurrency, but is needed to prevent data corruption.
- Keep transactions short.
- Make a transaction only as long as it needs to
be to perform its specific function. Each additional SQL statement generally
acquires more locks. Locks are normally held until the transaction commits.
The longer the transaction, the greater the potential that the transaction
will hold a lock that might be needed by another transaction.
- Keep non-database processing outside of transactions.
- Extra processing
takes time, so locks might be held longer than they need to be.
- Retrieve all user input before the start of a transaction.
- Keep terminal reads and writes (especially user prompts) outside of
transactions to ensure that locks are not held when someone walks away from
the terminal.
Page last updated on November 29, 1995
|
Printable version
