Concurrency Control of PowerBuilder

 

Concurrency Control of PowerBuilder

 
Concurrency refers to the ability of multiple users to simultaneously access the same data at the same time. Generally, relational databases have the capability of concurrency control, but such concurrent functions also pose a risk to data consistency. Imagine what would happen if two users tried to access a bank user's record and asked to modify the user's deposit balance at the same time? We can set DataWindow in PowerBuilder for concurrency control.

The so-called concurrency control refers to the way to ensure that the data is not overwritten or changed during the user data modification process. In the following example, we will see how to set DataWindow to control development access. To illustrate the problem, let's take a simple example in the banking system. The deposit status of a user is as follows:

Let us assume that the process is like this: An employee of the company withdraws 2,000 yuan from the bank's front-end, and the bank cashier queries the user's deposit information to show that the bank's deposit balance is 20,000 yuan. At this moment, another bank account transfers a cheque to pay for the account of 5,000 yuan, and the machine query also obtains the current user deposit of 20,000 yuan. At this time, the bank cashier sees that the user deposit has exceeded the receiving amount, the customer paid 2,000 yuan and changed the user deposit to 18,000 yuan. Then another operator of the Bank added the incoming 5,000 yuan according to the check and changed the user balance to 25,000 yuan, can the database management system accept these changes?

In the design of DataWindows, choose Rows Update ..., The Specify Update Characteristics setting window appears. In this window, we set the Where clause generation in the Update statement for development control. Here there are three options. Let's take a look at the results of these three options in this example:

(1) Key Columns: The generated Where clause only compares the values of the primary Key Columns in the table with those in the initial query to determine the record to be modified. In the above example, the transfer check operation will overwrite the modification made by the cashier, so that the bank loses two thousand yuan.

(2) Key and Updateable Columns: The generated Where clause compares the values of primary Key Columns and modifiable Columns in the table with those in the initial query. In the preceding example, the results of both queries have a balance of 20 thousand. When the first person changes the balance, the balance is still 20 thousand yuan. Therefore, the change is valid, and the balance is no longer 20 thousand in the check transfer operation, therefore, the column does not match and the modification fails.

(3) Key and Modified Columns: The Where clause compares the primary Key and Columns to be Modified. In this example, the result is the same as that of Key and Updateable Columns, because the balance has changed, it is no longer the same as the original query, so it cannot be modified.

Let's make another assumption. We will change the check transfer operation in the Bank background to freeze user deposits, that is, change the value of the Status field to freeze, and the order of events is shown in the following table, then the order in the table is 4... Can the front-end cashier's modification be established:

1. Key Columns: The Where clause only compares the primary Key values. Obviously, the cashier's modification is allowed.
2. key and Updateable Columns, the error message is displayed.

3. Key and Modified Columns: the comparison of the Where clause includes the primary Key and the column to be Modified. Because the Modified column in this column remains unchanged at 20,000 yuan, the cashier's modification can be established.

In this example, we can see that the Key and Updateable Columns have the strictest options, which can avoid the error of modifying the balance when the Status column changes, however, this will also prohibit us from making some concurrent modifications as permitted, such as the cashier's account balance modification and the clerk's contact address modification. Therefore, we should select the appropriate Update settings based on the actual situation.

Depending on the database we use, we also have some other options for controlling concurrent access and modification, such as locking data.

A lock is an operation that prevents other users from modifying the specified row. The lock is automatically released when a transaction such as commit, rollback, and disconnect is terminated. If the DBMS you use supports lock operations, you can add withholdlock to the SELECT statement in the from clause during the datawindow Design of Power-builder: that is, in the sqlwindow of datawindow, click the right mouse in the title of the table window. The last option in the pop-up menu is holdlock. Select this option. The generated SQL statement locks the queried data after the retrieve () function is executed to prevent other users from modifying the access until commit,
Events such as rollback are unlocked. The problem with this method is that after the user queries the data, the user may leave the computer for a long time, and other users cannot modify the data during this period. In addition, some DBMS such as Sybase do not support row-level locks. That is to say, when you query a row, more rows are locked, which increases the restrictions of concurrent processing. Another noteworthy problem is that the transaction commit of a window in the Multi-Window application will cause the query row of other data windows in the event to be unlocked, and the modification may cause errors. Some DBMS systems support a data item called "timestamp" to control concurrency. Each table has a timestamp data column. When the insert statement or update statement modifies the data row, the column is automatically modified to the current time. When you want to modify, the where clause can check whether the two values of the timestamp column are consistent during query and modification, so as to ensure that your modifications do not overwrite others' modifications, therefore, this validation method is the same as the key and updateable columns option. Even if the two users modify different columns in the same row, the last modifier fails. In common relational databases, Sybase and Microsoft sqlserver support the use of timestamps. In PowerBuilder, no matter which database the user connects to in the background, Pb will automatically ignore the updatecharacteristics option as long as the column name with timestamp in the table and the data type is datetime, the where clause generates a comparison between the primary key and the timestamp column.

If your database does not support timestamps but triggers, you can add an integer column to the table. When a record in the table is modified, the column is automatically added with 1. The following table uses the Watcom database. Add the Updcnt field to the Shipper table and create two triggers. This field can change when any user or process attempts to modify a row record.

Write the INSERT trigger as follows:
DROP TRIGGERINS-SHIPPER'
CREATE TRIGGER SHIPPER BEFORE INSERT ON SHIPPER
Referencing new as New value
FOR EACH ROW
BEGIN
SET new value. UpdCnt = new value. UpdCnt + 1;
End'

You can also compile an UPDATE trigger.
In your PowerBuilder application, in addition to the primary key of the table, you must add this column as the detection column to the Where clause in the Update statement, the background database compares the data in the modification with the user's Retrieve operation to confirm whether the modification can be performed. In DataWindows, add the Updcnt item to the Uniquekeycolumn (s) in the lower-right corner of the SpecifyUpdateCharacteristics dialog box. Note that Key Columns is selected in whereclause, in this way, PowerBuilder considers Updcnt as the primary key of the table when constructing the where clause and becomes the detection item.

When the Update function of the data window is called, the trigger changes the Updcnt list in the record to a new value. To ensure that the next modification is valid, you should immediately Retrieve () so that the Updcnt value in the DataWindow buffer is the same as that in the database. Obviously, the cost of querying immediately after modification is much lower than that of any other concurrency control.