Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 6. Managing Database Transactions
Poor transactional control can hurt database performance and even bring the database to a halt. Repeated poor database performance may be due to a lack of transactional control during large inserts , updates, or deletes. Not only are large batch processes, such as these demanding on the CPU and memory themselves , but temporary storage for rollback information continues to grow until either a COMMIT or ROLLBACK command is issued.
When a COMMIT is issued, rollback transactional information is written to the target table and the rollback information in temporary storage is cleared. When a ROLLBACK is issued, no changes are made to the database and the rollback information in the temporary storage is cleared. If neither a COMMIT nor ROLLBACK is issued, the temporary storage for rollback information continues to grow until there is no more space left, thus forcing the database to stop all processes until space is freed. Although space usage is ultimately controlled by the DBA, a lack of transactional control can still cause database processing to stop, sometimes forcing the DBA to take action that may consist of killing running user processes.
