TRANSACTIONS



MySQL - Transactions


Transaction is a group of queries or operations performed at a time on database. Transaction in terms of database is related to commit the results over disk or rollback the results in case of failure. Sometime it is necessary to either run all the queries or no single query.


Properties Of Transactions :

The properties of a transaction in MySQL are abbreviated as ACID (Atomicity, consistency, isolation and durability).


  • Atomicity: Atom is the smallest unit it is either completely exist or not at all. In the same way queries that are grouped into a transaction either runs completely or not run at all. If a single query fails then the whole transaction rolls back.

  • Consistency: Data is updated and visible to users only after complete execution of the transaction, no intermediate values seen by any user which may be rolled back later during transaction.

  • Isolation: Transactions are independent from each other, no other transaction or query can fetch the data or value from another transaction until first transaction is complete.

  • Durability: Ensures that the result always writes into the disk even in the case of system failure. In case of failure it dealt with logs to either redo or undo the whole transaction.


    Transaction Keywords :


    • COMMIT: For confirming the operation and writes the result into the disk from the buffers / logs.

    • ROLLBACK: It persist the data for all tables to its original state if the transaction fails in the middle of its execution.

    • NOTE: INNODB engine is the transactional safe engine so always use INNODB in case of applications where you need ACID compliance.


      EXAMPLE :


      Lets take a table containing records of an Account :


      AccountNumberAccountantNameAccountBalanceAccountBranchAccountBank
      1Nirupam562552HyderabadSOS Bank
      2Yaksh96554KakinadaPOP Bank
      3Dakshya21556VijayawadaLOL Bank
      4Nirakshith65423SulthanabadDOT Bank

      Now,we will perform TRANSACTIONS on this table and check out the results :


      START Transaction;
      UPDATE accountrecord SET AccountBalance=AccountBalance- 1500 where AccountNumber = 1;
      UPDATE accountrecord SET AccountBalance=AccountBalance+ 1500 where  AccountNumber = 2;
      COMMIT;
      

      The result will be produced as follows :


      AccountNumberAccountantNameAccountBalanceAccountBranchAccountBank
      1Nirupam561052HyderabadSOS Bank
      2Yaksh98054KakinadaPOP Bank

      Example :


      START Transaction;
      UPDATE accountrecord SET AccountantName="Nynika" where AccountBank = "SOS BANK" ;
      COMMIT;
      

      The result will be produced as follows :


      AccountNumberAccountantNameAccountBalanceAccountBranchAccountBank
      1Nynika561052HyderabadSOS Bank


      MySQL TRIGGERS

      MySQL - Triggers

      posted on 2019-11-29 21:44:07 - mysql Tutorials


      Grant_ Revoke Privilege

      MySQL - Grant_ Revoke Privilege

      posted on 2019-11-26 23:15:04 - mysql Tutorials


      MySQL Vs SQL

      MySQL Vs SQL

      posted on 2019-11-25 05:02:26 - mysql Tutorials


      Prompt Examples

      ChatGPT Prompt Examples

      posted on 2023-06-21 22:37:19 - ChatGPT Tutorials


      Use Cases

      Chat GPT Key Use Cases

      posted on 2023-06-21 21:03:17 - ChatGPT Tutorials


      Prompt Frameworks

      Prompt Frameworks

      posted on 2023-06-21 19:33:06 - ChatGPT Tutorials