Implement “account” transactions in postgresql












1















I am looking into on different ways a simple account functionality that can be implemented in Postgresql 9.5. I would like to be able to do it with maximum of "Repeatable read" as transaction isolation level. The reason why it should be "Repeatable read" isolation level is that we have detected a high level of collisions "serializable" isolation level and even with a retry strategy around 10 times within 10 seconds it still fails because of the amount of collisions.




  • Deposit money

  • Withdraw money

  • Cannot withdraw money if balance of the user is less than zero (sum of amount for user)

  • Balance must never be able to go below zero (sum of amount for user)


It should handle multiple connections and multiple users and the same user can initiate deposits and withdraws in different connections.



The only solution I currently have is to do something like the following:



For deposits:




  1. Allow deposit money directly as an insert.


For withdraws:




  1. Check balance > amount of withdraw before continue.

  2. Insert withdraw money transaction with status initiated.

  3. Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.

  4. Update withdraw money transaction status completed if status is initiated.


Any materials or examples are welcomed.










share|improve this question














bumped to the homepage by Community 10 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Can you ever have negative balances?

    – Evan Carroll
    Jan 3 '17 at 16:39











  • I'm also not sure what you mean with status initialized/failed

    – Evan Carroll
    Jan 3 '17 at 20:27











  • what solution did you find for this?

    – frostymarvelous
    Jun 11 '18 at 18:23
















1















I am looking into on different ways a simple account functionality that can be implemented in Postgresql 9.5. I would like to be able to do it with maximum of "Repeatable read" as transaction isolation level. The reason why it should be "Repeatable read" isolation level is that we have detected a high level of collisions "serializable" isolation level and even with a retry strategy around 10 times within 10 seconds it still fails because of the amount of collisions.




  • Deposit money

  • Withdraw money

  • Cannot withdraw money if balance of the user is less than zero (sum of amount for user)

  • Balance must never be able to go below zero (sum of amount for user)


It should handle multiple connections and multiple users and the same user can initiate deposits and withdraws in different connections.



The only solution I currently have is to do something like the following:



For deposits:




  1. Allow deposit money directly as an insert.


For withdraws:




  1. Check balance > amount of withdraw before continue.

  2. Insert withdraw money transaction with status initiated.

  3. Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.

  4. Update withdraw money transaction status completed if status is initiated.


Any materials or examples are welcomed.










share|improve this question














bumped to the homepage by Community 10 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Can you ever have negative balances?

    – Evan Carroll
    Jan 3 '17 at 16:39











  • I'm also not sure what you mean with status initialized/failed

    – Evan Carroll
    Jan 3 '17 at 20:27











  • what solution did you find for this?

    – frostymarvelous
    Jun 11 '18 at 18:23














1












1








1








I am looking into on different ways a simple account functionality that can be implemented in Postgresql 9.5. I would like to be able to do it with maximum of "Repeatable read" as transaction isolation level. The reason why it should be "Repeatable read" isolation level is that we have detected a high level of collisions "serializable" isolation level and even with a retry strategy around 10 times within 10 seconds it still fails because of the amount of collisions.




  • Deposit money

  • Withdraw money

  • Cannot withdraw money if balance of the user is less than zero (sum of amount for user)

  • Balance must never be able to go below zero (sum of amount for user)


It should handle multiple connections and multiple users and the same user can initiate deposits and withdraws in different connections.



The only solution I currently have is to do something like the following:



For deposits:




  1. Allow deposit money directly as an insert.


For withdraws:




  1. Check balance > amount of withdraw before continue.

  2. Insert withdraw money transaction with status initiated.

  3. Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.

  4. Update withdraw money transaction status completed if status is initiated.


Any materials or examples are welcomed.










share|improve this question














I am looking into on different ways a simple account functionality that can be implemented in Postgresql 9.5. I would like to be able to do it with maximum of "Repeatable read" as transaction isolation level. The reason why it should be "Repeatable read" isolation level is that we have detected a high level of collisions "serializable" isolation level and even with a retry strategy around 10 times within 10 seconds it still fails because of the amount of collisions.




  • Deposit money

  • Withdraw money

  • Cannot withdraw money if balance of the user is less than zero (sum of amount for user)

  • Balance must never be able to go below zero (sum of amount for user)


It should handle multiple connections and multiple users and the same user can initiate deposits and withdraws in different connections.



The only solution I currently have is to do something like the following:



For deposits:




  1. Allow deposit money directly as an insert.


For withdraws:




  1. Check balance > amount of withdraw before continue.

  2. Insert withdraw money transaction with status initiated.

  3. Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.

  4. Update withdraw money transaction status completed if status is initiated.


