Shulou(Shulou.com)06/01 Report--

This article will explain in detail the example analysis of awesome-stored-procedure release in mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Some time ago, due to the non-standard flooding of SP stored procedures on the offline analysis library, various deadlock blocking problems often occurred in ETL and BI reports. So decided to start with the whole simple SP coding specification, in fact, the main purpose is to record the running log and status of SP.

Stored procedures may already be ancient objects in the eyes of many "Internet new generation" programmers. Nowadays, most Internet system implementations are unlikely to adopt stored procedure because of its burden on database, difficulty in debugging, poor portability, and natural disadvantages in distributed and high concurrency scenarios. So it is not surprising that Alibaba's Java programming specification clearly states that "the use of stored procedures is prohibited".

But in many companies' offline relational analysis libraries, SP is still a quite common technical tool. After the flooding of SP, it is easy to develop the following diseases:

1. The running frequency and duration of Event created based on SP are set blindly and randomly. This can lead to:

Before SP itself finished running this time, the next one started by itself, and ended up locking itself up.

There is a dependency order between different SP. Once the pre-SP times out, the subsequent SP execution can only be incorrect or locked.

2. The execution process of SP is black-box and invisible. To be exact, SP does not have enough log clues in the process of running, and most of the time only know the owner of the card, but do not know where the card is.

3. There is no exception handling in SP. An error occurs during the operation of the SP, if there is no capture processing, resulting in the final operation failure, then the higher the recovery cost of the SP in the front end of the entire dependent link.

The above three problems can finally be solved by a log table. In addition, in order to achieve self-increasing serial numbers, an additional serial number table is built, with a total of 2 tables.

I have uploaded the code of the SP log tool to Github to facilitate myself and everyone to read. And because I particularly like the Awesome series on Github, I bumped into porcelain on the way, and this code base is named

Awesome-stored-procedure

Https://github.com/NicholasQu/awesome-stored-procedure

At present, they are all written based on MYSQL, so consider perfecting other databases when you have time. Next, let's briefly introduce these Function / Procedure.

FUNCTION getSeqNo

/ *

* use examples

* /

Select getSeqNo ('mall'), getSeqNo (' pay')

Function: according to the serial number category, obtain the global self-increasing serial number.

The cross-operation of different categories will cause the sequence number to jump empty, and this function does not guarantee the continuity of the sequence number within the same category.

PROCEDURE logSp*

/ *

* use examples

* /

Call logSpStart ('sp1','testing sp1',100,@sp_exec_no)

Call logSpStartAndCheck ('sp1','testing sp1',100,'sp2', date_add (now (), interval-1 minute), now (), @ sp_exec_no)

Call logSpEnd ('sp1','end sp1', @ sp_exec_no)

SP startup and prerequisite check

Used to record the SP startup time and status, and to check that the conditions under which you can continue to run are met:

1. Its own running frequency is high, whether it has not finished running before. In this case, just call logSpStart.

two。 Rely on other SP to run to complete, can start itself. In this case, call logSpStartAndCheck.

SP debugging information printing

Used to print debug logs at the Info level and call logSpInfo

SP end log print logSpEnd

Used to indicate that the SP has finished running. Note that only the completion of the SP is identified here, and it may not be successful. There may be an exception in the entire SP intermediate process, but it is finished running.

SP exception handling

Handle the exception. There are two ways:

1. If the exception is innocuous and you can continue with the following logic, use logSpErrAndContinue

two。 If the exception can directly cause SP to terminate, call logSpErrAndEnd

Standard SP writing method

Refer to p_sp_demo.MYSQL.SP.sql

Https://github.com/NicholasQu/awesome-stored-procedure/blob/master/recordSpLogs/p_sp_demo.MYSQL.SP.sql

This is the end of the article on "sample Analysis released by awesome-stored-procedure in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.