Practical MySQL & MariaDB

MySQL ලොක් කිරීම විස්තර කරයි: ලොක් තත්ත්වය පරීක්ෂා කිරීම, ලොක් ආරක්ෂිතව මුදා හරීම, සහ ඩෙඩ්ලොක් වලින් වැළැක්වීම (5.6–8.0)

Server සැකසුම් සහ පරිපාලනය
目次

1. හැඳින්වීම

MySQL යනු දත්ත ගබඩා කළමනාකරණ පද්ධතියක් ලෙස පුළුල් ලෙස භාවිතා වේ, නමුත් බහු විමසුම් එකම දත්ත වෙත ප්‍රවේශ වීමට උත්සාහ කරන විට, අගුළු යන්ත්‍රණයක් ක්‍රියාත්මක වේ. අගුළු දත්ත සමගත්‍යතාව රැක ගැනීමට අත්‍යවශ්‍ය වේ; එහෙත්, අනිසි කළමනාකරණයක් මඟින් ඩෙඩ්ලොක්ස් සහ කාර්ය සාධන අඩු වීම සිදුවිය හැක.

මෙම ලිපියේ, MySQL හි අගුළු කිරීමේ මූලික සංකල්ප පැහැදිලි කරමින් අගුළු තත්ත්වය පරීක්ෂා කිරීම, අගුළු මුදාහරිනු සහ ඩෙඩ්ලොක්ස් වැළැක්වීම පිළිබඳ විස්තරාත්මක මාර්ගෝපදේශ ලබා දෙනු ඇත.

ඔබ මෙම ලිපියෙන් ඉගෙන ගන්නා දේ

  • MySQL අගුළු වර්ග සහ ඒවායේ බලපෑම
  • MySQL අනුවාදය අනුව අගුළු පරීක්ෂා කිරීම
  • අගුළු මුදාහරින ආරක්ෂිත ක්‍රියාපටිපාටි
  • ඩෙඩ්ලොක්ස් වැළැක්වීමට ප්‍රායෝගික උපදෙස්

අපි MySQL අගුළු කිරීමේ මූලික සංකල්ප පැහැදිලි කිරීමෙන් ආරම්භ කරමු.

2. MySQL අගුළු කිරීමේ මූලික සංකල්ප

දත්ත ගබඩාවක, “අගුළු” යනු බහු ගනුදෙනු එකවර දත්ත වෙනස් කිරීමට උත්සාහ කරන විට දත්ත අඛණ්ඩතාව රැක ගැනීමට ප්‍රවේශය සීමා කරන යන්ත්‍රණයකි. අගුළු නිසි ලෙස කළමනාකරණය නොකළහොත්, කාර්ය සාධන අඩු වීම සහ ඩෙඩ්ලොක්ස් සිදුවිය හැක.

2.1 අගුළු ප්‍රධාන වර්ග

MySQL හි, අවශ්‍ය දත්ත ආරක්ෂණ මට්ටම අනුව විවිධ අගුළු වර්ග ඇත.

පේළි අගුළු

  • නියමිත පේළි පමණක් අගුළු කරයි, අනෙකුත් ගනුදෙනු වලට බලපෑම අඩු කරයි.
  • InnoDB ගබඩා එන්ජින් මගින් පමණක් සහය දක්වයි.
  • SELECT ... FOR UPDATE හෝ SELECT ... LOCK IN SHARE MODE භාවිතා කරන විට ක්‍රියාත්මක වේ.

වගුව අගුළු

  • සම්පූර්ණ වගුව අගුළු කරයි, බහු විමසුම් එකවර ක්‍රියාත්මක වීම වැලැක්වයි.
  • MyISAM ගබඩා එන්ජින් සමඟ සාමාන්‍යයෙන් භාවිතා වේ.
  • LOCK TABLES ප්‍රකාශය භාවිතා කරන විට ක්‍රියාත්මක වේ.

අදහස් අගුළු

  • පේළි අගුළු සහ වගුව අගුළු සමඟ ගැටුම් වැළැක්වීමට සමන්විත කරන අගුළු.
  • InnoDB තුළ පමණක් භාවිතා කරයි, ස්වයංක්‍රීයව කළමනාකරණය වේ.

ඩෙඩ්ලොක්

  • බහු ගනුදෙනු එකිනෙකාගේ අගුළු සඳහා රැඳී සිටින තත්ත්වයක්.
  • ගනුදෙනු නිසි ලෙස සැලසුම් නොකළහොත්, සැකසීම සම්පූර්ණයෙන් නවතී යා හැක.

2.2 අගුළු සිදුවීමේ උදාහරණ

අගුළු කෙසේ සිදුවේදැයි තේරුම් ගැනීමට විශේෂ SQL විමසුම් බලමු.

