Radicore Forum: Bug Reports » Application Crashes Due to Duplicated Concurrent INSERT Transactions

Home » RADICORE development » Bug Reports » Application Crashes Due to Duplicated Concurrent INSERT Transactions

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5886]

Tue, 20 September 2016 01:25

kong
Messages: 90
Registered: December 2011

Member

This is further on the issue that was brought up here http:// radicore.org/fud/index.php?t=msg&th=2283&goto=5883&a mp;a mp;a mp;a mp;#msg_5883

Multiple submits of the same ADD1 forms will lead to concurrent INSERT transactions in the database. If the table uses non auto-increment primary keys or has other unique key constraints, this could result in fatal error thrown by the database (in this case MySQL) and application crash.

The checks built into the framework as described here http:// www.tonymarston.net/php-mysql/functions-and-variables.html#n otes._dml_insertrecord should have prevented such crash and presented the user with user-friendly error messages instead. However, under certain race conditions which are hard to replicate, multiple submits of the same form in rapid succession could result in concurrent transactions invoked by calling startTransaction() function followed by insertRecord() method in the controller script for every click on the submit button. If you are unlucky, you could end up with these concurrent insertRecord transactions all working off the same pre-transaction snapshot of the database table and hence each transaction passing the above mentioned framework built-in checks without raising any errors, until one transaction has been committed successfully but then is followed by another transaction's INSERT which in turn makes the database throw a Duplicate Key Fatal Error, crashing the application.

To solve this problem, there are several options I can think of:
1) Explicitly set database table lock before the transaction starts by customizing _cm_getDatabaseLock() for every table with non auto-increment primary key or other unique keys.
2) Use a hidden field token to guard against multiple submissions of the same form, for example as shown in http://docstore.mik.ua/orelly/webprog/pcook/ch09_06.htm and http://phpsense.com/2006/prevent-duplicate-form-submission/.
3) Since we are relying on the framework for validations and unique key constraint checking anyway, we might as well use a different SQL insert command that does not throw Unique Key Fatal Errors on insert. For example in MySQL use INSERT IGNORE instead of INSERT.
4) Temporary lower the Isolation Level for Transactions in the database before the transaction begins. For example, in MySQL, lower from default level 3 'REPEATABLE READ' to level 1 'READ UNCOMMITTED'.

In my case I am using option 3 as that is least amount of work. But for the long run and improving robustness of the framework I think the 2nd option could be worth some effort.

[Updated on: Tue, 20 September 2016 04:01]

Report message to a moderator

Re: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5888 is a reply to message #5886]

Tue, 20 September 2016 05:59

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

Firstly, you should train your users not to keep repeatedly pressing the submit button in the hope that it will make things go faster. This issue is not limited to the Radicore framework as it is eactly the same for *ALL* web applications.

Secondly, you are missing an option. I have seen some websites which, when a submit button is pressed, use javascript to disable the button. The button is not enabled until the operation started by that button is completed and the form is refreshed.

Thirdly, using an option which requires comparing a value in the form with a value stored in the session data would still not be foolproof as the second submission could read the session data before the first submission has updated it.

In all the years that the Radicore framework has been used this issue has never appeared. It sounds like the only sure-fire option would be to lock the entire table before each unsert so that concurrent inserts are then impossible. However, the side effect of this would be to slow down the system for all those users who are adding records to the same table.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

jQuery Solution: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5919 is a reply to message #5888]

Sat, 01 October 2016 03:08

kong
Messages: 90
Registered: December 2011

Member

Using jQuery it is quite effective to disable a button after the first click and show a spinner to indicate that the application is processing the request.

For those interested, let me summarize:

1) Get an image file for the spinner you like to use and save to radicore/css/ folder. Attached is the image I used (spin-red.svg).

2) Download jQuery file from https://jquery.com/download/ and save to radicore/javascript/ folder. Attached is the jQuery file I used (jquery-3.1.1.min.js).

3) For each module that you would like to implement this solution for, add the following to the module's style_custom.css file:

.loadingspinner
{
    background:
        url("../css/spin-red.svg")
        no-repeat
        center center;
}