Any materials or examples are welcomed.







postgresql






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 30 '16 at 15:55









Dennis SoegaardDennis Soegaard

213




213





bumped to the homepage by Community 10 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 10 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • Can you ever have negative balances?

    – Evan Carroll
    Jan 3 '17 at 16:39











  • I'm also not sure what you mean with status initialized/failed

    – Evan Carroll
    Jan 3 '17 at 20:27











  • what solution did you find for this?

    – frostymarvelous
    Jun 11 '18 at 18:23



















  • Can you ever have negative balances?

    – Evan Carroll
    Jan 3 '17 at 16:39











  • I'm also not sure what you mean with status initialized/failed

    – Evan Carroll
    Jan 3 '17 at 20:27











  • what solution did you find for this?

    – frostymarvelous
    Jun 11 '18 at 18:23

















Can you ever have negative balances?

– Evan Carroll
Jan 3 '17 at 16:39





Can you ever have negative balances?

– Evan Carroll
Jan 3 '17 at 16:39













I'm also not sure what you mean with status initialized/failed

– Evan Carroll
Jan 3 '17 at 20:27





I'm also not sure what you mean with status initialized/failed

– Evan Carroll
Jan 3 '17 at 20:27













what solution did you find for this?

– frostymarvelous
Jun 11 '18 at 18:23





what solution did you find for this?

– frostymarvelous
Jun 11 '18 at 18:23










1 Answer
1






active

oldest

votes


















0
















  • Good Idea For deposits: Allow deposit money directly as an insert.


  • Bad Idea For withdraws: Check balance > amount of withdraw before continue.


    • Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.




I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?



BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE name = 'Bob';
UPDATE accounts
SET balance = balance + 100
WHERE name = 'Sally';
END;



  • If the balance on accounts would go under 0 with the deduction, it'll simply fail there.

  • If it passes then you will see bob's balance go down for your transaction.

  • Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.


The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that table until the transaction is committed.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f156791%2fimplement-account-transactions-in-postgresql%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0
















    • Good Idea For deposits: Allow deposit money directly as an insert.


    • Bad Idea For withdraws: Check balance > amount of withdraw before continue.


      • Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.




    I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?



    BEGIN;
    UPDATE accounts
    SET balance = balance - 100
    WHERE name = 'Bob';
    UPDATE accounts
    SET balance = balance + 100
    WHERE name = 'Sally';
    END;



    • If the balance on accounts would go under 0 with the deduction, it'll simply fail there.

    • If it passes then you will see bob's balance go down for your transaction.

    • Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.


    The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that table until the transaction is committed.






    share|improve this answer




























      0
















      • Good Idea For deposits: Allow deposit money directly as an insert.


      • Bad Idea For withdraws: Check balance > amount of withdraw before continue.


        • Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.




      I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?



      BEGIN;
      UPDATE accounts
      SET balance = balance - 100
      WHERE name = 'Bob';
      UPDATE accounts
      SET balance = balance + 100
      WHERE name = 'Sally';
      END;



      • If the balance on accounts would go under 0 with the deduction, it'll simply fail there.

      • If it passes then you will see bob's balance go down for your transaction.

      • Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.


      The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that table until the transaction is committed.






      share|improve this answer


























        0












        0








        0









        • Good Idea For deposits: Allow deposit money directly as an insert.


        • Bad Idea For withdraws: Check balance > amount of withdraw before continue.


          • Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.




        I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?



        BEGIN;
        UPDATE accounts
        SET balance = balance - 100
        WHERE name = 'Bob';
        UPDATE accounts
        SET balance = balance + 100
        WHERE name = 'Sally';
        END;



        • If the balance on accounts would go under 0 with the deduction, it'll simply fail there.

        • If it passes then you will see bob's balance go down for your transaction.

        • Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.


        The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that table until the transaction is committed.






        share|improve this answer















        • Good Idea For deposits: Allow deposit money directly as an insert.


        • Bad Idea For withdraws: Check balance > amount of withdraw before continue.


          • Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.




        I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?



        BEGIN;
        UPDATE accounts
        SET balance = balance - 100
        WHERE name = 'Bob';
        UPDATE accounts
        SET balance = balance + 100
        WHERE name = 'Sally';
        END;



        • If the balance on accounts would go under 0 with the deduction, it'll simply fail there.

        • If it passes then you will see bob's balance go down for your transaction.

        • Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.


        The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that table until the transaction is committed.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 3 '17 at 21:03









        Evan CarrollEvan Carroll

        31.8k967216




        31.8k967216






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f156791%2fimplement-account-transactions-in-postgresql%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh