Analysis and summary of the impact of MySQL transactions on efficiency

1. Database transactions will reduce database performance. In order to ensure data consistency and isolation, transactions need to lock transactions.

2. If other transactions need to operate this part of data, they must wait for the last transaction to end (commit, rollback).

Examples

create table acct(
    acct_no varchar(32),
    acct_name varchar(32),
    balance decimal(16,2)
);
 
insert into acct values
    ('0001','Jerry', 1000),
    ('0002','Tom', 2000);
 
start transaction; -- Start transaction update acct set balance = balance - 100 where acct_no = '0001'; -- Simulate the deductor update acct set balance = balance + 100 where acct_no = '0002'; -- Simulate the payee commit; -- Transaction commit rollback; -- Transaction rollback

Knowledge point expansion:

Transactions

• Atomicity: All operations of the entire transaction are either successfully committed or rolled back if they fail.
  
• Consistency: refers to the transaction converting the database from one consistent state to another consistent state, and the integrity of the database is not destroyed before and after the transaction starts.
  
• Isolation: requires that a transaction modify data in the database and that the modification is not visible to other transactions until it is completed.
  
• Persistence: Once a transaction is committed, the changes it makes are permanently saved to the database. At this point, even if the system crashes, the modified data that has been submitted will not be lost.
  

Big Business

Transactions that take a long time to run and operate on a large amount of data

• Locking too much data, causing a lot of blocking and lock timeouts
• Rollback takes a long time
• Long execution time, which may cause master-slave delay

How to handle big transactions:

• Avoid processing too much data at once
  
• Remove unnecessary select operations from transactions

This concludes this article on the impact of MySQL transactions on efficiency. For more information on the impact of MySQL transactions on efficiency, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!