PB Concurrency Control

Concurrency Control

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 datawindows design, choose rows|Update..., Specify will appear
UpdateCharacteristics settings window, in which 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)KeyColumns: 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
AndUpdateable columns: The generated where clause compares the values of primary key columns and modifiable columns in the table with whether or not they were originally queried. 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
AndModified columns: The WHERE clause compares the primary key and the columns to be modified. In this example
AndThe selection of updateable columns is the same, because the balance has changed and 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.KeyColumns: The WHERE clause only compares the primary key values. Apparently, the change by the cashier is allowed.

2.Key
AndUpdateable columns: The generated where clause includes all the columns that can be modified. Therefore, when the cashier modifies the statue field, the statue field is frozen and the valid value of the cashier query does not match. The modification fails and an error message is displayed.3.Key
AndModified 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 the keyAndThe updateable columns option is the strictest, which can avoid the balance modification error when the Status column changes. However, this will also prevent us from making some concurrent modifications as permitted, for example, the cashier changes the deposit balance, and the clerk changes the contact address of the user. 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. When a transaction ends, such as executing commit, Rollback , Disconnect and other statements automatically release the lock. If the DBMS you are using supports lock operations - During the datawindow Design of builder, the SELECT statement can be added to the from clause
Holdlock : In the SQL window of data window, click the right mouse in the title of the table window, And the last option in the pop-up menu is holdlock. Select this option. The generated SQL statement is - After the trievel () function is executed, lock the queried data to prevent other users from modifying access until events such as commit and 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 ( 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 corresponds to the key
And The updateable columns option is the same. Even if the two users modify different columns in the same row, the last modifier fails. In common relational databases, Sybase and Microsoft SQL server support the use of timestamps. In PowerBuilder, no matter which database the user is connected to in the background, Pb will automatically ignore the update characteristics 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 TriggerShipper before
Insert
OnShipper

Referencing newAsNewvalue

ForEach row

Begin

SetNewvalue. updcnt=Newvalue. 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 unique key column (s) in the lower-right corner of the specify update characteris-tics dialog box. Note that key columns is selected in where clause, 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 concurrent sending control.