Problems with large transactions in an OLTP environment

In the application environment, often need to ensure the consistency of several tables related data, in order to deal with this demand, development engineers often use transactions, some insert,update and other statements tied together to form a transaction (Transaction), such as the following pseudo-code example (transaction 1):

begin transaction
insert into messgae(id,col1,col2...) values(#id,#col1,#col2...);
update thread set post_count=nvl(post_count,0) + 1 where id=#v_id;
update test_user set sum_reply = sum_reply + 1 where userid=#v_userid;
end transaction

In order to realize the need to apply data integrity, the development engineer has to bind these several SQL together to form a big business, and there is no problem. In the early stages of OLTP business development, this design does not encounter any problems and can maximize the integrity of the application-tier data. However, with the development of OLTP traffic, the rapid growth of concurrent traffic, this big business began to appear problems, blocking phenomenon is very serious, resulting in the Application Server page response time significantly lengthened, and has affected the normal operation of the business! As a result of the investigation, there is also an update SQL (transaction 2) in the system:

Update thread set VIEW_COUNT=NVL (view_count,0) + 1 where id= #v_id;

And this because of the increase in the amount of traffic, the number of executions unusually high. Obviously, when transaction 1 executes, there are multiple statements to execute, longer to execute, and if there is a large number of transaction 2 to execute at this time, I am referring to a record that updates the same ID. Then there will be a lot of enqueue waiting. This is the problem we have to face under the current transaction strategy under the OLTP environment.

At the beginning of a large number of lock Enqueue waiting, transaction 2 execution frequency differs for Transaction 1: Transaction 1 is low and transaction 2 is high. A strategy can be used to reduce the number of database executions for transaction 2, such as putting this update in the cache, updating it to the database at every interval, and reducing the chance of collision between transaction 1 and transaction 2.

With the further development of OLTP, the further increase of concurrent traffic, due to transaction 1 itself is larger, the frequency of transaction 1 will also become more and more high, at that time, transaction 1 and transaction 1 itself blocked, then how do we solve? It seems to be the only way to dismiss a transaction, and here is another view to completely dismiss the transaction, which is to take apart every statement in transaction 1, as shown in the following example:

begin transaction
insert into messgae(id,col1,col2...) values(#id,#col1,#col2...);
end transaction

begin transaction
update thread set post_count=nvl(post_count,0) + 1 where id=#v_id;
end transaction

begin transaction
update test_user set sum_reply = sum_reply + 1 where userid=#v_userid;
end transaction

This scheme can be regarded as the extreme of transaction 1, the complete antithesis of transaction 1, and the consistency of data is completely insecure. If the database is down, or the application server down, the data is likely to be inconsistent. In order to reduce the probability of inconsistent data, you can use a compromise solution to some extent, business is a good thing, it depends on how you use! Analysis of the above transaction 1 business, found the following laws:

Insert INTO Messgae (id,col1,col2 ...) VALUES (#id, #col1, #col2 ...); This SQL can be considered "private" and will not cause blocking (no bitmap index on this table)

Update thread set POST_COUNT=NVL (post_count,0) + 1 where id= #v_id; This SQL is "public", it is easy to have multiple sessions update the same record, easy to block

Update Test_user Set sum_reply = sum_reply + 1 where userid= #v_userid; This SQL updates its own things and can be considered "private" without blocking

Analysis of the above results in another scenario: tying the "public" to a transaction, tying "private" to a transaction, and the original transaction 1 to 2 transactions, as the following example:

begin transaction1_1
update thread set post_count=nvl(post_count,0) + 1 where id=#v_id;
end transaction1_1

begin transaction1_2
insert into messgae(id,col1,col2...) values(#id,#col1,#col2...);
update test_user set sum_reply = sum_reply + 1 where userid=#v_userid;
end transaction1_2