Practical MySQL & MariaDB

Ufafanuzi wa Kufunga MySQL: Angalia Hali ya Kufunga, Achia Kufunga Salama, na Zuia Vifunga vya Kifo (5.6–8.0)

Usanidi na Usimamizi wa Server
目次

1. Utangulizi

MySQL inatumika sana kama mfumo wa usimamizi wa hifadhidata, lakini wakati maswali mengi yanajaribu kufikia data sawa, mekanizma ya kufunga inashitishwa. Kufunga ni muhimu kwa kudumisha usawa wa data; hata hivyo, usimamizi usio sahihi unaweza kusababisha deadlocks na kupungua kwa utendaji.

Katika makala hii, tutaelezea dhana za msingi za kufunga katika MySQL na kutoa mwongozo wa kina juu ya jinsi ya kuangalia hali ya kufunga, jinsi ya kuachilia kufunga, na jinsi ya kuzuia deadlocks.

Unachojifunza katika Makala Hii

  • Aina za kufunga kwa MySQL na athari zake
  • Jinsi ya kuangalia kufunga kulingana na toleo la MySQL
  • Taratibu salama za kuachilia kufunga
  • Mshauriano wa vitendo kuzuia deadlocks

Hebu tuanze kwa kuelezea dhana za msingi za kufunga kwa MySQL.

2. Dhana za Msingi za Kufunga kwa MySQL

Katika hifadhidata, “kufunga” ni mekanizma inayopunguza upatikanaji ili kudumisha usahihi wa data wakati shughuli nyingi zinajaribu kubadilisha data kwa wakati mmoja. Ikiwa kufunga hakisimamiwi ipasavyo, kupungua kwa utendaji na deadlocks inaweza kutokea.

2.1 Aina Kuu za Kufunga

Katika MySQL, kuna aina kadhaa za kufunga kulingana na kiwango cha ulinzi wa data kinachohitajika.

Kufunga kwa Safu

  • Hufunga safu maalum pekee, ikipunguza athari kwa shughuli zingine.
  • Inasaidiwa tu na injini ya hifadhi ya InnoDB.
  • Inashitishwa wakati wa kutumia SELECT ... FOR UPDATE au SELECT ... LOCK IN SHARE MODE .

Kufunga kwa Jedwali

  • Hufunga jedwali zima, kuzuia maswali mengi kutekelezwa kwa wakati mmoja.
  • Mara nyingi hutumika na injini ya hifadhi ya MyISAM.
  • Inashitishwa wakati wa kutumia amri ya LOCK TABLES.

Kufunga kwa Nia

  • Kufunga kunatumika kuratibu kufunga kwa safu na kufunga kwa jedwali ili kuepuka migogoro.
  • Inatumika tu katika InnoDB na inasimamiwa kiotomatiki.

Deadlock

  • Hali ambapo shughuli nyingi zinangojea kufunga kwa kila mmoja.
  • Ikiwa shughuli hazijapangwa ipasavyo, usindikaji unaweza kusimama kabisa.

2.2 Mifano ya Kutokea kwa Kufunga

Hebu tazame maswali maalum ya SQL ili kuelewa jinsi kufunga kunavyotokea.

Mfano wa Kufunga kwa Safu

Kutekeleza SQL ifuatayo kutafunga safu maalum.

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until this transaction is COMMIT or ROLLBACK

Kama kikao kingine kinajaribu kusasisha safu ile ile, kitalazimika kuingia katika hali ya kusubiri kufunga (mkakasi wa kufunga).

Mfano wa Kufunga kwa Jedwali

Ili kufunga jedwali zima, tumia amri ifuatayo.

LOCK TABLES products WRITE;
-- Other sessions cannot modify the products table until all read/write operations are complete

Mpaka kufunga hiki kitatolewa, watumiaji wengine hawawezi kubadilisha data katika jedwali la products.

Mfano wa Deadlock

Ifuatayo inaonyesha hali ya kawaida ya deadlock.

-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...

-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...

-- Session 1 (executes another statement)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

Katika hali hii, kila shughuli inangojea nyingine itoweze kufunga kwake, na kusababisha deadlock.

3. Jinsi ya Kuangalia Hali ya Kufunga kwa MySQL (Kwa Toleo)

Ili kubaini kama kufunga kunatokea, unahitaji kutekeleza amri zinazofaa kwa toleo lako la MySQL.

3.1 Jinsi ya Kuangalia Kufunga katika MySQL 5.6 na Mapema

Katika MySQL 5.6 na mapema, unaweza kuangalia taarifa za kufunga kwa kutumia SHOW ENGINE INNODB STATUS\G;.

SHOW ENGINE INNODB STATUS\G;

Kutekeleza amri hii inaonyesha taarifa za kina kuhusu kufunga vilivyo hai kwa sasa.

3.2 Jinsi ya Kuangalia Kufunga katika MySQL 5.7

Katika MySQL 5.7 na baadaye, njia rahisi zaidi ni kutumia jedwali la sys.innodb_lock_waits.

SELECT * FROM sys.innodb_lock_waits;

By querying this table, you can identify which transactions are waiting for locks.

3.3 Jinsi ya Kuangalia Kufuli katika MySQL 8.0 na Baadaye

Katika MySQL 8.0 na baadaye, unaweza kupata taarifa za kina zaidi za kufuli kwa kutumia performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

Ili kutambua kikao kinachoshikilia kufuli, tumia SQL ifuatayo:

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;

Hii inakuruhusu kubainisha mchakato unaohusika na kufuli.

4. Jinsi ya Kuachilia Kufuli za MySQL (Hatari Zimeelezwa)