පේළි අගුළු උදාහරණය

පහත SQL ක්‍රියාත්මක කිරීමෙන් නියමිත පේළියක් අගුළු වේ.

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

වෙනත් සැසියක් ඒම පේළිය යාවත්කාලීන කිරීමට උත්සාහ කළහොත්, එය අගුළු රැඳී සිටීමේ තත්ත්වයට (අගුළු තරඟය) පත්වේ.

වගුව අගුළු උදාහරණය

සම්පූර්ණ වගුවක් අගුළු කිරීමට, පහත විධානය භාවිතා කරන්න.

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

මෙම අගුළු මුදා හරින තුරු, අනෙකුත් පරිශීලකයන් products වගුවේ දත්ත වෙනස් කළ නොහැක.

ඩෙඩ්ලොක් උදාහරණය

පහත දැක්වෙන්නේ සාමාන්‍ය ඩෙඩ්ලොක් අවස්ථාවකි.

-- 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

මෙම තත්ත්වයේ, සෑම ගනුදෙනුවක්ම අනෙක් ගනුදෙනුවේ අගුළු මුදා හරින තෙක් රැඳී සිටී, එමඟින් ඩෙඩ්ලොක් සිදුවේ.

3. MySQL අගුළු තත්ත්වය පරීක්ෂා කිරීම (අනුවාද අනුව)

අගුළු සිදුවීම් පරීක්ෂා කිරීමට, ඔබේ MySQL අනුවාදයට අදාළ විධාන ක්‍රියාත්මක කළ යුතුය.

3.1 MySQL 5.6 සහ එයට පෙර අගුළු පරීක්ෂා කිරීම

MySQL 5.6 සහ එයට පෙර, SHOW ENGINE INNODB STATUS\G; භාවිතා කර අගුළු තොරතුරු පරීක්ෂා කළ හැක.

SHOW ENGINE INNODB STATUS\G;

මෙම විධානය ක්‍රියාත්මක කිරීමෙන් වර්තමාන සක්‍රිය අගුළු පිළිබඳ විස්තරාත්මක තොරතුරු පෙන්වයි.

3.2 MySQL 5.7 හි අගුළු පරීක්ෂා කිරීම

MySQL 5.7 සහ පසුගිය අනුවාද වල, සරලම ක්‍රමය sys.innodb_lock_waits වගුව භාවිතා කිරීමයි.

SELECT * FROM sys.innodb_lock_waits;

මෙම වගුව විමර්ශනය කිරීමෙන්, ලොක්‌ස් සඳහා බලා සිටින ගනුදෙනු හඳුනාගත හැකිය.

3.3 MySQL 8.0 සහ පසුකාලීනව ලොක්‌ස් පරීක්ෂා කරන්නේ කෙසේද

MySQL 8.0 සහ පසුකාලීනව, performance_schema.data_locks භාවිතයෙන් වැඩි විස්තරාත්මක ලොක්‌ස් තොරතුරු ලබාගත හැකිය.

SELECT * FROM performance_schema.data_locks;

ලොක්‌ස් තබාගත් සැසිය හඳුනාගැනීම සඳහා, පහත SQL භාවිතා කරන්න:

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

මෙය ඔබට ලොක්‌ස් සඳහා වගකිව යුතු ක්‍රියාවලිය නිශ්චිතව හඳුනාගැනීමට ඉඩ සලසයි.

4. MySQL ලොක්‌ස් නිදහස් කරන්නේ කෙසේද (අවදානම් පැහැදිලි කිරීම)

MySQL හි ලොක්‌ස් එකක් සිදුවී නිසි ලෙස කළමනාකරණය නොකළහොත්, සැකසුම් නැවතිලා යා හැකි අතර දත්ත සමුදායන කාර්ය සාධනය පහත වැටිය හැකිය.
මෙම කොටසේදී, අපි ලොක්‌ස් නිදහස් කරන්නේ කෙසේද යන්න සහ ඒවායේ අවදානම් පැහැදිලි කරන්නෙමු.

4.1 ලොක්‌ස් තබාගත් සැසිය හඳුනාගැනීම

ලොක්‌ස් එකක් නිදහස් කිරීමට පෙර, එය තබාගත් සැසිය කුමනද යන්න හඳුනාගත යුතුය. ලොක්‌ස් බලා සිටින සැසි පරීක්ෂා කිරීම සඳහා පහත SQL භාවිතා කරන්න:

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

මෙම විමර්ශනය ලොක්‌ස් බලා සිටින සැසි ලැයිස්තුගත කරයි.

MySQL 8.0 සහ පසුකාලීනව, පහත භාවිතයෙන් විස්තරාත්මක ලොක්‌ස් තොරතුරු ලබාගත හැකිය:

SELECT * FROM performance_schema.data_locks;

