OLTP games
Recently, the topic of implementing high-performance applications has become popular on Habré. We also decided to experiment a little bit in this direction and share the current results of our research.
The “Hello, world!” Test subject is the simplest OLTP system:
')
Performance and resiliency requirements are key to such systems. Therefore, the search for a solution to the problem was carried out in the direction of: C, C ++, fastcgi, nginx, lighttpd, oracle. First of all, we were curious to try various options for building OLTP on these technologies, as well as measure performance and peak loads.
It is necessary to process transactions of the form: transfer X cu (money, but what did you think) from account A to account B. In order not to invent a bicycle and ensure scalability, the HTTP protocol was selected for interaction with the client.
server.com/paysys.request?account=123&operator=456&money=789
which means: transfer 789 money from the account of the operator 456 to the account of the user 123.
There are only three labels in the database:
(Figure 1. Database structure)
The transaction procedure is not much more complicated than the database structure:
The most ideologically and practically correct approach to building a high-load server application with a large number of simultaneous connections is the implementation of the multiplexer ( The C10K problem ).
We decided to take the "unconventional" Oracle 10.2 Express Edition as the database. Transaction (update) in the database we performed for ~ 2 ms . At times, when Oracle flushed data to disk (apparently dbwr was writing modified data blocks from the buffer cache), the transaction was delayed to ~ 20 ms .
To connect to the database, we decided to use libraries from the manufacturer. As such, within our task, there were 2 jokes:
Immediately make a reservation that in setting up Oracle, we are far from Tom Kite, therefore, with tuning the database and operating system did not bother much. Let's do it when we move to a full server.
For the purity of the experiment, ~ 100k records were recorded in each tablet.
How to build such a system? We first came to mind the simplest solution based on the FastCGI protocol. I think it’s not worth explaining once again how FastCGI differs from its progenitor CGI. For a high-loaded application, this difference is fundamental. I would like to dwell on another nuance, which we found out far from immediately.
The FastCGI specification provides for the preservation of a user connection for the possibility of implementing multiplexed processing of several requests simultaneously. In simple terms, you can call an asynchronous operation in the request handler and calmly give the processor time to other clients, and at the next iteration of the polling cycle get the result of the called operation and, sending the answer to the client, close the connection. Unfortunately, nginx and lighttpd (as well as the libfcgi and fastcgipp libraries) do not yet support this, demanding to close the connection before processing the next client.
However, in this example, we limited ourselves to only one short query to the database, which can be fully executed simultaneously, without fear of any significant loss in performance.
Nginx and lighttpd allow you to interact with FastCGI by two methods: via TCP channel and Unix Domain Socket. The advantage of the first approach is the possibility of clustering FastCGI servers, the disadvantage is the relatively high overhead of data transfer (TCP stack though). UDS, however, works only locally, due to which higher performance is ensured.
I would like to say a lot of “warm” words about the second method. After the first tests, it was found that almost a tenth of requests with more than 200 clients (on our hardware) reach the FastCGI application. The snag was in the net.unux.max_dgram_qlen kernel variable, which limits the size of the request queue when writing to the socket. By default, this option was 10, which explained the loss of requests. After setting the value to 10,000, everything seemed to return to normal. But it was not there. Almost a tenth of the requests were guaranteed to fall off, and nginx wrote terrible words “connect () to socket failed (11: Resource temporary unavailable)” in logia . In Russian, with a large number of simultaneous requests, the socket was “choking”. We have not yet been able to solve this problem. Although productivity has increased, but who needs it, when about 10% of transactions are completed with the “502” code.
Used siege and ab. Due to memory problems, siege flatly refused to make more than 380 requests per second, while ab “squeezed” up to 1000 requests, but then referred to an excessive number of open sockets.
For the reliability of the results, testing was carried out according to the scheme of 10,000 queries at 100, 200, 300, 400 simultaneous. For FastCGI on this machine with default kernel settings, the loads were enough to overcome the critical threshold of 10% of lost requests. The lighttpd + fastcgi bundle began to “stumble” at ~ 200 requests, while the nginx bundle held up to ~ 300 simultaneous connections. With a load of more than 400 simultaneous connections, both FastCGI options fell off with the message “apr_poll: The timeout specified has expired (70007)”, while the nginx module without any screaming evened out with 1000 simultaneous connections via ab. The tests, again, rested against the "iron". For clarity, we decided to present the measurement results in the form of graphs.
So, on the x-axis - the number of simultaneous requests, on the y-axis - in accordance with the caption to the chart.
(Figure 2. The longest query, s)
(Figure 3. Average request processing time, sec.)
(Figure 4. The number of requests processed per second)
(Figure 5. Time spent on processing 10,000 requests, sec.)
Resource limitations did not allow for overwhelming results, but we believe that comparative testing was entirely successful. Test results do not claim to be complete, but in general, in our opinion, confirm the expectations. Surely leading domestic athlete with a minimum of additional equipment. What a huge respect for him and his coach! In conjunction with FastCGI, he again bypasses a little bit of his foreign rival.
In our experiments, the mechanism of asynchronous calls to the database has not yet been used. It is curious how the test results will change if we complicate the processing of the client to several calls to the database and implement a full multiplexer. On FastCGI, this will not work, but this can be done with the nginx module, and also for completeness of the experiments, to call into the ranks of the "experimental" own HTTP daemon based on a library like poco, asio, pion ...
In general, to be continued ...
UPD
Nginx modules are server extensions statically compiled with it. Built-in functionality like SSL and FastCGI is also implemented as modules. Plus - speed, minus - the complexity of the update on a running machine. Excellent manuals are here .
A stripped-down example is below. For a change, work with ORACLE via the OCI C-library.
The “Hello, world!” Test subject is the simplest OLTP system:
')
Performance and resiliency requirements are key to such systems. Therefore, the search for a solution to the problem was carried out in the direction of: C, C ++, fastcgi, nginx, lighttpd, oracle. First of all, we were curious to try various options for building OLTP on these technologies, as well as measure performance and peak loads.
And so, the conditions of the problem
It is necessary to process transactions of the form: transfer X cu (money, but what did you think) from account A to account B. In order not to invent a bicycle and ensure scalability, the HTTP protocol was selected for interaction with the client.
Requests are
server.com/paysys.request?account=123&operator=456&money=789
which means: transfer 789 money from the account of the operator 456 to the account of the user 123.
There are only three labels in the database:
(Figure 1. Database structure)
The transaction procedure is not much more complicated than the database structure:
procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .
procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .procedure PROCESS_TRANS(p_account_id in number, p_operator_id in number, p_sum in number, o_result out number) is l_cnt number; cursor cur_account is select id from pay_account where id = p_account_id for update of balance; cursor cur_operator is select id from pay_operator where id = p_operator_id and total >= p_sum for update of total; begin o_result := -1; -- tansaction begins select count (1) -- if such operator exist? into l_cnt from pay_operator opr where opr.id = p_operator_id; if (l_cnt != 1) then o_result := -2; -- Operator not found! return ; end if ; open cur_account; open cur_operator; fetch cur_account into l_cnt; -- need to fetch to update rows later fetch cur_operator into l_cnt; -- 0/1 row in cursor as selected by prim_key if cur_account%notfound then o_result := -3; -- Account not found! elsif cur_operator%notfound then o_result := -4; -- Operator has not enough money else update pay_account set balance = (balance + p_sum) where current of cur_account; update pay_operator set total = (total - p_sum) where current of cur_operator; insert into pay_transaction ( id, account_id, operator_id, money, datetime ) values ( pay_transaction_seq.nextval, p_account_id, p_operator_id, p_sum, sysdate ); commit ; o_result := 1; -- transaction successfully finished! end if ; close cur_account; close cur_operator; end ; * This source code was highlighted with Source Code Highlighter .
The most ideologically and practically correct approach to building a high-load server application with a large number of simultaneous connections is the implementation of the multiplexer ( The C10K problem ).
We decided to take the "unconventional" Oracle 10.2 Express Edition as the database. Transaction (update) in the database we performed for ~ 2 ms . At times, when Oracle flushed data to disk (apparently dbwr was writing modified data blocks from the buffer cache), the transaction was delayed to ~ 20 ms .
To connect to the database, we decided to use libraries from the manufacturer. As such, within our task, there were 2 jokes:
- Oracle Call Interface ©
- Oracle C ++ Call Interface (C ++)
Ingredients:
- Oracle XE 10.2
- Nginx 0.7.62
- Lighttpd 1.4.23
Immediately make a reservation that in setting up Oracle, we are far from Tom Kite, therefore, with tuning the database and operating system did not bother much. Let's do it when we move to a full server.
For the purity of the experiment, ~ 100k records were recorded in each tablet.
Experiences
How to build such a system? We first came to mind the simplest solution based on the FastCGI protocol. I think it’s not worth explaining once again how FastCGI differs from its progenitor CGI. For a high-loaded application, this difference is fundamental. I would like to dwell on another nuance, which we found out far from immediately.
The FastCGI specification provides for the preservation of a user connection for the possibility of implementing multiplexed processing of several requests simultaneously. In simple terms, you can call an asynchronous operation in the request handler and calmly give the processor time to other clients, and at the next iteration of the polling cycle get the result of the called operation and, sending the answer to the client, close the connection. Unfortunately, nginx and lighttpd (as well as the libfcgi and fastcgipp libraries) do not yet support this, demanding to close the connection before processing the next client.
However, in this example, we limited ourselves to only one short query to the database, which can be fully executed simultaneously, without fear of any significant loss in performance.
Nginx and lighttpd allow you to interact with FastCGI by two methods: via TCP channel and Unix Domain Socket. The advantage of the first approach is the possibility of clustering FastCGI servers, the disadvantage is the relatively high overhead of data transfer (TCP stack though). UDS, however, works only locally, due to which higher performance is ensured.
I would like to say a lot of “warm” words about the second method. After the first tests, it was found that almost a tenth of requests with more than 200 clients (on our hardware) reach the FastCGI application. The snag was in the net.unux.max_dgram_qlen kernel variable, which limits the size of the request queue when writing to the socket. By default, this option was 10, which explained the loss of requests. After setting the value to 10,000, everything seemed to return to normal. But it was not there. Almost a tenth of the requests were guaranteed to fall off, and nginx wrote terrible words “connect () to socket failed (11: Resource temporary unavailable)” in logia . In Russian, with a large number of simultaneous requests, the socket was “choking”. We have not yet been able to solve this problem. Although productivity has increased, but who needs it, when about 10% of transactions are completed with the “502” code.
FastCGI server:
* This source code was highlighted with Source Code Highlighter .
- const char * bind_address = ": 9000" ;
- const char * db_user_name = "orauser" ;
- const char * db_password = "pass" ;
- const char * db_conn_str = "comp: 1521 / xe" ;
- int main ( int argc, char * const argv [])
- {
- using namespace oracle :: occi;
- int listenQueueBacklog = 4000;
- FCGX_Request request;
- if (FCGX_Init ())
- exit (1);
- int listen_socket = FCGX_OpenSocket (bind_address, listenQueueBacklog);
- if (listen_socket <0)
- exit (1);
- if (fchmod (listen_socket, S_IROTH | S_IWOTH))
- exit (1);
- if (FCGX_InitRequest (& request, listen_socket, 0)) exit (1);
- Environment * env = Environment :: createEnvironment (Environment :: DEFAULT);
- Connection * conn = env-> createConnection (db_user_name, db_password, db_conn_str);
- Statement * stmt = conn-> createStatement (
- "BEGIN PAY_SYS.PROCESS_TRANS (: v1,: v2,: v3,: v4); END;" );
- while (FCGX_Accept_r (& request) == 0)
- {
- bool noException = false , succesful = false ;
- long start = GetTickCount ();
- char * params = FCGX_GetParam ( "QUERY_STRING" , request.envp);
- int operatorID, accountID, moneySum, result = -1;
- operatorID = GetIntParam ( params , "operator =" );
- accountID = GetIntParam ( params , "account =" );
- moneySum = GetIntParam ( params , "money =" );
- if (operatorID <= 0 || accountID <= 0 || moneySum <= 0)
- {
- FCGX_FPrintF (request. Out , "HTTP / 1.0 503 Params Error \ n" );
- FCGX_FPrintF (request. Out , "Content-type: text / html \ r \ n \ r \ n" );
- FCGX_FPrintF (request. Out , "wrong params <br> \ n" );
- FCGX_Finish_r (& request);
- continue ;
- }
- try
- {
- stmt-> setInt (1, accountID);
- stmt-> setInt (2, operatorID);
- stmt-> setInt (3, moneySum);
- stmt-> registerOutParam (4, OCCIINT, sizeof (result));
- stmt-> execute ();
- result = stmt-> getInt (4);
- if (result == 1)
- succesful = true ;
- }
- catch (SQLException & sqlExcp)
- {
- error_log (sqlExcp.getMessage (). c_str ());
- }
- if (succesful)
- {
- FCGX_FPrintF (request. Out , "HTTP / 1.0 200 OK \ n" );
- FCGX_FPrintF (request. Out , "Content-type: text / html \ r \ n \ r \ n" );
- FCGX_FPrintF (request. Out , "SQL result =% d" , result);
- }
- else
- {
- FCGX_FPrintF (request. Out , "HTTP / 1.0 503 DatabaseError \ n" );
- FCGX_FPrintF (request. Out , "Content-type: text / html \ r \ n \ r \ n" );
- FCGX_FPrintF (request. Out , "Database error occured" );
- error_log ( "db error" );
- }
- FCGX_Finish_r (& request);
- }
- conn-> terminateStatement (stmt);
- env-> terminateConnection (conn);
- Environment :: terminateEnvironment (env);
- return 0;
- }
Total
Used siege and ab. Due to memory problems, siege flatly refused to make more than 380 requests per second, while ab “squeezed” up to 1000 requests, but then referred to an excessive number of open sockets.
For the reliability of the results, testing was carried out according to the scheme of 10,000 queries at 100, 200, 300, 400 simultaneous. For FastCGI on this machine with default kernel settings, the loads were enough to overcome the critical threshold of 10% of lost requests. The lighttpd + fastcgi bundle began to “stumble” at ~ 200 requests, while the nginx bundle held up to ~ 300 simultaneous connections. With a load of more than 400 simultaneous connections, both FastCGI options fell off with the message “apr_poll: The timeout specified has expired (70007)”, while the nginx module without any screaming evened out with 1000 simultaneous connections via ab. The tests, again, rested against the "iron". For clarity, we decided to present the measurement results in the form of graphs.
So, on the x-axis - the number of simultaneous requests, on the y-axis - in accordance with the caption to the chart.
(Figure 2. The longest query, s)
(Figure 3. Average request processing time, sec.)
(Figure 4. The number of requests processed per second)
(Figure 5. Time spent on processing 10,000 requests, sec.)
Conclusion.
Resource limitations did not allow for overwhelming results, but we believe that comparative testing was entirely successful. Test results do not claim to be complete, but in general, in our opinion, confirm the expectations. Surely leading domestic athlete with a minimum of additional equipment. What a huge respect for him and his coach! In conjunction with FastCGI, he again bypasses a little bit of his foreign rival.
In our experiments, the mechanism of asynchronous calls to the database has not yet been used. It is curious how the test results will change if we complicate the processing of the client to several calls to the database and implement a full multiplexer. On FastCGI, this will not work, but this can be done with the nginx module, and also for completeness of the experiments, to call into the ranks of the "experimental" own HTTP daemon based on a library like poco, asio, pion ...
In general, to be continued ...
UPD
Nginx modules are server extensions statically compiled with it. Built-in functionality like SSL and FastCGI is also implemented as modules. Plus - speed, minus - the complexity of the update on a running machine. Excellent manuals are here .
A stripped-down example is below. For a change, work with ORACLE via the OCI C-library.
* This source code was highlighted with Source Code Highlighter .
- // option WITHOUT setting support via nginx.conf and error PROCESSING
- #include <ngx_config.h>
- #include <ngx_core.h>
- #include <ngx_http.h>
- #include < string .h>
- #include <oci.h>
- const text * db_user_name = ( const text *) "orauser" ;
- const text * db_password = ( const text *) "pass" ;
- const text * db_conn_str = ( const text *) "comp: 1521 / xe" ;
- const text * command = ( const text *)
- "BEGIN PAY_SYS.PROCESS_TRANS (: v1,: v2,: v3,: v4); END;" ;
- OCIEnv * env;
- OCISvcCtx * context;
- OCISession * session;
- OCIServer * server;
- OCIError * error;
- OCIStmt * statement;
- OCIBind * bnd1, * bnd2, * bnd3, * bnd4;
- int operatorID, accountID, sum, result;
- static char * ngx_http_payment_init (ngx_conf_t * cf,
- ngx_command_t * cmd, void * conf);
- // array of possible module options in nginx.conf
- static ngx_command_t ngx_http_payment_commands [] =
- { // set only one main option, including the module in the specified Location
- {ngx_string ( "payment_enabled" ),
- NGX_HTTP_LOC_CONF | NGX_CONF_NOARGS,
- ngx_http_payment_init,
- NGX_HTTP_LOC_CONF_OFFSET,
- 0,
- Null}
- ngx_null_command // terminates an array
- };
- // callback, don't use
- static ngx_http_module_t ngx_http_payment_module_ctx =
- {
- NULL, / * preconfiguration * /
- NULL, / * postconfiguration * /
- NULL, / * create main configuration * /
- NULL, / * init main configuration * /
- NULL, / * create server configuration * /
- NULL, / * merge server configuration * /
- NULL, / * create location configuration * /
- NULL / * merge location configuration * /
- };
- // module handle, includes all parameters
- ngx_module_t ngx_http_payment_module =
- {
- NGX_MODULE_V1,
- & ngx_http_payment_module_ctx, / * module context * /
- ngx_http_payment_commands, / * module directives * /
- NGX_HTTP_MODULE, / * module type * /
- NULL, / * init master * /
- NULL, / * init module * /
- NULL, / * init process * /
- NULL, / * init thread * /
- NULL, / * exit thread * /
- NULL, / * exit process * /
- NULL, / * exit master * /
- NGX_MODULE_V1_PADDING
- };
- void SetCallParams ( int oper, int account, int money)
- {
- if (bnd1! = 0) OCIHandleFree ((dvoid *) bnd1, OCI_HTYPE_BIND);
- if (bnd2! = 0) OCIHandleFree ((dvoid *) bnd2, OCI_HTYPE_BIND);
- if (bnd3! = 0) OCIHandleFree ((dvoid *) bnd3, OCI_HTYPE_BIND);
- if (bnd4! = 0) OCIHandleFree ((dvoid *) bnd4, OCI_HTYPE_BIND);
- // copy from the stack to be able to pass a valid pointer
- result = 0;
- operatorID = oper;
- accountID = account;
- sum = money;
- // bind the parameters to the SQL call
- OCIBindByPos (statement, & bnd1, error, 1, & accountID,
- sizeof (accountID), SQLT_INT, (dvoid *) 0,
- (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
- OCIBindByPos (statement, & bnd2, error, 2, & operatorID,
- sizeof (operatorID), SQLT_INT, (dvoid *) 0,
- (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
- OCIBindByPos (statement, & bnd3, error, 3, & sum, sizeof (sum), SQLT_INT,
- (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0,
- (ub4 *) 0, OCI_DEFAULT);
- OCIBindByPos (statement, & bnd4, error, 4, & result, sizeof (result), SQLT_INT,
- (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
- }
- // main handler, all work is done here
- static ngx_int_t ngx_http_payment_handler (ngx_http_request_t * r)
- {
- // clear the body section
- ngx_int_t rc = ngx_http_discard_request_body (r);
- if (rc! = NGX_OK && rc! = NGX_AGAIN)
- {
- ngx_log_error (NGX_LOG_ERR, r-> connection-> log, 0,
- "Failed ngx_http_discard_request_body ()" );
- return rc;
- }
- // strings in nginx are stored as a pair length-data,
- // not as traditional C-style zero-end
- // copy for the parser to work
- const int buflen = 100;
- char buf [buflen + 1];
- int len = (r-> args.len <buflen)? r-> args.len: buflen;
- buf [len] = '\ 0' ;
- ngx_memcpy (buf, r-> args.data, r-> args.len);
- int successful = 0;
- int operator , abonent, money, result = -1;
- // call the simplest parser
- operator = GetIntParam (buf, "operator =" );
- abonent = GetIntParam (buf, "abonent =" );
- money = GetIntParam (buf, "money =" );
- if ( operator > 0 && abonent> 0 && money> 0)
- {
- SetCallParams ( operator , abonent, money);
- int retCode = OCIStmtExecute (
- context, statement, error, (ub4) 1, (ub4) 0,
- (OCISnapshot *) NULL, (OCISnapshot *) NULL,
- (ub4) OCI_COMMIT_ON_SUCCESS);
- if (retCode == OCI_SUCCESS)
- successful = 1;
- }
- r-> headers_out.content_type.len = sizeof ( "text / html" ) - 1;
- r-> headers_out.content_type.data = (u_char *) "text / html" ;
- r-> headers_out.content_length_n = 0;
- if (successful == 1)
- r-> headers_out.status = NGX_HTTP_OK;
- else
- r-> headers_out.status = NGX_HTTP_INTERNAL_SERVER_ERROR;
- // ... forming the body of the response and sending it to nginx .. see emiller examples
- }
- static char * ngx_http_payment_init (ngx_conf_t * cf,
- ngx_command_t * cmd,
- void * our_conf)
- {
- // set nginx handler
- ngx_http_core_loc_conf_t * core_conf =
- ngx_http_conf_get_module_loc_conf (cf, ngx_http_core_module);
- core_conf-> handler = ngx_http_payment_handler;
- // oracle
- bnd1 = (OCIBind *) 0;
- bnd2 = (OCIBind *) 0;
- bnd3 = (OCIBind *) 0;
- bnd4 = (OCIBind *) 0;
- OCIEnvCreate ((OCIEnv **) & env, (ub4) OCI_DEFAULT,
- (dvoid *) 0, (dvoid * (*) (dvoid *, size_t)) 0,
- (dvoid * (*) (dvoid *, dvoid *, size_t)) 0,
- ( void (*) (dvoid *, dvoid *)) 0, (size_t) 0, (dvoid **) 0);
- / * allocate a server handle * /
- OCIHandleAlloc ((dvoid *) env, (dvoid **) & server,
- OCI_HTYPE_SERVER, 0, (dvoid **) 0);
- / * allocate an error handle * /
- OCIHandleAlloc ((dvoid *) env, (dvoid **) & error,
- OCI_HTYPE_ERROR, 0, (dvoid **) 0);
- / * create a server context * /
- int retCode = OCIServerAttach (server, error, db_conn_str,
- strlen (( const char *) db_conn_str), OCI_DEFAULT);
- / * allocate a service handle * /
- retCode = OCIHandleAlloc ((dvoid *) env, (dvoid **) & context,
- OCI_HTYPE_SVCCTX, 0, (dvoid **) 0);
- / * set the server attribute in the service context * /
- retCode = OCIAttrSet ((dvoid *) context, OCI_HTYPE_SVCCTX,
- (dvoid *) server, (ub4) 0, OCI_ATTR_SERVER, error);
- / * allocate a user session handle * /
- retCode = OCIHandleAlloc ((dvoid *) env, (dvoid **) & session,
- OCI_HTYPE_SESSION, 0, (dvoid **) 0);
- // set up user & password for our session
- retCode = OCIAttrSet ((dvoid *) session, OCI_HTYPE_SESSION,
- ( void *) db_user_name, (ub4) strlen (( const char *) db_user_name),
- OCI_ATTR_USERNAME, error);
- retCode = OCIAttrSet ((dvoid *) session, OCI_HTYPE_SESSION,
- ( void *) db_password, (ub4) strlen (( const char *) db_password),
- OCI_ATTR_PASSWORD, error);
- // start session
- retCode = OCISessionBegin (context, error, session,
- OCI_CRED_RDBMS, OCI_DEFAULT);
- / * set the user session attribute in the service context * /
- retCode = OCIAttrSet ((dvoid *) context, OCI_HTYPE_SVCCTX,
- (dvoid *) session, (ub4) 0,
- OCI_ATTR_SESSION, error);
- OCIHandleAlloc ((dvoid *) env, (dvoid **) & statement,
- OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
- OCIStmtPrepare (statement, error, command, (ub4) strlen (( char *) command),
- (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
- return NGX_CONF_OK;
- }
Source: https://habr.com/ru/post/69974/
All Articles