one. concept
In a network environment, the database application is a c/s or multi-layer structure pattern. In this environment, the development of database applications should consider reducing the amount of network data transmission as much as possible and improving concurrency as much as possible. Based on this purpose, a cached data update technology came into being. The general process is: the application extracts the data from the database to the client's buffer, and completes data modification, update, and inserts new data in the buffer. ;After the operation is completed, the data is submitted to the database at one time at a suitable time, which greatly reduces network traffic, reduces the load on the database server, and improves concurrency performance.
It should be said that this is not a very new technology. This technology has been supported in earlier versions of database front-end development tools such as Delphi. However, the author found that some programmers do not pay attention to the rational use of this technology and still stay under the idea of stand-alone applications, resulting in inefficiency or potential errors in the compiled programs. Therefore, it is necessary to summarize the advantages, principles and methods of application of this technology (taking delphi as an example).
two. Pros and cons
Data update technology with cache has the following advantages:
(1) Minimize network traffic and reduce data access time. Improve the efficiency of client database operators.
(2) Reduces the burden on the database server, because many repeated updates, modifications, and deletion operations can be completed in the client's buffer, and the results are only submitted to the server in the end.
(3) Effectively reduce the transaction processing time and reduce the throughput of concurrent transactions. This can better ensure the consistency of the database.
We can give an example to illustrate its superiority: the database operator inserts a data record into the database, but immediately finds that the record does not meet the requirements, so he deletes the data. In this process, if data update technology without buffer is used, the server side will perform one insertion operation and one delete operation, and conduct two round-trip data transmissions on the client and server side, and if such data is used to be with other database tables. There is a cascading relationship, and the cascading update, deletion, recovery and other operations of data must also be taken into account. If the data update method is adopted with cache, these two steps of inverse operations can be completed in the data buffer of the client without any actions to the server side, and no network data transmission and cascading update of data will be generated. operate. This shows the huge advantages of buffered data update technology.
One disadvantage of data update with cache is that because the data is stored on the client, if the data is changed by other users, it will cause inconsistent situations such as loss and modification. It is necessary to consider the details of its concurrency control and fully consider the variables. Inconsistencies in data may occur. The application of this technology requires certain skills and certain thinking changes.
three. Application principles
The advantages of any technology are reflected in a certain environment. The buffered database update technology is mainly used in the following situations:
(1) C/s or multi-layer database application occasions. In this case, network traffic can be effectively reduced. In stand-alone situation, the technology makes no sense.
(2) In the case of data update of multiple tables. For example, in the update of the main table/detail structure, the addition and deletion of the two tables often affect each other. After the client completes all updates of the two tables, a transaction can be defined to submit the done Update operation. This effectively shortens the transaction time and better ensures data consistency.
(3) In occasions where the server load capacity is limited. Nowadays, with the increase in the speed of PCs and the decrease in price, the difference in the capabilities of clients and servers is getting smaller and smaller, and the service capabilities of servers are relatively reduced. Objectively, it is required to reduce the burden on the server from the software perspective, while buffered data updates reduce the burden on the server by sharing part of the update task by the client.
(4) In the case where the probability of data being updated at the same time is relatively low. If the same data in the database is likely to be updated by multiple users in the same period, then this situation is not suitable for cached updates, because in this case, error overwriting of data is easily generated, resulting in inconsistency of data.
Four. Overview of control methods in Delphi
As a popular database development tool, Delphi has rich database manipulation functions. Delphi provides comprehensive support for buffered data access technology. Generally, delphi provides users with several data set controls for accessing database tables such as TTable and TQuery; it provides TDBNevigator controls, which can add, delete, modify, query and other operations on data sets. There is a cachedUpdate option in the property control of the dataset. Set this item to true, delphi promises to access the dataset in a buffered form, which means that the update operations made to the dataset will not be automatically reflected immediately To the database server, only when the actual submitted method (such as applyUpdates(), etc.) is called, delphi reflects the actual submitted data to the database. At the same time, the data set's on UpdateRecord() method is used to define the need to be updated when the database table is actually updated. Simultaneous operations (such as cascading deletion, etc.). This provides convenience for us to control the steps of data submission ourselves. Although this makes programming more difficult, we need such flexibility in some cases. Moreover, through this model, it does greatly reduce the length of transactions, reduce network traffic, and increase the reliability of the application. Below we give a specific application module to illustrate how to use this programming model.
five. Delphi program example
(1) Application background description
Suppose we make a module for product order processing. This module involves three database tables: Order table Order (with fields such as Order ID, Amount SumMoney, Date date, Customer Name Costomer, etc.), Order Detail (with Order Detail ID, Order ID, and Product Number CommondityID), Order Detail (with Order ID, Order ID, and Product Number CommondityID , quantity amount, unit price PRice and other fields), inventory table storage (including product number CommondityID, existing stocks and other fields). Among them, the order and the order list are one-to-many relationships, with the order number OrderID as the connection field. Whenever an order is added, the inventory table must be modified and the quantity of corresponding goods sold is subtracted from the inventory.
(2) Program framework description
The following is a framework for delphi programs, which roughly explains how to use the programming mode of cache updates. Readers can further improve the functions of this program by themselves.
unit Order;
{Unit Name}
interface
uses
{referenced module}
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Grids, DBGrids, ExtCtrls, DBCtrls, ToolWin, ActnMan, ActnCtrls,
ActnMenus, DB, DBTables;
Type
{Declared variables, added controls, and defined methods and procedures}
TOrderForm = class(TForm)
TBOrder: TTable;
TBDetail: TTable;
OrderDB: TDatabase;
ActionMainMenuBar1: TActionMainMenuBar;
DBNavigator1: TDBNavigator;
DBGrid1: TDBGrid;
procedure TBOrderAfterPost(DataSet: TDataSet);
procedure TBDetailNewRecord(DataSet: TDataSet);
procedure TBDetailUpdateRecord(DataSet: TDataSet;
UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
procedure TBDetailAfterPost(DataSet: TDataSet);
procedure FormCreate(Sender: TObject);
Private
{ Private declarations }
public
{ Public declarations }
end;
var
OrderForm: TOrderForm;
Implementation
{$R *.dfm}
{The following is the main program framework}
procedure TOrderForm.FormCreate(Sender: TObject);
{Set the cache update options for the main table and detailed table to true}
Begin
TBOrder.CachedUpdates:=true;
TBDetail.CachedUpdates:=true;
end;
procedure TOrderForm.TBOrderAfterPost(DataSet: TDataSet);
{After submitting the update of the Order table, execute the content of this process. This process implements the actual submitted transactions to the main table and the detailed table.
Note: If the cachedUpdates property of a data set is true,
Then the post action is just a commit action in the client buffer.
Instead of actually submitting it to the actual database. To achieve a true commit,
The applyUpdates statement is required. }
Begin
OrderDB.StartTransaction;//The update transaction starts execution
try
TBOrder.ApplyUpdates;//Please actual update to the main table
TBDetail.ApplyUpdates;//Real updates to the detailed list
except
Orderdb.Rollback;//If an accident occurs, then roll back the transaction and exit the process
exit;
end;
OrderDB.Commit;//If no accident occurs, then complete the transaction commit
TBOrder.commitUpdates;//Clear the client buffer of the TBOrder table
TBDetail.commitUpdates;// Clear the client buffer of the TBDetail table
end;
procedure TOrderForm.TBDetailNewRecord(DataSet: TDataSet);
{The action completed when a detailed list is added. }
Begin
TBDetail.FieldByName('OrderID').AsInteger:=TBOrder.FieldByName('OrderID').AsInteger;
file://assign the orderID field of the main table to the orderID field of the detailed table. This field is the associated field of the two tables.
end;
procedure TOrderForm.TBDetailUpdateRecord(DataSet: TDataSet;
UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
{When the database table is actually updated, the operations that need to be performed simultaneously are defined in the onUpdateRecord event.
In this case, a cascading update operation of the detailed list and inventory table is performed.
Pay attention: The operations performed in this process are actions performed when the database is actually updated.
Instead of the actions performed when updating the client's cached data}
Var temp_query:TQuery;
Begin
if UpdateKind=ukInsert then file://If the update type is to insert a new record, then update the corresponding inventory amount
with temp_query do
Begin
close;
SQL.clear;
SQL.add('update storage set stocks=stocks-:amount');
SQL.add(' where commondityID=:commondityID');
paramByName('amount'):=TBOrder.FieldByName('amount').AsFloat;
ParamByName('commondityID'):=TBDetai.FieldByName('commondityID').AsInteger;
execSQL; file://Execute the SQL statement to update the inventory and subtract the corresponding inventory amount.
end;
end;
procedure TOrderForm.TBDetailAfterPost(DataSet: TDataSet);
{After modifying the records in the detailed table and submitting (post), execute the statements in this process.
Pay attention: This kind of submission is targeted at client data and is not really reflected in the database.
In this example, the implemented function is to calculate the total amount field of the main table}
Begin
TBOrder.FieldByName('money'):=0;
with TBDetail do
Begin
first;
while not eof do
Begin
TBOrder.FieldByName('money'):=TBOrder.FieldByName('money')+
FieldByName('price').AsFloat*FieldByName('amount');
file://Accumulate the amount of the detailed table to the amount field of the main table
next;
end;
end;
end;
end.