Question 233566 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – When we should transaction in database?

I have started the working on transaction concept of database but, I don’t know when to use transaction and when we should not?

Suppose, If I am running simple SELECT * FROM table_name query. So, Do I have to use transaction concept or not.

Or Do I proceed transaction concept with Insertion and Updation query.

Please let me know!

Tags:

2

Answers


  1. 0

    For one isolated query you don’t need to start transaction. When an database supports transaction, then it start transaction implicitly. The benefit of transaction is when

    a) you execute more queries, and you need some consistency of data (like repeatable read for example),

    b) when you need to ensure data consistency for write operations (or as protection against race condition)

    c) the commit can be expensive operation due forcing IO synchronization. The transaction can wrap more write operations, and then can reduce number of commits, what can significantly increase speed. It has not impact for unsafe systems that doesn’t use fsync.

    Login or Signup to reply.
  2. 0

    When to use transactions and when not to depends on the query you want to execute. For example, you wouldn’t want a situation where someone sends money to another person, and suddenly the connection is lost or the computer shuts down. This would result in the sender’s balance being deducted, while the recipient hasn’t received the money yet. In cases like this, we can use transactions, as quoted from the PostgreSQL documentation.

    I can provide you with an example query as shown below, using MySQL:

    start transaction;

update accounts
set balance = balance - 100
where account_id = 1;

update accounts
set balance = balance + 100
where account_id = 2;

commit;

    This way, your transactional data should be secure. If a connection is lost or the computer crashes, there won’t be any changes to your data, thus preventing undesirable outcomes, as I have explained.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search