CN112416977B - Method, device and equipment for data transmission applied to database - Google Patents
Method, device and equipment for data transmission applied to database Download PDFInfo
- Publication number
- CN112416977B CN112416977B CN202011300687.2A CN202011300687A CN112416977B CN 112416977 B CN112416977 B CN 112416977B CN 202011300687 A CN202011300687 A CN 202011300687A CN 112416977 B CN112416977 B CN 112416977B
- Authority
- CN
- China
- Prior art keywords
- transaction
- client
- execution
- result
- sent
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 67
- 230000005540 biological transmission Effects 0.000 title claims abstract description 36
- 238000004590 computer program Methods 0.000 claims description 3
- 230000008569 process Effects 0.000 description 17
- 238000010586 diagram Methods 0.000 description 14
- 230000008859 change Effects 0.000 description 7
- 230000009471 action Effects 0.000 description 3
- 238000004458 analytical method Methods 0.000 description 3
- 238000004364 calculation method Methods 0.000 description 2
- 230000000694 effects Effects 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 2
- 230000007246 mechanism Effects 0.000 description 2
- 230000006872 improvement Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000011084 recovery Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
-
- H—ELECTRICITY
- H04—ELECTRIC COMMUNICATION TECHNIQUE
- H04L—TRANSMISSION OF DIGITAL INFORMATION, e.g. TELEGRAPHIC COMMUNICATION
- H04L67/00—Network arrangements or protocols for supporting network services or applications
- H04L67/01—Protocols
Abstract
The present disclosure provides a data transmission method, apparatus, device and computer readable storage medium applied to a database. The method comprises the following steps: the method comprises the steps that a server receives a first execution command sent by a client, wherein the first execution command comprises a batch return value, the first execution command is used for indicating the server to execute a first transaction, if the current mode is an automatic submission mode and the first transaction is a single sentence transaction, the first transaction is executed, an execution result is obtained, and the first execution result is sent to the client according to the batch return value. For the single sentence transaction in the batch submission mode, the server does not need to send all result data to the client at one time, but can send the first execution result according to the batch return value sent by the client, so that the data can be transmitted by using a smaller network bandwidth, the pressure of transmission resources is reduced, and the transmission performance is improved.
Description
Technical Field
The present disclosure relates to the field of database technologies, and in particular, to a method, an apparatus, a device, and a computer readable storage medium for data transmission applied to a database.
Background
With the rapid development of computer technology, databases are being applied more and more widely, developers are more and more, and databases are being developed towards more user-friendly directions.
When the database system executes the structured query language (Structured Query Language, abbreviated as SQL) statement, if the client designates the batch return value fetchsize of the SQL statement, the server sends the result data to the client in batches according to the batch return value. However, in the auto commit mode in the object-relational postgres database, the server sends the result data to the client all at once, even though the client specifies a bulk return value for the SQL statement.
Thus, the automatic commit mode will result in the transmission of resultant data that occupies a significant amount of network bandwidth.
Disclosure of Invention
To solve or at least partially solve the above technical problems, the present disclosure provides a method, apparatus, device, and computer-readable storage medium for data transmission applied to a database.
In a first aspect, the present disclosure provides a method for data transmission applied to a database, applied to an object-relational database, the method comprising:
Receiving a first execution command sent by a client, wherein the first execution command comprises a batch return value, and the first execution command is used for indicating a server to send a first execution result of the first transaction;
if the current mode is an automatic submitting mode and the first transaction is a single sentence transaction, executing the first transaction to obtain an executing result;
and sending a first execution result to the client according to the batch return value.
Optionally, the execution result includes at least one piece of result data, and the executing the first transaction to obtain an execution result includes:
executing the first transaction until the amount of resulting data is equal to the bulk return value,
or,
until the first transaction is executed, and the quantity of the obtained result data is smaller than a batch return value; and the first execution result is the obtained result data.
Optionally, before sending the first execution result to the client according to the batch return value, the method further includes:
judging whether all result data of the first transaction are obtained currently;
ending the first transaction if all the result data of the first transaction are obtained currently;
And if the result data of all the first transactions are not obtained currently, waiting for receiving a second execution command sent by the client, wherein the second execution command is used for indicating the server to continue to send the second execution result of the first transactions according to the batch return value.
Optionally, the executing the first transaction, to obtain an execution result, includes:
executing the first transaction to obtain all result data of the first transaction, wherein the number of the result data of all the first transaction is greater than or equal to the first execution result;
before the first execution result is sent to the client, the method further comprises:
judging whether to send all the result data of the first transaction to a client after sending the first execution result according to the number of the result data of all the first transactions and the number of the first execution results;
if all the result data of the first transaction are sent to the client after the first execution result is sent, ending the first transaction;
and if not all the result data of the first transaction are currently sent to the client, waiting for receiving a second execution command sent by the client, wherein the second execution command is used for indicating the server to continue sending the second execution result of the first transaction according to the batch return value.
Optionally, the method further comprises:
and if a third execution command sent by the client is received, storing the result data of the first transaction which is not sent to the client, wherein the third execution command is used for indicating the server to execute a second transaction.
Optionally, after storing the result data of the first transaction that is not sent to the client, the method further includes:
receiving a fourth execution command sent by the client, wherein the fourth execution command is used for indicating the server to send a third execution result of the first transaction according to a batch return value;
obtaining a third execution result according to the batch return value and the stored result data of the first transaction which is not sent to the client;
and sending the third execution result to the client.
Optionally, the method further comprises:
and receiving an ending command of the first transaction sent by the client, and submitting the first transaction.
In a second aspect, the present disclosure provides an apparatus for data transmission applied to a database, the apparatus for application to an object-relational database, the apparatus comprising:
the receiving module is used for receiving a first execution command sent by the client, wherein the first execution command comprises a batch return value, and the first execution command is used for indicating a server to send a first execution result of the first transaction;
The obtaining module is used for executing the first transaction if the current mode is an automatic submitting mode and the first transaction is a single sentence transaction, so as to obtain an execution result;
and the sending module is used for sending a first execution result to the client according to the batch return value.
Optionally, the execution result includes at least one piece of result data, and the obtaining module is specifically configured to:
executing the first transaction until the amount of resulting data is equal to the bulk return value,
or,
until the first transaction is executed, and the quantity of the obtained result data is smaller than a batch return value; and the first execution result is the obtained result data.
Optionally, the apparatus further includes:
the judging module is used for judging whether all result data of the first transaction are obtained currently;
ending the first transaction if all the result data of the first transaction are obtained currently;
and if the result data of all the first transactions are not obtained currently, waiting for receiving a second execution command sent by the client, wherein the second execution command is used for indicating the server to continue to send the second execution result of the first transactions according to the batch return value.
Optionally, the obtaining module is specifically configured to:
executing the first transaction to obtain all result data of the first transaction, wherein the number of the result data of all the first transaction is greater than or equal to the first execution result;
the apparatus further comprises:
judging whether to send all the result data of the first transaction to a client after sending the first execution result according to the number of the result data of all the first transactions and the number of the first execution results;
if all the result data of the first transaction are sent to the client after the first execution result is sent, ending the first transaction;
and if not all the result data of the first transaction are currently sent to the client, waiting for receiving a second execution command sent by the client, wherein the second execution command is used for indicating the server to continue sending the second execution result of the first transaction according to the batch return value.
Optionally, the apparatus further includes:
and the storage module is used for storing the result data of the first transaction which is not sent to the client if a third execution command sent by the client is received, wherein the third execution command is used for indicating the server to execute a second transaction.
Optionally, the receiving module is further configured to: receiving a fourth execution command sent by the client, wherein the fourth execution command is used for indicating the server to send a third execution result of the first transaction according to a batch return value;
the obtaining module is further configured to: obtaining a third execution result according to the batch return value and the stored result data of the first transaction which is not sent to the client;
the sending module is further configured to: and sending the third execution result to the client.
Optionally, the receiving module is further configured to: and receiving an ending command of the first transaction sent by the client, and submitting the first transaction.
In a third aspect, the present disclosure provides a data transmission apparatus applied to a database, comprising:
a memory for storing processor-executable instructions;
a processor for implementing the method as described in the first aspect above when the computer program is executed.
In a fourth aspect, the present disclosure provides a computer-readable storage medium having stored therein computer-executable instructions which, when executed by a processor, are adapted to carry out a method for data transmission applied to a database as described in the first aspect above.
Compared with the prior art, the technical scheme provided by the embodiment of the disclosure has the following advantages:
the method comprises the steps that a server receives a first execution command sent by a client, wherein the first execution command comprises a batch return value, the first execution command is used for indicating the server to execute a first transaction, if the current mode is an automatic submission mode and the first transaction is a single sentence transaction, the first transaction is executed, a first execution result is obtained according to the batch return value, and the first execution result is sent to the client. For the single sentence transaction in the batch submission mode, the server does not need to send all result data to the client at one time, but can send the first execution result according to the batch return value sent by the client, so that the data can be transmitted by using a smaller network bandwidth, the pressure of transmission resources is reduced, and the transmission performance is improved.
Drawings
The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the disclosure and together with the description, serve to explain the principles of the disclosure.
In order to more clearly illustrate the embodiments of the present disclosure or the solutions in the prior art, the drawings that are required for the description of the embodiments or the prior art will be briefly described below, and it will be obvious to those skilled in the art that other drawings can be obtained from these drawings without inventive effort.
FIG. 1 is a schematic diagram of a database system;
FIG. 2 is a schematic diagram of a transaction state machine executing SQL statements;
FIG. 3 is a schematic diagram of an execution flow of a single sentence transaction in an automatic commit mode provided by the present disclosure;
FIG. 4 is a schematic diagram of a transaction state machine for executing SQL statements provided by the present disclosure;
fig. 5 is a flowchart of a method for data transmission applied to a database according to an embodiment of the present disclosure;
FIG. 6 is a flowchart of another method for data transmission applied to a database according to an embodiment of the present disclosure;
FIG. 7 is a schematic diagram of an execution flow of a single sentence transaction in an automatic commit mode provided by the present disclosure;
fig. 8 is a schematic structural diagram of an apparatus for data transmission applied to a database according to an embodiment of the present disclosure;
fig. 9 is a schematic structural diagram of an apparatus for data transmission applied to a database according to an embodiment of the present disclosure.
Detailed Description
In order that the above objects, features and advantages of the present disclosure may be more clearly understood, a further description of aspects of the present disclosure will be provided below. It should be noted that, without conflict, the embodiments of the present disclosure and features in the embodiments may be combined with each other.
In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present disclosure, but the present disclosure may be practiced otherwise than as described herein; it will be apparent that the embodiments in the specification are only some, but not all, embodiments of the disclosure.
The terms involved in the present disclosure will be explained first.
Transaction (transaction): refers to a sequence of database operations that access and potentially manipulate various data items, either all or none, and is an indivisible unit of work. A transaction consists of all database operations performed between the beginning of the transaction and the end of the transaction.
The structured query language (Structured Query Language, SQL for short) refers to a database query and programming language for accessing data and querying, updating and managing database systems.
Fig. 1 is a schematic architecture diagram of a database system applied to a database, and in the following, a specific application scenario of the present invention will be described with reference to fig. 1, where, as shown in fig. 1, the database system includes a client and a server, and the client is connected to the server, where, one or more clients may be provided, one database may be a server, and a database may also be a database cluster formed by multiple servers, which is not limited in this application.
In the query scene of the postgres database, the client sends a query command to the server, wherein the query command comprises SQL sentences and command sets for processing the SQL sentences, and the server correspondingly processes the SQL sentences in the received query command according to the command sets sent by the client. The command set that processes the SQL statement may include, but is not limited to, one or more of the following: resolving commands, binding commands, interpreting commands, executing commands and synchronizing commands, wherein the resolving commands are used for indicating a server to analyze SQL sentences in terms of morphology, grammar and semantics; the binding command is used for indicating the binding parameters of the server and determining an execution plan; the interpretation command is used for explaining to the server that the description information of the tuple needs to be returned, for example, the description information of the tuple can be a list of target columns, a corresponding type, and the like; the execution command is used for indicating the server to execute the statement and returning the result data; the synchronization command is used to instruct the server to close the current transaction. For the commands in the command set, the client needs to send the commands to the server according to the sequence of the analysis command, the binding command, the interpretation command, the execution command and the synchronization command, wherein the client can send one or more commands to the server at a time.
Fig. 2 is a schematic diagram of a transaction state machine for executing an SQL statement, as shown in fig. 2, where in fig. 2, the states 201-208 represent different states of a transaction respectively, the conditions 1-7 represent respective state switching conditions, where condition 1 is a start transaction command, condition 2 is a commit transaction command, condition 3 is an abort current transaction, condition 4 is a start transaction block, condition 5 is an end transaction block, condition 6 is a user abort transaction block, condition 7 is an abort any transaction, a connection line between the states of the transaction represents that the transaction can be switched from one state to another, conditions 1-7 on a straight line represent state switching conditions, that is, a condition command represented by the condition label is received, the states are switched, and where if one of the conditions is met, the states can be switched, and the transaction block indicated by an arrow is another state to which the transaction block is switched. Where transaction block_start 201 indicates a start transaction block, transaction_start 202 indicates a start transaction, transaction block_default 203 indicates a recovery to a default state after completion of execution of a transaction, transaction block_in-process 204 indicates a current transaction is being executed, transaction block_abort 205 indicates a state in which the current transaction block is aborted, transaction block_end 206 indicates a current transaction block end, transaction block_abort_end 207 indicates a state in which the current transaction block has ended aborted, and transaction block_abort_wait 208 indicates a state in which the current transaction block has entered into an end after aborted.
When executing a transaction of an SQL statement, the state change of the transaction generally begins from transaction block_default 203 until transaction block_default 203 ends, and in a non-automatic commit mode, the state change process of the transaction generally begins from transaction_start 202, then switches to transaction block_start 201, then goes through an intermediate process (which may include transaction block_in-process 204, transaction block_abort 205, transaction block_end 206, and/or transaction block_abort_end 207), and finally to the state change to transaction block_default 203. In auto commit mode, the state change process for a transaction generally begins from transaction_Start 202 to state change to transaction block_Default 203, wherein if an abort command is received, a switch is made to transaction block_abort_wait 208, the final state change is transaction block_Default 203, and the entire transaction block ends.
In the database, a user can set a batch return value fetchsize of the SQL sentence at the client, the batch return value is sent to the server together when the client sends an execution command to the server, the server executes a first transaction after receiving the execution command sent by the client to obtain result data, and the server distributes and sends the result data to the client in batches according to the batch return value.
However, in the object-relational postgres database, in a single sentence transaction in the automatic commit mode, when the server sends an execution result to the client, all result data is sent to the client at a time, and then the transaction is committed, that is, even if the user sets a batch return value of an SQL statement at the client, the client will not send the batch return value to the server, or even if the client sends the batch return value to the server, the execution mechanism of the server cannot process the batch return value, and the server still sends all result data to the client all at once according to the original mechanism, however, this occupies a larger network bandwidth to transmit the result data, and reduces transmission performance.
The method for transmitting data aims to solve the technical problems in the prior art. The method comprises the steps that a server receives a first execution command which is sent by a client and contains a batch return value, if the current mode is an automatic submitting mode and a first transaction is a single sentence transaction, the first transaction is executed, an execution result is obtained, and the first execution result is sent to the client according to the batch return value. Therefore, in the automatic submission mode in the postgres database, the server can send the first execution result to the client according to the batch return value sent by the client, so that the data can be transmitted by using smaller network bandwidth, the pressure of transmission resources is reduced, and the transmission performance is improved.
Fig. 3 is a schematic diagram of an execution flow of a single sentence transaction in an automatic commit mode provided in the present disclosure, as shown in fig. 3, after receiving different command messages according to a time sequence, corresponding steps of the command messages are executed.
Fig. 4 is a schematic diagram of a transaction state machine for executing an SQL statement provided in the present disclosure, as shown in fig. 4, fig. 4 is an improvement made on the basis of the state machine shown in fig. 2, and a state transaction block_batch return 209 is added, and a condition 8 and a condition 9 are correspondingly added, where the condition 8 is a start batch send transaction, and the condition 9 is an end batch send transaction. 201-209 respectively represent different states of the transaction, conditions 1-9 respectively represent respective state switching conditions, a connection line between states of the respective transaction represents switching from one state to another state, any one or more reference numerals between 1-9 on a straight line represent the state switching conditions, and a transaction block indicated by an arrow is the state to be switched. Transaction block bulk return 209 indicates that the first execution result of the transaction is sent to the client according to the bulk return value, the server determines whether all the result data of the first transaction is sent to the client after sending the result data to the client each time before sending the execution result to the client, if all the result data of the first transaction is not sent to the client, the state remains in transaction block bulk return 209, and if all the result data of the first transaction is sent to the client, the state switches to transaction block end 206. In addition, the status of transaction block_bulk return 209 is increased, thus increasing the instances of transactions such as interrupts, leaving the transaction state machine intact.
After the server receives the parse command, in a transaction state change 301 step, the current transaction state is changed to transaction_start 202 shown in fig. 2. After the server receives the execution command, in the step of changing the transaction state 302, before the server returns the result data to the client according to the batch return value, it may be determined whether all the result data of the current transaction has been sent to the client after the result data is sent to the client, if all the result data of the current transaction has not been sent to the client, the current transaction state is changed to the batch return processing flow, and the transaction state is switched to the transaction block_batch return 209.
The following describes the technical solutions of the present disclosure and how the technical solutions of the present application solve the above technical problems in detail with specific embodiments.
Fig. 5 is a flowchart of a method for data transmission applied to a database according to an embodiment of the present disclosure, as shown in fig. 5, where the method of the present embodiment is executed by a server, and the method of the present embodiment is as follows:
s501, receiving a first execution command sent by a client.
The first execution command comprises a batch return value, and the first execution command is used for indicating the server to send a first execution result of the first transaction.
When the client has a query task, the client sends a query command to the server, wherein the query command may include an analysis command, and after the client performs analysis on the SQL statement, all or each command that needs to be further executed by the server is sent to the server, and the further executed command may include, but is not limited to: binding a command, interpreting the command, and executing the command; the query command may also include a first execution command.
The first execution command includes a batch return value, where the batch return value is used to instruct the server to send the result data to the client according to the batch return value, for example, if the batch return value is 5, the server may send 5 pieces of result data to the client.
S502, judging whether the current mode is an automatic submitting mode, and the first transaction is a single sentence transaction.
A single statement transaction is used for dividing an SQL statement into a transaction for processing, and the current transaction is submitted after the execution of the SQL statement is completed. The server detects the current mode and the nature of the first transaction, determines whether the current mode is an automatic commit mode, and whether the first transaction is a single sentence transaction, if the current mode is not the automatic commit mode, and/or if the first transaction is not the single sentence transaction, the processing is performed according to the existing flow, the corresponding server may switch states according to the state machine shown in fig. 4, and if the current mode is the automatic commit mode, and if the first transaction is the single sentence transaction, the execution continues to S503.
S503, executing the first transaction to obtain an execution result.
S504, according to the batch return value, sending a first execution result to the client.
In one possible implementation manner, the first transaction is executed to obtain result data of all first transactions, where the result data of all first transactions is all result data obtained by executing the first transaction, and the number of the result data of all first transactions is greater than or equal to the first execution result. And obtaining a first execution result according to the batch return value.
If the result data of all the first transactions is 0, that is, the first transaction is completed and the result data is not obtained, the first execution result is that the first transaction has no result data, and the first execution result is sent to the client.
If the number of result data of all the first transactions is not 0 and is less than or equal to the batch return value, the first execution result contains all the result data. For example, assuming that the server executes the first transaction, the total obtained result data is 10 pieces, and the batch return value is 20, the first execution result is the total 10 pieces of result data, and the first execution result is sent to the client.
If the number of result data of all the first transactions is greater than the batch return value, the first execution result includes the result data of the batch return value number. For example, assuming that the batch return value is 20, if the server executes the first transaction and the total result data obtained is 40, 20 result data out of the 40 result data are included in the first execution result.
In another possible implementation manner, the server executes the first transaction according to the batch return value to obtain result data with the quantity less than or equal to the batch return value, where the first execution result includes the obtained result data.
Optionally, the first transaction is executed until the amount of resulting data is equal to the bulk return value,
or,
until the first transaction is completed and the amount of resulting data is less than the bulk return value.
For example, in the process of executing the first transaction, the obtained result data may be counted, and each time one piece of result data is obtained, the count value is incremented until the count value reaches the batch return value, execution of the first transaction is suspended, and the result data obtained at this time is formed into the first execution result. And if the quantity of the result data of all the first transactions obtained after the first transactions are executed in the executing process is smaller than the batch return value, the result data of all the first transactions are contained in the first execution result.
After the server obtains the first execution result, if the client side does not request the result data of the first transaction, the server can not continue to execute the first transaction, so that the CPU consumption of the server is reduced, and the calculation processing resource of the server is saved.
In this embodiment, the server receives a first execution command sent by the client, where the first execution command includes a batch return value, where the first execution command is used to instruct the server to execute a first transaction, and if the current mode is an automatic commit mode and the first transaction is a single sentence transaction, execute the first transaction, obtain a first execution result according to the batch return value, and send the first execution result to the client. For the single sentence transaction in the batch submission mode, the server does not need to send all result data to the client at one time, but can send the first execution result according to the batch return value sent by the client, so that the data can be transmitted by using a smaller network bandwidth, the pressure of transmission resources is reduced, and the transmission performance is improved.
On the basis of the above embodiment, further, after step S504, the method further includes:
it is determined whether or not the result data of all the first transactions are currently transmitted to the client.
In one possible implementation manner, if executing the first transaction obtains the result data of all the first transactions, it is determined whether the result data of all the first transactions is currently sent to the client according to the number of the result data of all the first transactions and the number of the first execution results. For example, if the number of result data of all the first transactions is the same as the number of first execution results, it is determined that the result data of all the first transactions has been currently sent to the client, the server may end the first transaction, and the transaction state switches to transaction block_end 206 shown in fig. 4. If the number of the result data of all the first transactions is greater than the number of the first execution results, it is determined that the result data of all the first transactions are not yet sent to the client, and the first transactions are not submitted, that is, the first transactions are not finished, the transaction state is switched to the transaction block_batch return 209 shown in fig. 4, and the server continues to wait for the client to send a second execution command, where the second execution command is used to instruct the server to continue to send the second execution result of the first transactions according to the batch return value.
In another possible implementation manner, if the first transaction is executed, and the result data with the batch return value number is obtained, execution of the first transaction is suspended, whether all the result data of the first transaction are sent to the client after the result data of the first transaction is sent at this time is judged, and then the obtained result data is formed into a first execution result and sent to the client. For example, it may be determined whether to send the result data of all the first transactions to the client according to whether the current first transaction is executed, if the first transaction is not executed, the result data of all the first transactions are not sent to the client currently (where there is a case that the result data may not be obtained by the first transaction that is not executed yet, at this time, the server may consider that the result data of all the first transactions are not sent to the client currently), and the server does not commit the first transaction, that is, does not end the first transaction, and the transaction state is switched to the transaction block_batch return 209 shown in fig. 4. If the first transaction has been completed by execution, then the result data for all of the first transaction has been sent to the client, the server may end the first transaction and the transaction state switches to transaction block_end 206 shown in FIG. 4.
Further, after S504, if the result data of all the first transactions are not yet sent to the client, the first transactions are not finished, and when the client sends a command for requesting data again, the second execution result is sent to the client according to the batch return value and the result data of the first transactions that are not sent to the client. The following is a detailed description of the embodiment shown in fig. 6.
Fig. 6 is a flowchart of another method for data transmission applied to a database according to an embodiment of the present disclosure, and fig. 6 is a flowchart of another method for data transmission applied to a database according to an embodiment of fig. 5, further, as shown in fig. 6, if all the result data of the first transaction are not yet sent to the client, S505, S506, and S507 may further include after S504:
s505, receiving a second execution command sent by the client.
The second execution command is used for indicating the server to continue to send the second execution result of the first transaction according to the batch return value.
S506, obtaining a second execution result according to the batch return value and the result data of the first transaction which is not sent to the client.
S507, sending a second execution result to the client.
If the number of the result data of the first transaction may be greater than the batch return value, in S504, the first execution result sent in S504 does not include all the result data of the first transaction, and when the client sends the second execution command each time, the result data of the first transaction that is not currently sent to the client is returned according to the batch return value, so as to obtain a second execution result, and then sent to the client.
For example, the batch return value is 5, after the server completes the first transaction, 20 pieces of result data are obtained, then 5 pieces of result data in the 20 pieces of result data are sent to the client, and after each time the second execution command sent by the client is received, the 5 pieces of result data which are not yet sent to the client are continuously sent to the client.
For another example, if the batch return value is 5, the server may send 5 pieces of result data to the client during the process of executing the first transaction, and then, after receiving the second execution command sent by the client each time, continue to execute the first transaction to obtain 5 pieces of result data, and then send the 5 pieces of result data to the client. It will be appreciated that if after the server has sent the 10 pieces of result data (2 times), the server does not continue to execute the first transaction if the client does not send a command to return the first execution result.
In this embodiment, after receiving the second execution command of the client, the server may send the second execution result according to the batch return value, and if the result data of the first transaction needs to be sent to the client only multiple times, the server sends the result data according to the second execution command sent by the client, so that the data can be transmitted by using a smaller network bandwidth, thereby reducing the pressure of transmission resources and improving the transmission performance.
On the basis of the above embodiment, further, after step S507, the method further includes:
it is determined whether or not the result data of all the first transactions are currently transmitted to the client.
The server may end the first transaction if the result data of all the first transactions have been sent to the client. If the result data of all the first transactions are not currently sent to the client, the first transaction is not committed, that is, the first transaction is not ended, and the transaction state stays at transaction block_bulk return 209 shown in fig. 4.
In some scenarios, the server needs to stop sending the result data of the first transaction to the client during the execution of the above steps, i.e. when the first transaction has not yet ended.
In a possible scenario, if a third execution command sent by the client is received, stopping sending the result data of the first transaction to the client in batches, and storing the result data of the first transaction which is not sent to the client, where the third execution command is used to instruct the server to execute the second transaction.
If, during the process of executing the first transaction by the server and sending the result data according to the batch return value, the third execution command of the second transaction of the new statement sent by the client is received at this time, the client needs to store the result data of the first transaction that has not yet been sent to the client, and the transaction state switches to the transaction block_end 206 shown in fig. 4. If the first transaction is executed at the moment, directly storing result data of the first transaction which is not sent to the client; if the first transaction is not executed at this time, the execution of the first transaction is continued until after the execution of the first transaction is completed, result data of the first transaction which is not transmitted to the client is stored.
Alternatively, the server may begin executing the second transaction while or after the first transaction is committed while the result data of the first transaction that was not sent to the client is stored, the transaction state switches to transaction block_default 203 shown in FIG. 4.
Optionally, after storing the result data of the first transaction that is not sent to the client, the method may further include: and receiving a fourth execution command sent by the client, wherein the fourth execution command is used for indicating the server to continue sending the third execution result of the first transaction according to the batch return value. And according to the batch return value and the stored result data of the first transaction which is not sent to the client, obtaining a third execution result, and sending the third execution result to the client.
After storing the result data of the first transaction that is not sent to the client, the stored result data of the first transaction that is not sent to the client may be sent according to the fourth execution command before the statement of the first transaction is closed. The case of receiving the fourth execution command includes, but is not limited to, the following:
in the first case, in the process of executing the second transaction;
secondly, after the second transaction is executed and submitted;
and in the third case, executing other transactions except the second transaction.
Optionally, after storing the result data of the first transaction that is not sent to the client, the result data of the first transaction that is not sent to the client may be deleted after the result data of the first transaction that is not sent to the client is sent to the client, or a duration of storing the unsent result data may be set, after reaching the duration of storing the unsent result data, the result data of the unsent first transaction may be deleted, or a space size or a number of storing the unsent result data may be set, and when reaching the set space size or number of the result data, the result data may be deleted according to a stored time sequence, or the result data of the first transaction that is not sent to the client may be deleted in other manners.
Optionally, after S503, the method further includes: setting a batch return flag bit, where the batch return flag bit is used to indicate that the server is currently executing the first transaction and enters into a batch return flow, that is, before the server returns the result data to the client according to the batch return value, it may be determined whether to send all the result data of the current transaction to the client after the result data is sent to the client at this time, and if all the result data of the current transaction is not sent to the client, the batch return flag bit is set, and the transaction state is switched to the transaction block_batch return 209 as shown in fig. 4. Accordingly, after S504, after submitting the first transaction, further comprising: and deleting the set batch return flag bits.
When the server receives a third execution command of the SQL sentence, checking whether a batch return flag bit is set currently, executing the third execution command if the batch return flag bit is not set, and if the batch return flag bit is set, indicating that the first transaction is currently being executed, storing result data of the first transaction which is not sent to the client, and then executing a second transaction corresponding to the SQL sentence. By setting the batch return flag bit, when the server receives a new statement, whether the current server is executing the first transaction or not can be quickly determined through the batch return flag bit, and whether the first transaction is in the process of returning the sending result data in batches, namely, the sending of the result data of the first transaction is not completed completely.
In this embodiment, in the process of sending the first execution result to the client in batches, if a third execution command sent by the client is received, the server stops sending the result data of the first transaction to the client in batches, and stores the result data of the first transaction that is not sent to the client, where the third execution command is used to instruct the server to execute the second transaction. When the client requests the result data of the first transaction next time, the server directly returns the value according to the batch without executing the first transaction, and the stored result data which is not sent to the client is sent to the client, so that the CPU consumption of the server is reduced, and the calculation processing resource of the server is saved.
In another possible scenario, if an end command of the first transaction sent by the client is received, sending of the first execution result of the first transaction to the client is stopped.
If the server receives the end command of the first transaction sent by the client in the process of sending the result data of the first transaction according to the batch return value, stopping sending the first execution result of the first transaction to the client in batches, and submitting the first transaction.
In this embodiment, the server receives the end command of the first transaction sent by the client, stops sending the result data of the first transaction to the client, and ensures that the server executes the corresponding steps according to the instruction command of the client.
Optionally, the server executes the first transaction according to the batch return value until the number of obtained result data is equal to the batch return value, the server does not continue to execute the first transaction, and continues to execute the first transaction after receiving the return command sent by the client again, so that after receiving the end command of the first transaction sent by the client, the first transaction is ended, for example, 1 ten thousand pieces of all result data of the first transaction, the batch return value is 5, the server executes the first transaction until 5 pieces of result data are obtained, and does not continue to execute the first transaction, and sends the 5 pieces of obtained result data to the client, and if the client only needs to use 5 pieces of result data at this time, the client sends the end command of the first transaction to the server, and the server does not need to continue executing the first transaction which has not been executed yet, but directly ends the first transaction. Thus, the server realizes the execution on demand, and the CUP resources of the server are saved.
In the following, description will be given of the execution flow of the single sentence transaction in the automatic commit mode provided by the present disclosure with reference to fig. 4 and fig. 7, and fig. 7 is a schematic diagram of the execution flow of the single sentence transaction in the automatic commit mode provided by the present disclosure, as shown in fig. 7, when the transaction state is switched to the transaction block_batch return 209 according to the time sequence, a batch return flag bit is set, after starting the first transaction, if a new sentence is received, the server determines whether the batch return flag bit exists, if the batch return flag bit is set, the result data of the first transaction that is not sent to the client is stored, and then the first transaction is committed. After each time the server sends the result data to the client, it determines whether the result data of the current first transaction has been sent to the client in its entirety, if so, the first transaction is submitted, and if not, the current interface portal for transmitting data with the client is required to be saved, so that the result data of the first transaction that is not sent to the client can be sent to the client continuously.
Fig. 8 is a schematic structural diagram of an apparatus for data transmission applied to a database according to an embodiment of the present disclosure, where, as shown in fig. 8, the apparatus provided in the embodiment is applied to an object-relational database, and includes:
A receiving module 801, configured to receive a first execution command sent by a client, where the first execution command includes a batch return value, and the first execution command is used to instruct a server to send a first execution result of a first transaction;
an obtaining module 802, configured to execute the first transaction if the current mode is an automatic commit mode and the first transaction is a single sentence transaction, and obtain an execution result;
and the sending module 803 is configured to send the first execution result to the client according to the batch return value.
Optionally, the execution result includes at least one piece of result data, and the obtaining module is specifically configured to:
the first transaction is executed until the amount of resulting data is equal to the bulk return value,
or,
until the first transaction is executed, and the quantity of the obtained result data is smaller than the batch return value; the first execution result is obtained result data.
Optionally, the apparatus further comprises:
the judging module is used for judging whether all result data of the first transaction are obtained currently;
ending the first transaction if all the result data of the first transaction are obtained currently;
and if the result data of all the first transactions are not obtained currently, waiting for receiving a second execution command sent by the client, wherein the second execution command is used for indicating the server to continue to send the second execution result of the first transactions according to the batch return value.
Optionally, the obtaining module 802 is specifically configured to:
executing the first transaction to obtain all result data of the first transaction, wherein the number of the result data of all the first transaction is greater than or equal to the first execution result;
the apparatus further comprises:
judging whether to send the result data of all the first transactions to the client after sending the first execution result according to the number of the result data of all the first transactions and the number of the first execution results;
if the result data of all the first transactions are sent to the client after the first execution result is sent at this time, ending the first transactions;
and if the result data of all the first transactions are not sent to the client at present, waiting for receiving a second execution command sent by the client, wherein the second execution command is used for indicating the server to continue to send the second execution result of the first transactions according to the batch return value.
Optionally, the apparatus further comprises:
and the storage module is used for storing the result data of the first transaction which is not sent to the client if a third execution command sent by the client is received, wherein the third execution command is used for indicating the server to execute the second transaction.
Optionally, the receiving module is further configured to: receiving a fourth execution command sent by the client, wherein the fourth execution command is used for indicating the server to send a third execution result of the first transaction according to the batch return value;
The resulting module is also for: obtaining a third execution result according to the batch return value and stored result data of the first transaction which is not sent to the client;
the sending module is further configured to: and sending the third execution result to the client.
Optionally, the receiving module is further configured to: and receiving an ending command of the first transaction sent by the client, and submitting the first transaction.
The device of the above embodiment may be used to implement the technical solution of the above method embodiment, and its implementation principle and technical effects are similar, and are not repeated here.
Fig. 9 is a schematic structural diagram of an apparatus for data transmission applied to a database according to an embodiment of the present disclosure, where, as shown in fig. 9, the apparatus provided in this embodiment includes:
a memory 901 for storing processor executable instructions;
a processor 902 for implementing a method of any of the data transmission described above when the computer program is executed.
The apparatus of the foregoing embodiment may be used to execute the technical solutions of the foregoing method embodiments, and implementation principles and technical effects are similar, and are not repeated herein.
Embodiments of the present disclosure provide a computer-readable storage medium having stored therein computer-executable instructions that, when executed by a processor, perform a method for performing any one of the data transmissions described above.
It should be noted that in this document, relational terms such as "first" and "second" and the like are used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Moreover, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises an element.
The above is merely a specific embodiment of the disclosure to enable one skilled in the art to understand or practice the disclosure. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the disclosure. Thus, the present disclosure is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
Claims (10)
1. A method for data transmission applied to a database, characterized in that it is applied to an object-relational database, the method comprising:
receiving a first execution command sent by a client, wherein the first execution command comprises a batch return value, and the first execution command is used for indicating a server to send a first execution result of a first transaction;
if the current mode is an automatic submitting mode and the first transaction is a single sentence transaction, executing the first transaction to obtain an executing result;
and sending a first execution result to the client according to the batch return value.
2. The method according to claim 1, wherein the execution result includes at least one piece of result data, and the executing the first transaction to obtain the execution result includes:
executing the first transaction until the amount of resulting data is equal to the bulk return value,
or,
until the first transaction is executed, and the quantity of the obtained result data is smaller than a batch return value; and the first execution result is the obtained result data.
3. The method of claim 2, wherein before sending the first execution result to the client according to the batch return value, further comprising:
Judging whether all result data of the first transaction are obtained currently;
ending the first transaction if all the result data of the first transaction are obtained currently;
and if the result data of all the first transactions are not obtained currently, waiting for receiving a second execution command sent by the client, wherein the second execution command is used for indicating the server to continue to send the second execution result of the first transactions according to the batch return value.
4. The method of claim 1, wherein the executing the first transaction results in an execution result comprising:
executing the first transaction to obtain all result data of the first transaction, wherein the number of the result data of all the first transaction is greater than or equal to the first execution result;
before the first execution result is sent to the client, the method further comprises:
judging whether to send all the result data of the first transaction to a client after sending the first execution result according to the number of the result data of all the first transactions and the number of the first execution results;
if all the result data of the first transaction are sent to the client after the first execution result is sent, ending the first transaction;
And if not all the result data of the first transaction are currently sent to the client, waiting for receiving a second execution command sent by the client, wherein the second execution command is used for indicating the server to continue sending the second execution result of the first transaction according to the batch return value.
5. The method of any one of claims 1-4, further comprising:
and if a third execution command sent by the client is received, storing the result data of the first transaction which is not sent to the client, wherein the third execution command is used for indicating the server to execute a second transaction.
6. The method of claim 5, wherein after storing the result data of the first transaction that is not sent to the client, further comprising:
receiving a fourth execution command sent by the client, wherein the fourth execution command is used for indicating the server to send a third execution result of the first transaction according to a batch return value;
obtaining a third execution result according to the batch return value and the stored result data of the first transaction which is not sent to the client;
and sending the third execution result to the client.
7. The method of any one of claims 1-4, further comprising:
and receiving an ending command of the first transaction sent by the client, and submitting the first transaction.
8. An apparatus for data transmission for use in a database, comprising:
the receiving module is used for receiving a first execution command sent by the client, wherein the first execution command comprises a batch return value, and the first execution command is used for indicating the server to send a first execution result of a first transaction;
the obtaining module is used for executing the first transaction if the current mode is an automatic submitting mode and the first transaction is a single sentence transaction, so as to obtain an execution result;
and the sending module is used for sending the first execution result to the client according to the batch return value.
9. A data transmission device for use in a database, comprising:
a memory for storing processor-executable instructions;
processor for implementing the method according to any of the preceding claims 1 to 7 when the computer program is executed.
10. A computer-readable storage medium, in which computer-executable instructions are stored, which when executed by a processor are adapted to carry out the method of data transmission applied to a database as claimed in any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011300687.2A CN112416977B (en) | 2020-11-19 | 2020-11-19 | Method, device and equipment for data transmission applied to database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011300687.2A CN112416977B (en) | 2020-11-19 | 2020-11-19 | Method, device and equipment for data transmission applied to database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112416977A CN112416977A (en) | 2021-02-26 |
| CN112416977B true CN112416977B (en) | 2023-12-19 |
Family
ID=74774470
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011300687.2A Active CN112416977B (en) | 2020-11-19 | 2020-11-19 | Method, device and equipment for data transmission applied to database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112416977B (en) |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2001016704A2 (en) * | 1999-08-31 | 2001-03-08 | Accenture Llp | System, method, and article of manufacture for a request sorter in a transaction services patterns environment |
| CN110427427A (en) * | 2019-08-02 | 2019-11-08 | 北京快立方科技有限公司 | A kind of bridged by pin realizes global transaction distributed approach |
| CN110688397A (en) * | 2019-07-30 | 2020-01-14 | 民生科技有限责任公司 | SQL-based distributed data unified access system and method |
| US10565203B1 (en) * | 2017-03-31 | 2020-02-18 | Intuit Inc. | Object relational mapper for non-relational databases |
Family Cites Families (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8965860B2 (en) * | 2010-04-01 | 2015-02-24 | Salesforce.Com, Inc. | Methods and systems for bulk uploading of data in an on-demand service environment |
| US11068506B2 (en) * | 2016-05-10 | 2021-07-20 | Sap Se | Selective dispatching of OLAP requests using execution statistics |
| US10628424B2 (en) * | 2016-09-15 | 2020-04-21 | Oracle International Corporation | Graph generation for a distributed event processing system |
-
2020
- 2020-11-19 CN CN202011300687.2A patent/CN112416977B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2001016704A2 (en) * | 1999-08-31 | 2001-03-08 | Accenture Llp | System, method, and article of manufacture for a request sorter in a transaction services patterns environment |
| US10565203B1 (en) * | 2017-03-31 | 2020-02-18 | Intuit Inc. | Object relational mapper for non-relational databases |
| CN110688397A (en) * | 2019-07-30 | 2020-01-14 | 民生科技有限责任公司 | SQL-based distributed data unified access system and method |
| CN110427427A (en) * | 2019-08-02 | 2019-11-08 | 北京快立方科技有限公司 | A kind of bridged by pin realizes global transaction distributed approach |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112416977A (en) | 2021-02-26 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10474645B2 (en) | Automatically retrying transactions with split procedure execution | |
| CN111433764A (en) | High-throughput distributed transaction management of global consistency sliced O L TP system and implementation method thereof | |
| CN111459418B (en) | RDMA (remote direct memory Access) -based key value storage system transmission method | |
| US10996996B2 (en) | Implementing multiple content management service operations | |
| EP2947582A1 (en) | Computing device and method for executing database operation command | |
| CN111198872B (en) | Method and device for processing transactions by database | |
| CN110675255A (en) | Method and apparatus for concurrently executing transactions in a blockchain | |
| CN111107022B (en) | Data transmission optimization method, device and readable storage medium | |
| CN113421073A (en) | Method and apparatus for concurrently executing transactions in a blockchain | |
| US20200252314A1 (en) | Method and apparatus for managing network connection, and storage medium | |
| CN112416977B (en) | Method, device and equipment for data transmission applied to database | |
| CN110689344B (en) | Method and apparatus for concurrently executing transactions in a blockchain | |
| CN106815339B (en) | Method and device for updating query plan | |
| CN115438025A (en) | Data processing method and device | |
| CN110543349B (en) | Application starting acceleration method and device and computer readable storage medium | |
| CN114385621A (en) | Locking method, device, equipment and medium | |
| CN112925807A (en) | Database-oriented request batch processing method, device, equipment and storage medium | |
| EP3901790A1 (en) | Determining method for a common component of pages, server and storage medium | |
| CN111737298B (en) | Cache data management and control method and device based on distributed storage | |
| CN112351053B (en) | Remote access method, device, equipment and medium for embedded database | |
| CN112162832B (en) | Method and device for realizing audit data storage under multi-version concurrency control | |
| CN110858918B (en) | MDS data acquisition method, digital television and storage medium | |
| CN109062929B (en) | Query task communication method and system | |
| CN116996450B (en) | Management data processing method, device and system | |
| CN112988391B (en) | Data processing method, device, equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |