Database transaction management
In the database, the so-called transaction can be understood to be a set of logical operation units to convert the data from one state to another. To ensure the consistency of data in the database, DML is considered as a discrete queue: from the beginning to it, the data consistency can be kept, and commit can be submitted; while a part of the queue is Operating Fail, then the entire queue It should be regarded as illegal, all the operations from the starting point should be retracted to the start state, ie ROLLBACK.
The operation of the transaction is this: first define a transaction, then modify the data, if you submit (commit), these modifications are permanently saved, if the rollback, the database management system will give up All modifications you have returned to start transactions. In addition, some database supports the "SavePoint" "SavePoint": that is, you can make the current state store in any point in a transaction process, just return to the storage point you set, not Return to all transactions. If your transaction can be divided into several groups of changes to the database, you can set multiple storage points, as needed, you can return to any of the storage points without making all transactions to modify the data.
Correctly managing transactions to ensure data integrity, when you do all the work is completed and given, there is no data to physically write into the database. Let us see such an example:
We have such a bank application system, the front desk user makes the operation under the depositor's one hundred deposits of the depositor; in the database, the records of these two customers are stored in two tables, when the user When making operations on the screen, two tables need to be modified in the background. If you subtract one hundred yuan in the database, the previous table must return to the state before the modification of the operation of the other hundred yuan, the previous table must return to the state before the modification, otherwise the content of the database Do not unify, the household households lose one hundred yuan, and the information is inevitable.
Therefore, transaction management is necessary.
Traditionally, we believe that a transaction includes modifications to one or more tables, and with the development of distributed databases and data warehouses, transactions may include modifications to one or more databases. In the above example, the two users may be a different user, and the information is stored in different databases in different geographies, and one of the above transactions involve the operation of different databases.
Transaction management in PB
PB supports the operation of transaction management as a reception development tool for the database. There is an object called transaction in the PB, and a global transaction object is established at the beginning of the application - SQLCA, which is a communication area that interacts with the database. Since the application is only available for a single database, the general developers mainly use SQLCA as transaction objects connected to the unique database.
The statement of transaction management in PowerScript has four: Commit, Rollback, Connect, disconnect.
When you need to apply a connection with the database, execute disconnect when you cancel the connection, and the two commands generally use the start and end of the application, which is the Open and Close events of the Application.
When the database modification of a transaction is successfully completed, the modification must submit to the database, and the commit statement is an end of an old transaction and a boundary of a new transaction. Before the modification is submitted, the database has not been truly modified, and these modifications are retained in a workspace, and only the modified users can see these modified values, after commit, all users are all You can see the new value. In the process of transaction, some errors are happened or in operation. It is necessary to stop the transaction in operation. You must use the rollback command to return the transaction. If the operation is not written without the rollback command, these operations must be incorrect as the next transaction Part of the decline in the database. If you are using a multi-window application, just an transaction object, you should pay more attention to Rollback and Commits affect the logical consistency of transactions. These two instructions performed in a window will make or fall back in other window applications.
In multi-user systems, the closer the time of modification and submission, the higher the possibility of submitting success. Because all SQL statements in a transaction are all executed, it is entirely possible, for example during your transaction, another user has modified data and submitted, which is likely to make your changes invalid, then Commits will fail, you must fall back to all of this business.
Sqlca's autocmit property
Transaction objects There is an AutoCommit property that allows developers to simplify the operation of transaction management, which can be assigned to TRUE or FALSE. When it is true, the PB automatically implicitly submitted.
Of course, you can set the AutoCommit property as a fake (default), explicitly submit or fall back in keywords such as commit or rollback. In most applications, a part of the database operation is to group committed, while others are not available. So we can use the AutoCommit's characteristics to determine the start of the transaction. When we set the AutoCommit property to true, the system sets the starting point for transactions at this time. When AutoCommit is set to FALSE, the system automatically takes this transaction. So you can set autoCommit to false, when you need to start a transaction, you will be True, which is the transaction start point.
The greatest advantage of this transaction management within PB is convenient. You don't think about the entire transaction, but just submit or roll it back to your modifications. But convenient and controllability is always contradictory, there is no mechanism for storage points and nested transaction management in PB, even if the database you are using, it cannot be embodied in PB. However, in ordinary applications, storage points and nested transactions are not required, and general transaction management is enough.
Dynamic SQL
In some applications, we also need to utilize the characteristics of nested transactions and storage points used, while transaction management within the PB does not provide such functions, and must design itself, that is, the dynamic SQL.
The way you use your own transaction management is the transaction instructions directly using the database itself. When you use your own management, you should stop the PB to stop management, setting up AutoCommit is false, and the system will not be automatically constructed for transaction processing. The way to implement manual transactions is to use the Execute Immediate: LS_SQL this PB command to perform any database operation. What you need to do is editing the database command into a string ls_sql (which can be a metrics, build the primary key, stored procedure, etc., can be used as follows:
Execute Immediate Begin Transaction Trans-Name
Such an instruction starts a transaction. With this approach, as long as the database you have supported supporting the management of nested transactions and storage points, we can also achieve the applications through PB.
The method of transaction management in PB is a variety of diversified, as long as you are flexible, you can design outstanding database applications. Function method of calling method of the background stored process in PB front desk
1. In the foreground, it is necessary to declare a transaction user object, saying that U_TRANS
2. Then use U_TRANS to replace the default Transact
3. Then use the stored procedures you want to use as external functions.
4. Finally, you can call as a function.
note:
Return parameter definition method - Integer: defined as a long
Character: Defined as String
