CN108228893A - A kind of relational database data persistence optimization and queuing strategy - Google Patents
A kind of relational database data persistence optimization and queuing strategy Download PDFInfo
- Publication number
- CN108228893A CN108228893A CN201810109706.XA CN201810109706A CN108228893A CN 108228893 A CN108228893 A CN 108228893A CN 201810109706 A CN201810109706 A CN 201810109706A CN 108228893 A CN108228893 A CN 108228893A
- Authority
- CN
- China
- Prior art keywords
- data
- interim
- tables
- relational database
- relationship library
- 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
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/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- 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/21—Design, administration or maintenance of databases
- G06F16/217—Database tuning
-
- 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/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention relates to field of communication technology, particularly a kind of relational database data persistence optimization and queuing strategy include the following steps：Step 1：It writes data into multiple interim tables and stores into server；Step 2：Data in interim table are written in table corresponding in the relationship library of server；Wherein, interim table is corresponded with the table in server, and during the table being written in relationship library in data, corresponding table is locked the data write-in for forbidding other users access or other interim tables in relationship library.Deadlock, reduction data processing amount, the relational database data persistence optimization for improving data processing speed and queuing strategy are reduced or eliminated the purpose of the present invention is to provide a kind of.
Description
Technical field
The present invention relates to field of communication technology, particularly a kind of relational database data persistence optimization and queuing strategy.
Background technology
The database mainstream that enterprise uses at present is all relational database, and relational database is powerful, can support more
User's operation, relational database maximum feature are exactly to allow between table and table there are relationship, and enterprise's use would generally be simultaneously number
There is relation table multiple according to persistence, if when failure occurs for some table, for data consistent rows, this persistence is needed to grasp
Make all to cancel.In order to realize rollback function, relational database introduces affairs, and affairs can be to operating during persistence
Table is locked (others can not read and write), after this persistence operates completion confirmation without mistake, is just unlocked, otherwise just
After carrying out rollback to data, then it is unlocked.
MSSQL is a very common database in relational database, and such as Fig. 1 is two tables in relationship library, table 1
With table 2, each table has three, totally six datas, six data persistences in database, needs to correspond in a traditional way
Six data command operations, instruction are recorded as,
Insert into tables 1 (FileName1, FileName2, FileName3) Values (' Table1Row1FN1', '
Table1Row1FN2','Table1Row1FN3')
Insert into tables 1 (FileName1, FileName2, FileName3) Values (' Table1Row2FN1', '
Table1Row2FN2','Table1Row2FN3')
Insert into tables 1 (FileName1, FileName2, FileName3) Values (' Table1Row3FN1', '
Table1Row3FN2','Table1Row3FN3')
Insert into tables 2 (FileName1, FileName2, FileName3) Values (' Table2Row1FN1', '
Table2Row1FN2','Table2Row1FN3')
Insert into tables 2 (FileName1, FileName2, FileName3) Values (' Table2Row2FN1', '
Table2Row2FN2','Table2Row2FN3')
Insert into tables 2 (FileName1, FileName2, FileName3) Values (' Table2Row3FN1', '
Table2Row3FN2','Table2Row3FN3')
There are six instructions to need to the extremely cost source I/O operation of database six times, so can cause to grow Tables 1 and 2
The lock of time.Furthermore if after submitting first instruction, network speed is slack-off, and it is slack-off that lower item instructs submission to be obstructed, and further increases affairs
Deadline extremely influences operation of the other users to the two tables.
In this case, it is most likely that there are traditional disaster pattern, if above-mentioned six data is same by first and second respectively
Shi Jinhang is operated, they are at corresponding operation note,
First：
Insert into tables 1 (FileName1, FileName2, FileName3) Values ('
Table1Row1FN1','Table1Row1FN2','Table1Ro w1FN3')
Insert into tables 2 (FileName1, FileName2, FileName3) Values ('
Table2Row1FN1','Table2Row1FN2','Table2Ro w1FN3')
Insert into tables 1 (FileName1, FileName2, FileName3) Values ('
Table1Row2FN1','Table1Row2FN2','Table1Ro w2FN3')
Second：
Insert into tables 2 (FileName1, FileName2, FileName3) Values ('
Table2Row2FN1','Table2Row2FN2','Table2Ro w2FN3')
Insert into tables 1 (FileName1, FileName2, FileName3) Values ('
Table1Row3FN1','Table1Row3FN2','Table1Ro w3FN3')
Insert into tables 2 (FileName1, FileName2, FileName3) Values ('
Table2Row3FN1','Table2Row3FN2','Table2Ro w3FN3')
According to the first and second operation orders：The first step, first first operate table 1 and table 1 are locked, and second first operates table 2 and table 2 is added
Lock.Second step, first needs operation table 2, but table 2 has been locked in the first step by second, and causing first that can not operate can only wait for, and second needs
Table 1 is operated, but table 1 has been locked in the first step by first, second can not also operate, and can only also wait for, and the first and second phases interlock like this
Dead other side, causes deadlock, and finally only side's time-out unsuccessfully unlocks, and the opposing party could continue or both sides' all time-out failures.
Invention content
Deadlock is reduced or eliminated the purpose of the present invention is to provide one kind, reduces data processing amount, raising data processing speed
The relational database data persistence optimization of degree and queuing strategy.
The present invention specific technical solution be：A kind of relational database data persistence optimization and queuing strategy, including
Following steps：
Step 1：It writes data into multiple interim tables and stores into server；
Step 2：Data in interim table are written in table corresponding in the relationship library of server；Wherein, temporarily
Table is corresponded with the table in server, during the table being written in relationship library in data, corresponding in relationship library
Table is locked the data write-in for forbidding other users access or other interim tables.
In above-mentioned relational database data persistence optimization and queuing strategy, when multiple users are required to relationship simultaneously
When table in library carries out data write-in, multiple users are carried out at the same time step 1, and are carried out successively according to the request sequence of user
Step 2.
In above-mentioned relational database data persistence optimization and queuing strategy, wrapped in interim table in the step 1
The information of the table in its corresponding relationship library is contained；
The information of the table in corresponding relationship library described in the interim table of multiple users is at least partly identical.
Compared with prior art, the beneficial effects of the present invention are：
The solution of the present invention can be unified to be written in relationship library by interim table, reduce lock as a result of interim table
Dead probability improves data processing speed.
Description of the drawings
Fig. 1 is the flow diagram of embodiment 1.
Specific embodiment
With reference to embodiment, technical scheme of the present invention is described in further detail, but do not formed pair
Any restrictions of the present invention.
Embodiment 1
A kind of relational database data persistence optimization and queuing strategy, include the following steps：
Step 1：It writes data into multiple interim tables and stores into server；
Step 2：Data in interim table are written in table corresponding in the relationship library of server；Wherein, temporarily
Table is corresponded with the table in server, during the table being written in relationship library in data, corresponding in relationship library
Table is locked the data write-in for forbidding other users access or other interim tables.
By the above method, locked for a long time caused by the table in same relation library can be directly invoked simultaneously to avoid user
Dead situation, does not influence the data write-in of user, and user will not directly sensory data processing speed be substantially reduced.
Preferably, when multiple users require to carry out data write-in to the table in relationship library simultaneously, multiple users are simultaneously
It carries out step 1, and step 2 successively is carried out according to the request of user sequence, it is right in interim table to contain its in the step 1
The information of table in the relationship library answered；The table in corresponding relationship library described in the interim table of multiple users
Information is at least partly identical.
In order to more specifically explain the scheme of the present embodiment, concrete application of the invention is exemplified below：
User is first in two interim table ##temp1 and ##temp2 exclusively enjoyed of Database, first respectively Tables 1 and 2
Data buffer storage is in interim table：
Insert into ##temp1(FileName1,FileName2,FileName3)Values('
Table1Row1FN1','Table1Row1FN2','Ta ble1Row1FN3')
Insert into ##temp1(FileName1,FileName2,FileName3)Values('
Table1Row2FN1','Table1Row2FN2','Ta ble1Row2FN3')
Insert into ##temp1(FileName1,FileName2,FileName3)Values('
Table1Row3FN1','Table1Row3FN2','Ta ble1Row3FN3')
Insert into ##temp2(FileName1,FileName2,FileName3)Values('
Table2Row1FN1','Table2Row1FN2','Ta ble2Row1FN3')
Insert into ##temp2(FileName1,FileName2,FileName3)Values('
Table2Row2FN1','Table2Row2FN2','Ta ble2Row2FN3')
Insert into ##temp2(FileName1,FileName2,FileName3)Values('
Table2Row3FN1','Table2Row3FN2','Ta ble2Row3FN3')
Since ##temp1 and ##temp2 are that user oneself builds table, there is no being locked to Tables 1 and 2, it is not interfered with
He is user.Tables 1 and 2 is uniformly copied to by interim table again, and ##temp1 and ##temp2 data be on the server, not by
Network speed influences the speed of duplication.
Insert into tables 1 (FileName1, FileName2, FileName3) Select*from##temp1
Insert into tables 2 (FileName1, FileName2, FileName3) Select*from##temp2
Such operation only carries out I/O operation twice to database, greatly shortens to the Tables 1 and 2 lock table time.To it
The influence of his user drops to very low.
When encountering traditional disaster pattern, the optimization processing of the present embodiment is as follows：
The interim table ##temp1 and ##temp2 oneself exclusively enjoyed is established to first first, while is annotated to need to lock Tables 1 and 2
Insert into ##temp1(FileName1,FileName2,FileName3)Values('
Table1Row1FN1','Table1Row1FN2','Ta ble1Row1FN3')
Insert into ##temp2(FileName1,FileName2,FileName3)Values('
Table2Row1FN1','Table2Row1FN2','Ta ble2Row1FN3')
Insert into ##temp1(FileName1,FileName2,FileName3)Values('
Table1Row2FN1','Table1Row2FN2','Ta ble1Row2FN3')
The interim table ##temp3 and ##temp4 oneself exclusively enjoyed is established to second first, while is annotated to need to lock Tables 1 and 2
Insert into ##temp3(FileName1,FileName2,FileName3)Values('
Table2Row2FN1','Table2Row2FN2','Ta ble2Row2FN3')
Insert into ##temp4(FileName1,FileName2,FileName3)Values('
Table1Row3FN1','Table1Row3FN2','Ta ble1Row3FN3')
Insert into ##temp3(FileName1,FileName2,FileName3)Values('
Table2Row3FN1','Table2Row3FN2','Ta ble2Row3FN3')
Following first and second will data to just table carry out persistence,
The operation of first is
Insert into tables 1 (FileName1, FileName2, FileName3) Select*from##temp1
Insert into tables 2 (FileName1, FileName2, FileName3) Select*from##temp2
The operation of second is
Insert into tables 2 (FileName1, FileName2, FileName3) Select*from##temp3
Insert into tables 3 (FileName1, FileName2, FileName3) Select*from##temp4
Step 2 can there are following several modes：
Pattern one
First and second can copy in Tables 1 and 2 simultaneously, it is possible that deadlock processing here and tradition, when due to
Optimization before, six I/O operations become four I/O operations, and the time of lock shortens, and interim table is all stored in server,
Reproduction process is not influenced by network speed, and the likelihood of deadlock can drop to very low.
Pattern two
It is lined up, is allowed after first asking to complete all operationss according to the priority of operation requests, then operate in addition a ask
It asks.Equal to being that a user operates database always, so being not in the situation of deadlock.If first is first asked, second
It just needs after first is waited for operate, could be operated.
Pattern three
Multiple operation requests can be carried out at the same time, when before start, the table locked to being carrying out operation annotation needs carries out
Contrast judgement, if it is overlapping the table locked to be needed to have in the table locked being needed to be annotated with the operation in performing operation annotation, this behaviour
Make after the completion of asking to be waited until being carrying out operation requests, can just be performed.First is to need by regular second in commission
It is to be added performed after be just performed because first and second have annotation to need the table locked：Tables 1 and 2, if there is the third, and third note
Releasing needs the table locked not have Tables 1 and 2, this when third is to may be performed simultaneously.
Above-described is only presently preferred embodiments of the present invention, all timess made in the range of the spirit and principles in the present invention
What modifications, equivalent substitutions and improvements etc., should all be included in the protection scope of the present invention.
Claims (3)
1. a kind of relational database data persistence optimization and queuing strategy, which is characterized in that include the following steps：
Step 1：It writes data into multiple interim tables and stores into server；
Step 2：Data in interim table are written in table corresponding in the relationship library of server；Wherein, interim table with
Table in server corresponds, during the table being written in relationship library in data, corresponding table in relationship library
The locked data write-in for forbidding other users access or other interim tables.
2. relational database data persistence optimization according to claim 1 and queuing strategy, which is characterized in that when multiple
When user requires to carry out data write-in to the table in relationship library simultaneously, multiple users are carried out at the same time step 1, and according to user
Request sequence carry out step 2 successively.
3. the relational database data persistence optimization according to claim 2 and queuing strategy, which is characterized in that
The information of the table in its corresponding relationship library is contained in the step 1 in interim table；
The information of the table in corresponding relationship library described in the interim table of multiple users is at least partly identical.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810109706.XA CN108228893A (en) | 2018-02-05 | 2018-02-05 | A kind of relational database data persistence optimization and queuing strategy |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810109706.XA CN108228893A (en) | 2018-02-05 | 2018-02-05 | A kind of relational database data persistence optimization and queuing strategy |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN108228893A true CN108228893A (en) | 2018-06-29 |
Family
ID=62669729
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810109706.XA Pending CN108228893A (en) | 2018-02-05 | 2018-02-05 | A kind of relational database data persistence optimization and queuing strategy |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108228893A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114217899A (en) * | 2021-12-15 | 2022-03-22 | 平安国际智慧城市科技股份有限公司 | Data persistence method and device, electronic equipment and storage medium |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1331817A (en) * | 1998-11-25 | 2002-01-16 | 电脑联合想象公司 | Method and appts. for concurrent DBMS table operations |
| US6714935B1 (en) * | 1998-09-21 | 2004-03-30 | Microsoft Corporation | Management of non-persistent data in a persistent database |
| CN101187934A (en) * | 2006-11-21 | 2008-05-28 | 国际商业机器公司 | Method and system for providing high performance data modification of relational database tables |
| CN101211362A (en) * | 2006-12-26 | 2008-07-02 | 国际商业机器公司 | System and method for database update management |
| CN101526957A (en) * | 2009-04-09 | 2009-09-09 | 北京四方继保自动化股份有限公司 | Pattern-model integrated version management method applied in power automatization system |
| US20140222872A1 (en) * | 2013-02-04 | 2014-08-07 | Bank Of America Corporation | Multi-row database updating for enterprise workflow application |
-
2018
- 2018-02-05 CN CN201810109706.XA patent/CN108228893A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6714935B1 (en) * | 1998-09-21 | 2004-03-30 | Microsoft Corporation | Management of non-persistent data in a persistent database |
| CN1331817A (en) * | 1998-11-25 | 2002-01-16 | 电脑联合想象公司 | Method and appts. for concurrent DBMS table operations |
| CN101187934A (en) * | 2006-11-21 | 2008-05-28 | 国际商业机器公司 | Method and system for providing high performance data modification of relational database tables |
| CN101211362A (en) * | 2006-12-26 | 2008-07-02 | 国际商业机器公司 | System and method for database update management |
| CN101526957A (en) * | 2009-04-09 | 2009-09-09 | 北京四方继保自动化股份有限公司 | Pattern-model integrated version management method applied in power automatization system |
| US20140222872A1 (en) * | 2013-02-04 | 2014-08-07 | Bank Of America Corporation | Multi-row database updating for enterprise workflow application |
Non-Patent Citations (1)
| Title |
|---|
| 董迎红: "信息管理系统中多用户并发控制理论及应用", 《现代情报》 * |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN114217899A (en) * | 2021-12-15 | 2022-03-22 | 平安国际智慧城市科技股份有限公司 | Data persistence method and device, electronic equipment and storage medium |
| CN114217899B (en) * | 2021-12-15 | 2023-10-17 | 深圳平安智慧医健科技有限公司 | Data persistence method, device, electronic equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN101046821B (en) | Generic database manipulator | |
| JP3704573B2 (en) | Cluster system | |
| US6826604B2 (en) | Input/output device information management system for multi-computer system | |
| JP4738413B2 (en) | Method and system for migrating source data to target data | |
| US6067587A (en) | Method for serializing and synchronizing data packets by utilizing a physical lock system and a control data structure for mutual exclusion lock | |
| US6336163B1 (en) | Method and article of manufacture for inserting volumes for import into a virtual tape server | |
| US6851022B2 (en) | Raid controller and control method thereof | |
| US7418563B2 (en) | Method for controlling storage device controller, storage device controller, and program | |
| JP2004013367A5 (en) | ||
| CN110471688A (en) | Operation system processing method, device, equipment and storage medium | |
| JPH04229355A (en) | Data access method and data processing system | |
| CN106897345A (en) | A kind of method and device of data storage | |
| JP2012033095A (en) | Document creation device for electronic medical chart | |
| CN108228893A (en) | A kind of relational database data persistence optimization and queuing strategy | |
| CN109446258A (en) | A kind of distributed data storage method and system | |
| EP0037264A2 (en) | Associative memory system | |
| US7318168B2 (en) | Bit map write logging with write order preservation in support asynchronous update of secondary storage | |
| CN106997305A (en) | A kind of transaction methods and device | |
| CN104220982B (en) | A kind of transaction methods and device | |
| US8904348B2 (en) | Method and system for handling errors during script execution | |
| CN110390969A (en) | A kind of method and system for realizing atomic write | |
| JP2586610B2 (en) | File creation method | |
| JPS5947341B2 (en) | Dynamic exclusive control method for index sequential files | |
| JPS62226367A (en) | Digital computer system | |
| CN115185648A (en) | Transaction processing method for memory data operation |
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: 20180629 |