CN111061801A - Transaction type database read-write separation implementation method - Google Patents
Transaction type database read-write separation implementation method Download PDFInfo
- Publication number
- CN111061801A CN111061801A CN201911359751.1A CN201911359751A CN111061801A CN 111061801 A CN111061801 A CN 111061801A CN 201911359751 A CN201911359751 A CN 201911359751A CN 111061801 A CN111061801 A CN 111061801A
- Authority
- CN
- China
- Prior art keywords
- read
- write
- connection pool
- jdbc
- server
- 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.)
- Pending
Links
Images
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/25—Integrating or interfacing systems involving database management systems
-
- 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/25—Integrating or interfacing systems involving database management systems
- G06F16/252—Integrating or interfacing systems involving database management systems between a Database Management System and a front-end application
Abstract
S1, deploying a cluster of a database management system, configuring a read server and a write server through a file manager, wherein the cluster is used for providing independent read server nodes and write server nodes; s2, providing a read connection pool and a write connection pool by the JDBC end of the database management system, wherein the read connection pool and the write connection pool point to the read server nodes of the cluster and are used for improving the response speed; and S3, configuring a read server address and a write server address at the JDBC end of the database management system, wherein the read server address and the write server address serve as the data source of the JDBC end connection pool. The transaction type database read-write separation implementation method provided by the invention is used for perfecting the prior art system, so that the database access efficiency is improved, the advantage of a cluster database is greatly improved, and a more efficient and rapid response speed is provided for an application system.
Description
Technical Field
The invention belongs to the field of transactional databases, and particularly relates to a method for realizing read-write separation of a transactional database.
Background
With the rapid development of the information technology, the internet data operation is often read more and less, and the data reading speed of the database becomes the primary bottleneck of the database.
In order to solve the problem of database read-write consistency, a database object needs to be locked. The locked object may be a table, record, attribute, index, etc. in a database, such as transaction T, and when a locking request is issued to the system, control is taken over the data object and no other transaction can perform any operation on the database object until transaction T releases its lock. The lock is a queuing mechanism, and the concurrent tasks are queued according to the sequence of the lock and changed into serial tasks. The concurrency and locking mechanism of the database determines that the data reading and writing operation of large data volume, and the data operation can be actually and sequentially completed in the database according to the locking mechanism. Therefore, system performance is reduced, especially in the current internet application scenario of big data access, a single data access mode inevitably causes a slow data reading and writing speed, and it is urgently needed to linearly improve the reading performance of the database and eliminate read-write lock conflicts so as to improve the writing performance of the database.
Disclosure of Invention
In view of this, the present invention is directed to provide a method for implementing separation of read and write operations of a transactional database, which implements separation of read and write operations of data by using an extended function in JDBC, so that a master server and a slave server are fully utilized.
In order to achieve the purpose, the technical scheme of the invention is realized as follows:
a method for realizing read-write separation of a transactional database comprises the following steps:
s1, deploying a cluster of a database management system, and configuring a read server and a write server through a file manager, wherein the cluster is used for providing independent read server nodes and write server nodes;
s2, providing a read connection pool and a write connection pool by the JDBC end of the database management system, wherein the read connection pool and the write connection pool point to the read server nodes of the cluster and are used for improving the response speed;
and S3, configuring a read server address and a write server address at the JDBC end of the database management system, wherein the read server address and the write server address serve as the data source of the JDBC end connection pool.
Further, the read connection pool and the write connection pool in S2 also point to the write server node of the cluster, so as to improve the response speed.
Further, the read connection pool and the write connection pool of the JDBC end of the database management system further include the following steps: the method comprises the steps of creating a read connection pool and a write connection pool of a JDBC end, using the read connection pool and the write connection pool of the JDBC end, and destroying the read connection pool and the write connection pool of the JDBC end.
Further, the method for creating the read connection pool and the write connection pool of the JDBC terminal includes: the JDBC terminal automatically creates a master data source connection pool and a slave data source connection pool, initializes the connection pools of the master data source and the slave data source according to the attribute configuration file in the class path and stores the connection pools in a hash table so as to be called later.
Further, the attribute configuration file comprises a read server configuration file and a write server configuration file.
Further, the method for using the read connection pool and the write connection pool of the JDBC terminal includes: the user executes SQL statements through a standard SQL interface State object execution Statement, and JDBC filters the SQL statements to judge whether reading operation exists.
Further, the JDBC filtering SQL statement judges that the reading operation exists, and the JDBC end can acquire the connection pointing to the reading cluster node in the reading connection pool;
and the JDBC filtering SQL statement judges that the reading operation does not exist, and a new reading connection is created again.
Further, after the user application is closed and disconnected, the JDBC end automatically closes the reading connection pool and the writing connection pool so as to destroy the reading connection pool and the writing connection pool of the JDBC end.
Compared with the prior art, the transaction type database read-write separation implementation method has the following advantages:
the transaction type database read-write separation implementation method provided by the invention is used for perfecting the prior art system, so that the database access efficiency is improved, the advantage of a cluster database is greatly improved, and a more efficient and rapid response speed is provided for an application system.
Drawings
The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate an embodiment of the invention and, together with the description, serve to explain the invention and not to limit the invention. In the drawings:
fig. 1 is a schematic diagram of a JDBC read-write separation implementation architecture according to an embodiment of the present invention;
fig. 2 is a schematic diagram illustrating implementation of JDBC read-write separation according to an embodiment of the present invention;
fig. 3 is a schematic diagram of a JDBC read-write separation external interface according to an embodiment of the present invention.
Detailed Description
It should be noted that the embodiments and features of the embodiments may be combined with each other without conflict.
In the description of the present invention, it is to be understood that the terms "center", "longitudinal", "lateral", "up", "down", "front", "back", "left", "right", "vertical", "horizontal", "top", "bottom", "inner", "outer", and the like, indicate orientations or positional relationships based on those shown in the drawings, and are used only for convenience in describing the present invention and for simplicity in description, and do not indicate or imply that the referenced devices or elements must have a particular orientation, be constructed and operated in a particular orientation, and thus, are not to be construed as limiting the present invention. Furthermore, the terms "first", "second", etc. are used for descriptive purposes only and are not to be construed as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defined as "first," "second," etc. may explicitly or implicitly include one or more of that feature. In the description of the present invention, "a plurality" means two or more unless otherwise specified.
In the description of the present invention, it should be noted that, unless otherwise explicitly specified or limited, the terms "mounted," "connected," and "connected" are to be construed broadly, e.g., as meaning either a fixed connection, a removable connection, or an integral connection; can be mechanically or electrically connected; they may be connected directly or indirectly through intervening media, or they may be interconnected between two elements. The specific meaning of the above terms in the present invention can be understood by those of ordinary skill in the art through specific situations.
The present invention will be described in detail below with reference to the embodiments with reference to the attached drawings.
As shown in fig. 1 to fig. 3, a method for implementing read-write separation of a transactional database includes the following steps:
s1, deploying a cluster of a database management system, and configuring a read server and a write server through a file manager, wherein the cluster is used for providing independent read server nodes and write server nodes;
s2, providing a read connection pool and a write connection pool by the JDBC end of the database management system, wherein the read connection pool and the write connection pool point to the read server nodes of the cluster and are used for improving the response speed;
and S3, configuring a read server address and a write server address at the JDBC end of the database management system, wherein the read server address and the write server address serve as the data source of the JDBC end connection pool.
The read connection pool and the write connection pool in S2 also point to the write server node of the cluster, so as to improve the response speed.
The read connection pool and the write connection pool of the JDBC end of the database management system further comprise the following steps: the method comprises the steps of creating a read connection pool and a write connection pool of a JDBC end, using the read connection pool and the write connection pool of the JDBC end, and destroying the read connection pool and the write connection pool of the JDBC end.
The method for creating the read connection pool and the write connection pool of the JDBC end comprises the following steps: the JDBC terminal automatically creates a master data source connection pool and a slave data source connection pool, initializes the connection pools of the master data source and the slave data source according to the attribute configuration file in the class path and stores the connection pools in a hash table so as to be called later.
The attribute configuration file includes a read server configuration file and a write server configuration file.
The method for using the read connection pool and the write connection pool of the JDBC end comprises the following steps: the user executes SQL statements through a standard SQL interface State object execution Statement, and JDBC filters the SQL statements to judge whether reading operation exists.
The JDBC filters SQL statements to judge that reading operation exists, and the JDBC end acquires connection pointing to a reading cluster node in a reading connection pool;
and the JDBC filtering SQL statement judges that the reading operation does not exist, and a new reading connection is created again.
After the user application is disconnected, the JDBC end automatically closes the reading connection pool and the writing connection pool so as to destroy the reading connection pool and the writing connection pool of the JDBC end.
A JDBC read-write separation method based on transactional database includes the following steps:
1) firstly, GBase 8s cluster service is deployed, and a read-write server is configured through a CM manager.
The cluster provides independent read and write server nodes.
2) In order to improve the response speed, the GBase 8s JDBC terminal provides two connection pools of reading and writing, wherein the connection pools of reading and writing point to all connections of the cluster reading service node (or all connections of the cluster writing service node). The JDBC end configures the addresses of the read and write servers as the data source of the JDBC end connection pool.
Further, the above process related to the creation, use and destruction of JDBC connection pool further includes the following steps:
a1. connection pool initialization process: applications create connections through JDBC, and it is not necessary for the user to know whether to connect to a read or write server. When a user establishes connection, two read-write connection pools are automatically established. The JDBC terminal automatically creates two master and slave data source connection pools, and when the two master and slave data source connection pools are loaded, the two master and slave data source connection pools are initialized according to the attribute configuration files (read server configuration and write server configuration) in the class path and are stored in the hash table, so that the subsequent calling is facilitated.
a2. Connection pool connection creation process: and executing the SQL Statement according to the user through a standard SQL interface State Statement, filtering the SQL Statement by the JDBC to judge whether a read operation exists, and if the read operation exists, acquiring the connection pointing to the read cluster node in a read connection pool by the JDBC terminal. If no connection exists in the current read connection pool, a new read connection is recreated. If there is no SELECT statement, JDBC will get the connection from the write connection pool. Similarly, if there is no connection in the connection pool that points to a write node, a new write connection is created.
a3 connection pool destruction: after the user application closes the connection, the JDBC end automatically closes the connection pool.
3) The JDBC end-to-read-write separation processing process comprises the following steps: and the application program sends SQL statements to the JDBC terminal. The JDBC intercepts SQL sent by the application and performs read-write separation processing in a classified mode.
GBase 8s is a transactional database, and the SQL read-write separation processing of transactions and non-transactions is different.
Whether it is a transaction operation is divided into the following two processes.
a1. Transactional SQL processing
Setauto commit (false), when the executed in the current transaction encounters the sql at the beginning of the first sentence which is not selected, the currently executed sql and the subsequent sql need to be executed by all master nodes, otherwise, the executed sql is executed by the slave nodes.
a2. Non-transactional SQL processing
Setautucom (true), the select statement is to be executed from the node and the rest of the master nodes.
The JDBC calling interface is divided into three types, namely State, PrepareState and CallabeState. The three processing modes are similar, but SQL read-write separation is required.
Simple SQL statement with state without parameters. Intercepting SQL sentences, judging whether a SELECT exists, if so, acquiring data from a read server, and if not, updating from a write server.
Objects of SQL statements precompiled by preparedstatement. The operation process is processed with the State element.
Callablestatement stores the procedural SQL statement set. The storage process executes, defaults to write operations, pointing to write node connections.
FIG. 1 is a diagram showing the architecture of JDBC read-write separation according to the present invention.
The JDBC read-write separation comprises a database connection Pool module (Pool), a master-slave data source module (MasterSlaveDataSource) and corresponding read-write separation logic added on the native JDBC. The database connection pool module mainly provides data source connection pool service for the outside, and the master-slave data source module provides management and connection of master-slave data sources for the outside; corresponding read-write separation logic is added on the native JDBC, and the corresponding read-write separation logic is mainly added on the specific interfaces of State, PrepareState and CallabeState.
Fig. 2 shows a schematic flow illustration of implementation of JDBC read-write separation deployment in the present invention, which specifically includes the following steps:
the application system sends an SQL request to the JDBC. JDBC intercepts the obtained SQL request and carries out classification processing, transaction SQL and non-transaction SQL.
a 1: the transaction-type processing process is as follows: when the execution in the current transaction encounters the sql at the beginning of the first sentence which is not select, the currently executed sql and the subsequent sql need to be executed by the 8s master server node, otherwise, the execution is executed by the slave node.
a 2: the non-transactional processing procedure is as follows: intercepting SQL sentences as select sentences to 8s to be executed from the server nodes, and executing the rest main nodes.
The above description is only for the purpose of illustrating the preferred embodiments of the present invention and is not to be construed as limiting the invention, and any modifications, equivalents, improvements and the like that fall within the spirit and principle of the present invention are intended to be included therein.
Claims (8)
1. A transaction type database read-write separation implementation method is characterized by comprising the following steps:
s1, deploying a cluster of a database management system, and configuring a read server and a write server through a file manager, wherein the cluster is used for providing independent read server nodes and write server nodes;
s2, providing a read connection pool and a write connection pool by the JDBC end of the database management system, wherein the read connection pool and the write connection pool point to the read server nodes of the cluster and are used for improving the response speed;
and S3, configuring a read server address and a write server address at the JDBC end of the database management system, wherein the read server address and the write server address serve as the data source of the JDBC end connection pool.
2. The method for implementing read-write separation of transactional database according to claim 1, wherein: the read connection pool and the write connection pool in S2 also point to the write server node of the cluster, so as to improve the response speed.
3. The method for implementing read-write separation of transactional database according to claim 1, wherein: the read connection pool and the write connection pool of the JDBC end of the database management system further comprise the following steps: the method comprises the steps of creating a read connection pool and a write connection pool of a JDBC end, using the read connection pool and the write connection pool of the JDBC end, and destroying the read connection pool and the write connection pool of the JDBC end.
4. The method of claim 3, wherein the transactional database comprises: the method for creating the read connection pool and the write connection pool of the JDBC end comprises the following steps: the JDBC terminal automatically creates a master data source connection pool and a slave data source connection pool, initializes the connection pools of the master data source and the slave data source according to the attribute configuration file in the class path and stores the connection pools in a hash table so as to be called later.
5. The method of claim 3, wherein the transactional database comprises: the attribute configuration file includes a read server configuration file and a write server configuration file.
6. The method for implementing read-write separation of transactional database according to claim 1, wherein: the method for using the read connection pool and the write connection pool of the JDBC end comprises the following steps: the user executes SQL statements through a standard SQL interface State object execution Statement, and JDBC filters the SQL statements to judge whether reading operation exists.
7. The method of claim 3, wherein the transactional database comprises: the JDBC filters SQL statements to judge that reading operation exists, and the JDBC end acquires connection pointing to a reading cluster node in a reading connection pool;
and the JDBC filtering SQL statement judges that the reading operation does not exist, and a new reading connection is created again.
8. The method for implementing read-write separation of transactional database according to claim 1, wherein: after the user application is disconnected, the JDBC end automatically closes the reading connection pool and the writing connection pool so as to destroy the reading connection pool and the writing connection pool of the JDBC end.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911359751.1A CN111061801A (en) | 2019-12-25 | 2019-12-25 | Transaction type database read-write separation implementation method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911359751.1A CN111061801A (en) | 2019-12-25 | 2019-12-25 | Transaction type database read-write separation implementation method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111061801A true CN111061801A (en) | 2020-04-24 |
Family
ID=70303549
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201911359751.1A Pending CN111061801A (en) | 2019-12-25 | 2019-12-25 | Transaction type database read-write separation implementation method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111061801A (en) |
Cited By (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111562987A (en) * | 2020-05-13 | 2020-08-21 | 北京思特奇信息技术股份有限公司 | Database connection pool balancing method and system for multi-instance database load |
| CN112231300A (en) * | 2020-10-21 | 2021-01-15 | 北京人大金仓信息技术股份有限公司 | Function analysis method and device and electronic equipment |
| CN112506962A (en) * | 2020-11-10 | 2021-03-16 | 中软国际科技服务南京有限公司 | High-performance database read-write separation method |
| CN115098573A (en) * | 2022-06-20 | 2022-09-23 | 上海爱可生信息技术股份有限公司 | Method for realizing database read-write separation |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102591964A (en) * | 2011-12-30 | 2012-07-18 | 北京新媒传信科技有限公司 | Implementation method and device for data reading-writing splitting system |
| CN102622427A (en) * | 2012-02-27 | 2012-08-01 | 杭州闪亮科技有限公司 | Method and system for read-write splitting database |
| US20160306854A1 (en) * | 2015-04-20 | 2016-10-20 | Oracle International Corporation | System and method for providing direct access to a sharded database |
| CN106126600A (en) * | 2016-06-21 | 2016-11-16 | 浪潮电子信息产业股份有限公司 | A kind of perform the method for read and write abruption, Apparatus and system |
| CN106446182A (en) * | 2016-09-29 | 2017-02-22 | 贵州云众知乐教育科技有限公司 | Database engine |
| CN110196859A (en) * | 2019-06-10 | 2019-09-03 | 天津神舟通用数据技术有限公司 | Data base read-write based on JDBC distributor separates cluster real-time consistency method |
-
2019
- 2019-12-25 CN CN201911359751.1A patent/CN111061801A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102591964A (en) * | 2011-12-30 | 2012-07-18 | 北京新媒传信科技有限公司 | Implementation method and device for data reading-writing splitting system |
| CN102622427A (en) * | 2012-02-27 | 2012-08-01 | 杭州闪亮科技有限公司 | Method and system for read-write splitting database |
| US20160306854A1 (en) * | 2015-04-20 | 2016-10-20 | Oracle International Corporation | System and method for providing direct access to a sharded database |
| CN106126600A (en) * | 2016-06-21 | 2016-11-16 | 浪潮电子信息产业股份有限公司 | A kind of perform the method for read and write abruption, Apparatus and system |
| CN106446182A (en) * | 2016-09-29 | 2017-02-22 | 贵州云众知乐教育科技有限公司 | Database engine |
| CN110196859A (en) * | 2019-06-10 | 2019-09-03 | 天津神舟通用数据技术有限公司 | Data base read-write based on JDBC distributor separates cluster real-time consistency method |
Non-Patent Citations (3)
| Title |
|---|
| 余杰等: "《JSP动态网站建设》", 30 April 2002, 国防工业出版社 * |
| 本书编委会: "《Java程序设计》", 31 August 2007, 中国计划出版社 * |
| 费雅洁等: "《ORACLE数据库实用技术 第2版》", 31 December 2014, 西安交通大学出版社 * |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111562987A (en) * | 2020-05-13 | 2020-08-21 | 北京思特奇信息技术股份有限公司 | Database connection pool balancing method and system for multi-instance database load |
| CN111562987B (en) * | 2020-05-13 | 2023-03-21 | 北京思特奇信息技术股份有限公司 | Database connection pool balancing method and system for multi-instance database load |
| CN112231300A (en) * | 2020-10-21 | 2021-01-15 | 北京人大金仓信息技术股份有限公司 | Function analysis method and device and electronic equipment |
| CN112506962A (en) * | 2020-11-10 | 2021-03-16 | 中软国际科技服务南京有限公司 | High-performance database read-write separation method |
| CN115098573A (en) * | 2022-06-20 | 2022-09-23 | 上海爱可生信息技术股份有限公司 | Method for realizing database read-write separation |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111061801A (en) | Transaction type database read-write separation implementation method | |
| CN106326381B (en) | HBase data retrieval method based on MapDB building | |
| JP6553822B2 (en) | Dividing and moving ranges in distributed systems | |
| CN104793988B (en) | The implementation method and device of integration across database distributed transaction | |
| US10860539B2 (en) | De-duplication-based remote replication method, and apparatus | |
| CN110019112B (en) | Data transaction processing method and device and electronic equipment | |
| WO2018001135A1 (en) | Method for processing database transaction, client and server | |
| US7313572B2 (en) | Attribute partitioning for user extensibility | |
| CN106033439A (en) | Method and system for processing distributed transaction | |
| MXPA06001846A (en) | Storage api for a common data platform. | |
| US8959117B2 (en) | System and method utilizing a generic update module with recursive calls | |
| US9858312B2 (en) | Transaction compensation for single phase resources | |
| CN110188114A (en) | A kind of optimization method of data manipulation, device, system, equipment and storage medium | |
| CN111414403A (en) | Data access method and device and data storage method and device | |
| CN105426469A (en) | Database cluster metadata management method and system | |
| EP3347815B1 (en) | Action-based routing of a transaction in an online transaction processing system | |
| CN110019530A (en) | Transaction methods and device based on distributed data base | |
| EP3824397B1 (en) | Version-based table locking | |
| CN114064684A (en) | Unified access method for heterogeneous data sources | |
| CN105868210B (en) | The creation method and device of unique index in a kind of distributed data base | |
| US20090037484A1 (en) | Programming system for occasionally-connected mobile business applications | |
| US8359323B2 (en) | Method and system for providing access to adapters | |
| EP3082050A1 (en) | Mass data fusion storage method and system | |
| CN115774754A (en) | Metadata management method, device, equipment and medium based on distributed transaction | |
| CN109815213A (en) | It is deleted on a kind of Append-Only database and the method and system of modification data |
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 | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20200424 |