Relational databases form the backbone of many business-critical systems and applications. These databases are expected to perform well and be available to satisfy the user requests for companies’ eCommerce platforms. Badly performing systems can cause customers to look elsewhere for a better solution.
Database administrators (DBAs) are responsible for making sure this doesn’t happen by maintaining high levels of system performance and availability. When systems are not responding optimally, DBAs need to find and address the underlying issues. An area of focus in this pursuit is how efficiently database transactions are executing on the affected system.
What are Database Transactions?
A transaction is simply a unit of work performed against a database. In a relational database transaction, a sequence of SQL statements are executed in a logical order either automatically or manually to make a change to a database. Through the management and control of the transactions, the integrity of the database is maintained when records are added, deleted, or updated.
Transactions are defined by four standard properties.
- Atomicity confirms that all components of the transaction are successfully completed. If they are not, the transaction is aborted and operations performed prior to the failure are rolled back.
- Consistency is used to verify that databases accurately reflect the changes from successful transactions.
- Isolation defines each transaction to be an independent and transparent entity.
- Durability ensures that the results of successful transactions persist if the system fails.
These properties are often identified by the acronym ACID.
Transaction control commands are used with Data Manipulation Language (DML) commands such as INSERT, UPDATE, and DELETE.
Following are the transaction control commands available for use:
- COMMIT – This command is used to save changes to a database invoked by a transaction. A COMMIT command saves all transactions made to the database since the last COMMIT or ROLLBACK command.
- ROLLBACK – The ROLLBACK command undoes transactions that have not been saved to the database.
- SAVEPOINT – A SAVEPOINT defines a point in a transaction that can be rolled back to without rolling back the whole transaction.
- SET TRANSACTION – This command specifies characteristics for a subsequent transaction, for example, if it can only read data.
Transactions are abstractions that are at the heart of database operations. They are made up of SQL statements and queries, which by definition makes them complex entities comprised of components that can each have issues of their own.
Problems Encountered with Database Transactions
Transactions can be plagued by multiple problems that contribute to degraded database performance.
- Locks and deadlocks – Transactions can take a considerable amount of time to complete, during which they may hold locks to system resources that impact other transactions or database operations. It may be that individual components of the transaction need to be tuned for better performance. In some cases, it may make sense to divide a transaction into smaller pieces.
- Extended rollback time – Transactions that run into trouble and need to be rolled back can cause degraded performance. ROLLBACKs can take longer than COMMITs and do not make the intended changes to the database. This implies they will often need to be executed again to achieve the desired results.
- Lack of transition isolation – Improperly isolated transactions can lead to database concurrency problems such as lost updates and non-repeatable reads. An example is when data has been changed between reads from a transaction by updates made by a different transaction. The values will differ in the two reads which should have returned identical information.
Addressing Problem Transactions in Real-Time
DBAs need to have reliable information regarding the flow of transactions in their databases. Precise for Databases can be a valuable tool that provides the data necessary to isolate and tune problem transactions. The application helps monitor and optimize database performance for Microsoft SQL Server, Oracle Database, IBM Db2, and SAP Sybase ASE.
Precise can monitor transactions continuously with low server overhead so the team doesn’t miss any performance issues or trends. Transactions are correlated with specific users, databases, devices, files, and objects to provide DBAs with additional content.
An overview dashboard provides a starting point that can be used to find the root cause of performance problems. Teams can drill down and view the full path a transaction is taking through the infrastructure to identify areas of concern. The tool provides tuning recommendations and enables DBAs to perform what-if analysis to predict the outcomes of prospective changes.
The importance of corporate data resources demands optimally performing databases. Precise offers a tool that assists DBAs to make that idea a reality.