4.2 KILL පණිවිඩය භාවිතයෙන් ලොක්‌ස් නිදහස් කිරීම

ලොක්‌ස් තබාගත් සැසිය හඳුනාගත් පසු, ක්‍රියාවලිය බලහත්කාරයෙන් අවසන් කිරීමෙන් එය නිදහස් කළ හැකිය.

1. ලොක්‌ස් තබාගත් ක්‍රියාවලිය පරීක්ෂා කරන්න

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

2. KILL පණිවිඩය භාවිතයෙන් සැසිය අවසන් කරන්න

KILL <process_id>;

උදාහරණයක් ලෙස, ID=12345 ඇති ක්‍රියාවලිය අවසන් කිරීම සඳහා, මෙය ක්‍රියාත්මක කරන්න:

KILL 12345;

⚠️ KILL පණිවිඩයේ අවදානම්

  • බලහත්කාරයෙන් අවසන් කළ ගනුදෙනු පසුපසට ගමන් කරයි
  • උදාහරණයක් ලෙස, විසුරුවා හරින ලද UPDATE ප්‍රකාශනයෙන් සිදුකළ වෙනස්කම් ඉවත් කළ හැකිය.
  • එය යෙදුම් දෝෂ ඇති කළ හැකිය
  • ඔබට KILL නිතර භාවිතා කිරීම අවශ්‍ය නම්, ඔබේ යෙදුම් සැලසුම් පරීක්ෂා කළ යුතුය.

4.3 ROLLBACK භාවිතයෙන් ලොක්‌ස් නිදහස් කිරීම (නිරাপද සඳහා ක්‍රමය)

KILL පණිවිඩය භාවිතා කිරීමට පෙර, ඉඩක් තිබේ නම්, ලොක්‌ස් ඇති කරන ගනුදෙනුව මෛනුම්‍යෙන් අවසන් කිරීම උත්සාහ කරන්න.

1. පළමුව, වත්මන් ගනුදෙනු පරීක්ෂා කරන්න

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. ගැටලුකාරී ගනුදෙනුව සොයාගත්හොත්, ROLLBACK ක්‍රියාත්මක කරන්න

ROLLBACK;

මෙම ක්‍රමය ඔබට දත්ත ස්ථිරභාවය රැකගෙන ලොක්‌ස් නිදහස් කිරීමට ඉඩ සලසයි.

4.4 SET innodb_lock_wait_timeout භාවිතයෙන් ලොක්‌ස් කළමනාකරණය ස්වයංක්‍රීය කිරීම

ලොක්‌ස් මෛනුම්‍යෙන් නිදහස් කිරීම වෙනුවට, ලොක්‌ස් නිශ්චිත කාලයක් තුළ නිදහස් නොවුණහොත් ගනුදෙනුව ස්වයංක්‍රීයව කාල අවුළුවානු ලබන ලෙස ලොක්‌ස් බලා සිටීමේ කාල සීමාවක් සකස් කළ හැකිය.

SET innodb_lock_wait_timeout = 10;

මෙම සැකැස්මෙන්, ලොක්‌ස් මීටත් වඩා 10 තත්පරකින් නිදහස් නොවුණහොත්, MySQL දෝෂයක් ආපසු ලබාදෙන අතර ගනුදෙනුව ස්වයංක්‍රීයව අවසන් කරයි.

5. MySQL ලොක්‌ස් සඳහා වැදගත් සටහන් සහ හොඳම පිළිවෙත්

නිසි ලොක්‌ස් කළමනාකරණය ඩෙඩ්ලොක්‌ස් සහ කාර්ය සාධනය පහත වැටීමේ අවදානම අඩු කිරීමට උපකාරී වේ. පහත දැක්වෙන්නේ ලොක්‌ස් අවම කිරීම සහ ඒවා කාර්යක්ෂමව කළමනාකරණය කිරීම සඳහා හොඳම පිළිවෙත් වේ.

5.1 ඩෙඩ්ලොක්‌ස් වැළැක්වීමට කෙසේද

ඩෙඩ්ලොක්‌ස් වැළැක්වීම සඳහා, පහත කරුණු මතක තබාගන්න:

1. ගනුදෙනු ක්‍රියාකාරීත්වයේ අනුපිළිවෙල සම්මතකරණය කරන්න

  • උදාහරණයක් ලෙස, බහු වගු ස්ථිර කිරීමේදී, වැජිගත්කම් සෑම විටම එකම අනුපිළිවෙලකින් ස්ථිර කරන්න.
  • උදාහරණය:
    -- 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: විවිධ ක්‍රියාකාරීත්ව අනුපිළිවෙල් ඩෙඩ්ලොක්‌ස් ඇති කළ හැකිය

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

-- Session 2 (deadlock may occur if executed in reverse order)
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