Ikiwa kufuli kutatokea katika MySQL na hakishughulikiwa vizuri, uchakataji unaweza kusimama na utendaji wa hifadhidata unaweza kudhoofika.
Katika sehemu hii, tutaeleza jinsi ya kuachilia kufuli na hatari zinazohusiana.

4.1 Kutambua Kikao Kinachoshikilia Kufuli

Kabla ya kuachilia kufuli, lazima utambue ni kikao gani kinachoshikilia. Tumia SQL ifuatayo kuangalia vikao vinavyopata kusubiri kufuli:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

Hii hoi inaorodhesha vikao vinavyosubiri kufuli.

Katika MySQL 8.0 na baadaye, unaweza kupata taarifa za kina za kufuli kwa kutumia:

SELECT * FROM performance_schema.data_locks;

4.2 Kuachilia Kufuli Kutumia Amri ya KILL

Pindi utakapotambua kikao kinachoshikilia kufuli, unaweza kuachilia kwa kumaliza mchakato kwa nguvu.

1. Angalia mchakato unaoshikilia kufuli

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. Maliza kikao kutumia amri ya KILL

KILL <process_id>;

Kwa mfano, kumaliza mchakato na ID=12345, tekeleza:

KILL 12345;

⚠️ Hatari za Amri ya KILL

  • Shughuli zilizomalizwa kwa nguvu hurudishwa nyuma
  • Kwa mfano, mabadiliko yaliyofanywa na taarifa ya UPDATE iliyokatizwa yanaweza kutupwa.
  • Inaweza kusababisha makosa ya programu
  • Ikiwa unahitaji kutumia KILL mara kwa mara, unapaswa kukagua muundo wa programu yako.

4.3 Kuachilia Kufuli na ROLLBACK (Njia Salama Zaidi)

Kabla ya kutumia amri ya KILL, ikiwezekana, jaribu kumaliza kwa mkono shughuli inayosababisha kufuli.

1. Kwanza, angalia shughuli za sasa

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Ikiwa utapata shughuli yenye tatizo, tekeleza ROLLBACK

ROLLBACK;

Njia hii inakuruhusu kuachilia kufuli wakati wa kudumisha uthabiti wa data.

4.4 Kuweka Otomatiki Ushughulikiaji wa Kufuli na SET innodb_lock_wait_timeout

Badala ya kuachilia kufuli kwa mkono, unaweza kusanidi muda wa kusubiri kufuli ili shughuli itakamilike kiotomatiki ikiwa kufuli hakitachiliwa ndani ya muda uliobainishwa.

SET innodb_lock_wait_timeout = 10;

Kwa mpangilio huu, ikiwa kufuli hakitachiliwa ndani ya sekunde 10, MySQL inarudisha kosa na kumaliza shughuli kiotomatiki.

5. Vidokezo Muhimu na Mazoea Bora kwa Kufuli za MySQL

Ushughulikiaji sahihi wa kufuli husaidia kupunguza hatari ya deadlocks na kudhoofika kwa utendaji. Hapo chini ni mazoea bora ya kupunguza kufuli na kushughulikia kwa ufanisi.

5.1 Jinsi ya Kuzuia Deadlocks

Ili kuzuia deadlocks, weka vidokezo vifuatavyo akilini:

1. Sanidi Mpangilio wa Utekelezaji wa Shughuli

  • Kwa mfano, wakati wa kusasisha majedwali mengi, sawa daima yasasishe katika mpangilio sawa .
  • Mfano:
    -- OK: Always update in the order orders → customers
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

× NG: Mpangilio tofauti wa utekelezaji unaweza kusababisha deadlocks

-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- Kipindi 2 (deadlock inaweza kutokea ikiwa itatekelezwa kwa mpangilio wa kinyume)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Keep Transactions Short

  • Commit or roll back as quickly as possible
  • Avoid long-running transactions, as they can negatively impact other processes.

3. Set Appropriate Indexes

  • Creating proper indexes helps avoid unnecessary locks .
  • Example: Adding an index on customer_id in the orders table ensures that only specific rows are locked .
    CREATE INDEX idx_customer_id ON orders (customer_id);

6. Summary

  • MySQL locks include row locks, table locks, and intention locks . Improper management can lead to deadlocks and performance issues.
  • The method for checking lock status varies depending on the MySQL version , so choose the appropriate approach for your environment.
  • Be cautious when releasing locks!
  • Try ROLLBACK before using the KILL command.
  • Use SET innodb_lock_wait_timeout to automatically handle lock timeouts.
  • To prevent deadlocks, standardize transaction execution order and keep transactions short .

7. FAQ (Frequently Asked Questions)

Q1. What is the easiest command to check MySQL lock status?

  • A1. In MySQL 8.0 and later, use SELECT * FROM performance_schema.data_locks; to easily check lock status.

Q2. What should I do if a deadlock occurs?

  • A2. First, run SHOW ENGINE INNODB STATUS\G; to identify the cause of the deadlock. Then review and standardize the transaction execution order to prevent recurrence.

Q3. Can using the KILL command corrupt data?

  • A3. When forcibly terminating a session, unfinished transactions are rolled back, which may affect data consistency. Use it with caution.

Q4. How can I prevent deadlocks?

  • A4. The following methods are effective:
  • Standardize transaction execution order
  • Keep transactions short
  • Set appropriate indexes

Q5. How can I reduce locks and improve MySQL performance?

  • A5.
  • Design proper indexes to reduce unnecessary locks
  • Keep transactions short to minimize lock duration
  • Avoid full table locks (LOCK TABLES)
  • Use read replicas to distribute read workloads