4) Add the following to the *.class.inc files of the tables you need this solution for (Also check you have fixed http:// radicore.org/fud/index.php?t=msg&th=2299&start=0& ;)

    function _cm_setJavaScript ($javascript)
        // insert any javascript to be included in the <HEAD> or <BODY> elements.
    {
        $javascript['head'][]['file'] = '../javascript/jquery-3.1.1.min.js';

        $javascript['foot'][]['code'] =  "
            $('form :submit').click( 
                function () { 
                    \$(this).
                    addClass('loadingspinner').
                    prop('disabled', true).
                    closest('form').
                    append(\$('<input/>', {
                        type: 'hidden', 
                        name: this.name, 
                        value: this.value
                    })).
                    submit();
                }
            );";

        return $javascript;

    } // _cm_setJavaScript

That's all, enjoy!

References:
http:// stackoverflow.com/questions/22509343/prevent-double-form-sub mission
http:// stackoverflow.com/questions/12837335/how-to-display-loading- spinner-in-a-textbox-on-clicking-of-a-button

Attachment: jquery_solution.7z
(Size: 28.23KB, Downloaded 986 times)

Report message to a moderator

Re: jQuery Solution: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5921 is a reply to message #5919]

Sat, 01 October 2016 04:20

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

That looks very interesting. When I get the time I will check it out.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: jQuery Solution: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5941 is a reply to message #5919]

Wed, 05 October 2016 13:09

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

Attached is a file that will make it possible to define in one file any javascript which is to be applied on every form and optionally for every field. The zip file contains the following:

custom_javascript.class.inc - to be placed in your 'includes' directory
include.xml.php5.inc - modified to read this file (if it exists)

I have tried this, and although it works OK in Firefox it doesn't work in Internet Explorer.

Attachment: custom_javascript.class.zip
(Size: 19.52KB, Downloaded 985 times)

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: jQuery Solution: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5946 is a reply to message #5941]

Wed, 05 October 2016 19:32

kong
Messages: 90
Registered: December 2011

Member

Made some changes for IE, please update $javascript['foot'][]['code'] to the following:

            $javascript['foot'][]['code'] =  "
                $('form :submit').click(function () {
                    \$(this).
                    prop('disabled', true).
                    closest('form').
                    append(\$('<input/>', {
                        type: 'hidden',
                        name: this.name,
                        value: this.value
                    })).
                    submit();
                    \$(this).addClass('loadingspinner');
                    event.preventDefault();
                });";

IE11 does not support svg file, so I attached a gif file for the spinner. Extract and save spin-red.gif file to radicore/css folder and update default.css or whatever css file you use:

.loadingspinner
{
  background:
          url("spin-red.gif")
          no-repeat
          center center;
}

I have tested this successfully on:
Chrome 53
Firefox 49
Waterfox 48
IE 11

Attachment: spin-red.7z
(Size: 0.64KB, Downloaded 966 times)

Report message to a moderator

Re: jQuery Solution: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5948 is a reply to message #5946]

Thu, 06 October 2016 05:29

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

That works fine in IE now. Thank you for your efforts. I shall incorporate this into the standard product and include it in the next release. I shall mention this contribution on my "Credits" page at http://www.radicore.org/credits.php

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

[Updated on: Thu, 06 October 2016 05:50]

Report message to a moderator

Re: jQuery Solution: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5950 is a reply to message #5948]

Thu, 06 October 2016 11:35

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

I have found a slight problem with this approach - if a LIST page has a navigation button which sends back a CSV file to the client, the navigation button is disabled with the spnning image, but when the CSV file is closed the LIST page is not refreshed and the spinner does not disappear. This also means that the navigation button is disabled and cannot be pressed again. The only way around this is to click on another button or hyperlink.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: jQuery Solution: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5956 is a reply to message #5950]

Fri, 07 October 2016 03:17

kong
Messages: 90
Registered: December 2011

Member

As reference, here is a good description of the cause of the problem related to downloads in general: http:// gruffcode.com/2010/10/28/detecting-the-file-download-dialog- in-the-browser/

In short: For any actions that do not result in a page refresh or redirect, we need to make use of a temporary cookie, which when detected in the browser will trigger jQuery to clean up the screen (meaning get rid of previous spinners and re-enable buttons).

Here are the steps to implement this update:

1) Download and extract 2 files from the attachment. An updated custom_javascript.class.inc and a new file jquery.cookie.min.js which should be saved in the radicore/javascript folder.

2) In std.output1.inc and std.output4.inc, find this line of code:

header("Content-type: text/comma-separated-values");

and change it to:

setcookie('clean_up_screen_' . session_name(), $GLOBALS['task_id'], time()+30);
header("Content-type: text/comma-separated-values");

When these transactions are activated they will set a 30 seconds cookie by session_name and by controller path upon start of download, which is the signal for the browser to clean up the screen without the need for screen refresh. The cookie will be deleted after each clean up. So far, I have only identified these 2 patterns that need this fix. Please let me know of other transaction patterns that also do not result in page reloads or redirects, as those will need similar fix as well.

I have checked this on
Chrome 53
Firefox 49
Waterfox 48
IE 11

Attachment: fix_csv.7z
(Size: 2.12KB, Downloaded 929 times)

Report message to a moderator

Re: jQuery Solution: Application Crashes Due to Duplicated Concurrent INSERT Transactions [message #5957 is a reply to message #5956]

Fri, 07 October 2016 05:48

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

That works brilliantly! I cannot see any other patterns that would need this change. The patterns for PDF files use a different mechanism so do not have the same problem.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Previous Topic:	Localization of task_desc in workitem
Next Topic:	Outer Database Popup File Missing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Thu Nov 21 09:24:59 EST 2024

Total time taken to generate the page: 0.01237 seconds