ระบุและแก้ไขการปิดกั้นและการชะงักงัน

เสร็จสมบูรณ์เมื่อ

การบล็อกเป็นเรื่องปกติในฐานข้อมูลที่ใช้การล็อก ธุรกรรมหนึ่งถือการล็อก ธุรกรรมอื่นรออยู่ คาดว่าจะมีการบล็อกสั้น ๆ ที่กินเวลาสองสามมิลลิวินาที มันจะกลายเป็นปัญหาเมื่อมันใช้เวลานานพอที่จะส่งผลกระทบต่อผู้ใช้ การชะงักงันเป็นรูปแบบที่รุนแรงกว่า: ธุรกรรมสองรายการบล็อกกันอย่างถาวร และเอ็นจิ้นฐานข้อมูลต้องยุติธุรกรรมหนึ่งรายการเพื่อทําลายวงจร

การบล็อก

การบล็อกเกิดขึ้นเมื่อเซสชันหนึ่งล็อกทรัพยากร และอีกเซสชันหนึ่งร้องขอการล็อกที่ขัดแย้งกันบนทรัพยากรเดียวกัน เซสชันการร้องขอจะรอจนกว่าเซสชันแรกจะปลดล็อค

ทรัพยากรอาจเป็นแถว หน้า หรือแม้แต่ทั้งตาราง การล็อกสามารถแชร์ (สําหรับการอ่าน) หรือแบบพิเศษ (สําหรับการเขียน) เมื่อเซสชันร้องขอการล็อกที่ขัดแย้งกับการล็อกที่มีอยู่ เซสชันนั้นจะถูกบล็อกจนกว่าเซสชันแรกจะคอมมิตหรือย้อนกลับธุรกรรมและปล่อยการล็อก

ระบุห่วงโซ่การปิดกั้น

ในฐานข้อมูล Azure SQL การแยกสแนปช็อตแบบอ่านที่ผูกมัด (RCSI) จะเปิดใช้งานตามค่าเริ่มต้น ดังนั้นการดําเนินการอ่านจึงใช้การกําหนดรุ่นแถวแทนการล็อกที่ใช้ร่วมกัน การตั้งค่านี้ช่วยลดการบล็อกระหว่างผู้อ่านและผู้เขียนได้อย่างมาก อย่างไรก็ตาม การบล็อกระหว่างผู้เขียนสองคน หรือการบล็อกที่เกิดจากธุรกรรมที่ชัดเจนที่มีระดับการแยกสูงกว่ายังคงเกิดขึ้น

เซสชันที่ด้านบนของห่วงโซ่การบล็อกเรียกว่าตัวบล็อกส่วนหัว เซสชันที่ถูกบล็อกอื่น ๆ ทั้งหมดกําลังรอไม่ว่าทางตรงหรือทางอ้อมเพื่อให้ตัวบล็อกส่วนหัวปลดล็อค หากต้องการค้นหาตัวบล็อกส่วนหัว ให้สอบถาม sys.dm_exec_requests และค้นหาเซสชันที่ไม่ใช่ blocking_session_id ศูนย์:

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS query_text,
    r.status,
    r.command
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

หากต้องการค้นหาตัวบล็อกส่วนหัวในผลการค้นหาเหล่านั้น ให้มองหารหัสเซสชันที่บล็อกผู้อื่นแต่ไม่ได้ถูกบล็อกเอง ตัวอย่างเช่น สมมติว่าแบบสอบถามส่งกลับแถวเหล่านี้:

session_id	blocking_session_id
55	52
60	52
52	0

เซสชัน 55 และ 60 ถูกบล็อกโดยเซสชัน 52 และเซสชัน 52 มี a blocking_session_id ของ 0ซึ่งหมายความว่าไม่มีอะไรปิดกั้น เซสชั่น 52 เป็นตัวบล็อกศีรษะ เมื่อคุณระบุแล้ว ให้สืบค้น sys.dm_exec_sessions และ sys.dm_exec_requests กรองไปยังรหัสเซสชันนั้นเพื่อดูว่ามีอะไรทํางานอยู่และเหตุใดจึงล็อกไว้

โปรดทราบว่า RCSI ขจัดการปิดกั้นระหว่างผู้อ่านและผู้เขียน แต่ไม่ใช่ระหว่างนักเขียนสองคน พิจารณาสถานการณ์ที่เซสชัน 52 เรียกใช้การอัปเดตชุดงานภายในธุรกรรมที่ชัดเจน:

-- Session 52
BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Processing' WHERE Region = 'West';
-- Transaction stays open while application does other work

การอัปเดตนี้ได้รับการล็อคพิเศษในทุกแถวที่ตรงกัน ตอนนี้เซสชัน 55 พยายามอัปเดตหนึ่งในแถวเดียวกัน:

-- Session 55
UPDATE Orders SET Priority = 1 WHERE OrderId = 4820;

เซสชัน 55 รอเพราะเซสชัน 52 มีการล็อกพิเศษในแถวนั้นอยู่แล้วและยังไม่ได้ผูกมัด SELECTการสืบค้นกับแถวเหล่านั้นจะยังคงประสบความสําเร็จภายใต้ RCSI เนื่องจากจะอ่านเวอร์ชันของแถวแทนการร้องขอการล็อกที่ใช้ร่วมกัน เมื่อ RCSI ลบการบล็อกตัวอ่าน-เขียนตามค่าเริ่มต้น การบล็อกที่คุณพบในฐานข้อมูล Azure SQL โดยทั่วไปจะเกี่ยวข้องกับสองเซสชันที่ทั้งคู่จําเป็นต้องเขียนลงในแถวเดียวกัน

รับรู้สถานการณ์การบล็อกทั่วไป

การทําความเข้าใจ ว่าเหตุใด จึงเกิดการบล็อกจะช่วยป้องกันได้ กลไกจัดการฐานข้อมูล Azure SQL จะจัดการการล็อกโดยอัตโนมัติ แต่รูปแบบบางอย่างนําไปสู่การบล็อกที่ยาวนานขึ้น:

คิวรีที่ทํางานเป็นเวลานานซึ่งเก็บล็อกไว้เป็นระยะเวลานาน คิวรีกําลังดําเนินการและดําเนินการอย่างแข็งขัน แต่จะล็อกไว้ตลอดเวลา เซสชันอื่นๆ ที่ต้องการการล็อกที่ขัดแย้งกันบนทรัพยากรเดียวกันจะรอจนกว่าคิวรีจะเสร็จสิ้น เมื่อต้องการแก้ไขปัญหานี้ ให้มองหาวิธีปรับคิวรีให้เหมาะสม เช่น การเพิ่มดัชนีหรือการเขียนใหม่เพื่อให้แตะแถวน้อยลง

เซสชันการนอนหลับที่มีธุรกรรมที่ไม่ได้ผูกมัด เซสชันดําเนินการคําสั่งภายในธุรกรรมที่ชัดเจน จากนั้นหยุดดําเนินการ แต่ไม่เคยคอมมิตหรือย้อนกลับ การล็อคจากธุรกรรมยังคงถูกเก็บไว้อย่างไม่มีกําหนด ปัญหานี้มักเกิดขึ้นเมื่อโปรแกรมประยุกต์ประสบปัญหาการหมดเวลาหรือการยกเลิกคิวรี แต่ไม่ได้ออกROLLBACK ใช้ SET XACT_ABORT ON เพื่อให้ข้อผิดพลาดรันไทม์ย้อนกลับธุรกรรมโดยอัตโนมัติ

เซสชันที่ไม่ได้ดึงข้อมูลแถวผลลัพธ์ทั้งหมด โปรแกรมประยุกต์ส่งแบบสอบถาม แต่ไม่ดึงแถวทั้งหมดจากชุดผลลัพธ์ ล็อคจะยังคงค้างอยู่ในแถวที่ยังไม่ได้ดึงข้อมูล ตรวจสอบให้แน่ใจว่าแอปพลิเคชันของคุณดึงแถวผลลัพธ์ทั้งหมดจนเสร็จสมบูรณ์

เซสชันในสถานะย้อนกลับ แบบสอบถามที่ถูกยกเลิก (โดยมี KILL หรือโดยการชะงักงัน) กําลังย้อนกลับการเปลี่ยนแปลง การย้อนกลับอาจใช้เวลามากสําหรับการปรับเปลี่ยนขนาดใหญ่ และเซสชันยังคงล็อกไว้ในระหว่างกระบวนการนี้ รอให้การย้อนกลับเสร็จสมบูรณ์ และหลีกเลี่ยงการปรับเปลี่ยนชุดงานขนาดใหญ่ในช่วงเวลาที่มีคนไม่ว่าง

การเชื่อมต่อที่กําพร้า แอปพลิเคชันไคลเอ็นต์ล้มเหลวหรือเวิร์กสเตชันรีสตาร์ทโดยไม่ปิดการเชื่อมต่อฐานข้อมูลอย่างสมบูรณ์ เซิร์ฟเวอร์ตรวจไม่พบการตัดการเชื่อมต่อในทันที ดังนั้นการล็อกจากเซสชันนั้นจะยังคงถูกระงับไว้ ยุติเซสชันกําพร้าด้วยKILL <session_id>;

Note

สองสถานการณ์เหล่านี้จะได้รับการบรรเทาในฐานข้อมูล Azure SQL ตามค่าเริ่มต้น RCSI ลดผลกระทบของแถวผลลัพธ์ที่ไม่ได้ดึงข้อมูล เนื่องจาก SELECT คิวรีไม่ได้รับการล็อกที่ใช้ร่วมกันภายใต้การกําหนดเวอร์ชันแถว ดังนั้นแถวที่ไม่ได้ดึงข้อมูลจะไม่บล็อกผู้เขียน การกู้คืนฐานข้อมูลแบบเร่งความเร็ว (ADR) ทําให้การย้อนกลับที่ยาวนานหายาก เนื่องจากสามารถเลิกทําการเปลี่ยนแปลงได้เกือบจะในทันทีโดยไม่คํานึงถึงขนาดธุรกรรม สถานการณ์ที่เหลืออีกสามสถานการณ์ (คิวรีที่ทํางานเป็นเวลานาน เซสชันการนอนหลับที่มีธุรกรรมที่ไม่ได้ผูกมัด และการเชื่อมต่อที่ไม่ได้ผูกมัด) ยังคงมีความเกี่ยวข้องอย่างสมบูรณ์ เนื่องจากเกี่ยวข้องกับการล็อกการเขียนแบบเอกสิทธิ์เฉพาะที่ RCSI และ ADR ไม่สามารถปล่อยก่อนกําหนดได้

แก้ไขการบล็อกที่ใช้งานอยู่

เมื่อพบการบล็อกที่ใช้งานอยู่

1. ระบุตัวบล็อกส่วนหัวโดยใช้แบบสอบถาม DMV ที่แสดงไว้ก่อนหน้านี้
2. กําหนดว่าธุรกรรมของเซสชันการบล็อกสามารถเสร็จสิ้นได้ด้วยตัวเองหรือกําลังรอการป้อนข้อมูลภายนอก
3. หากเซสชันการบล็อกเป็นการเชื่อมต่อที่ไม่ได้ใช้งานหรือถูกละทิ้ง ให้ยุติด้วย KILL <session_id>;
4. ตรวจทานแผนการดําเนินการของคิวรีการบล็อกสําหรับโอกาสในการเพิ่มประสิทธิภาพ เช่น ดัชนีที่ขาดหายไป

เพื่อป้องกันไม่ให้การบล็อกเกิดขึ้นซ้ํา ให้ทําธุรกรรมให้สั้น ดําเนินการเฉพาะใบแจ้งยอดขั้นต่ําที่จําเป็นภายในธุรกรรมและตกลงทันที ใช้ใน SET XACT_ABORT ON โค้ดแอปพลิเคชันของคุณเพื่อให้ข้อผิดพลาดรันไทม์ย้อนกลับธุรกรรมทั้งหมดโดยอัตโนมัติ ซึ่งจะป้องกันไม่ให้ธุรกรรมที่เสร็จสมบูรณ์ครึ่งหนึ่งระงับการล็อกอย่างไม่มีกําหนด ย้ายตรรกะที่ผู้ใช้ต้องเผชิญทั้งหมดนอกขอบเขตธุรกรรม

ทางตัน

การ ชะงักงั นเกิดขึ้นเมื่อธุรกรรมตั้งแต่สองรายการขึ้นไปสร้างการขึ้นต่อกันแบบวงกลม ธุรกรรมแต่ละรายการจะล็อคที่อีกฝ่ายต้องการ และไม่สามารถดําเนินการต่อได้ นี่คือตัวอย่างที่เป็นรูปธรรม:

1. ธุรกรรม A อัปเดตแถวที่ 1 และรับการล็อกพิเศษ
2. ธุรกรรม B อัปเดตแถวที่ 2 และรับการล็อกพิเศษ
3. ธุรกรรม A พยายามปรับปรุงแถวที่ 2 และถูกบล็อกโดยธุรกรรม B
4. ธุรกรรม B พยายามปรับปรุงแถว 1 และถูกบล็อกโดยธุรกรรม A

ธุรกรรมทั้งสองไม่สามารถเสร็จสิ้นได้ การตรวจสอบการชะงักงันของกลไกจัดการฐานข้อมูลจะตรวจสอบรอบเหล่านี้เป็นระยะ โดยมีช่วงเวลาเริ่มต้นห้าวินาทีที่ลดลงเหลือเพียง 100 มิลลิวินาทีเมื่อเกิดการชะงักงันบ่อยครั้ง เมื่อตรวจพบวัฏจักร จะเลือกธุรกรรมที่มีราคาแพงที่สุดเพื่อย้อนกลับในฐานะ เหยื่อ ย้อนกลับ และส่งคืนข้อผิดพลาด 1205 ไปยังแอปพลิเคชัน การย้อนกลับนี้ช่วยให้ธุรกรรมอื่นเสร็จสมบูรณ์

บันทึกข้อมูลการชะงักงัน

ใน SQL Server และ Azure SQL Managed Instance system_health เซสชันเหตุการณ์เพิ่มเติมจะบันทึกเหตุการณ์ชะงักงันตามค่าเริ่มต้น คุณสามารถสืบค้นรายงานการชะงักงันจากบัฟเฟอร์วงแหวนโดยใช้ sys.dm_xe_session_targets และsys.dm_xe_sessions

ในฐานข้อมูล Azure SQL วิธีการจะแตกต่างกัน คุณสร้างเซสชันเหตุการณ์เพิ่มเติมแบบกําหนดเองที่บันทึกsqlserver.database_xml_deadlock_reportเหตุการณ์ และสอบถามโดยใช้ DMV sys.dm_xe_database_sessions ที่มีขอบเขตฐานข้อมูลและsys.dm_xe_database_session_targets ตัวอย่างต่อไปนี้สร้างเซสชันการจับภาพทางตันและสอบถามบัฟเฟอร์วงแหวน:

-- Create and start the session
CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE = ON, MAX_MEMORY = 4 MB);
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE STATE = START;
GO

-- Query deadlock events from the ring buffer
DECLARE @tracename sysname = N'deadlocks';

SELECT
    d.value('(/event/@timestamp)[1]', 'datetime2') AS deadlock_time,
    d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml
FROM (
    SELECT CAST(target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
    INNER JOIN sys.dm_xe_database_session_targets AS t
        ON CAST(t.event_session_address AS BINARY(8)) = CAST(s.address AS BINARY(8))
    WHERE s.name = @tracename
        AND t.target_name = N'ring_buffer'
) AS ring_buffer
CROSS APPLY rb.nodes(
    '/RingBufferTarget/event[@name=''database_xml_deadlock_report'']'
) AS xevent(d)
ORDER BY deadlock_time DESC;

กราฟชะงักงันประกอบด้วยสามส่วน รายชื่อเหยื่อจะระบุว่าธุรกรรมใดถูกยกเลิก รายการกระบวนการแสดงแต่ละกระบวนการที่เกี่ยวข้อง รวมถึงข้อความแบบสอบถาม ระดับการแยก และโหมดล็อก รายการทรัพยากรแสดงทรัพยากรที่ถูกล็อกและกระบวนการที่เป็นเจ้าของและรอในแต่ละทรัพยากร

ในฐานข้อมูล Azure SQL คุณยังสามารถกําหนดค่าการแจ้งเตือนการชะงักงันผ่านพอร์ทัล Azure เพื่อรับการแจ้งเตือนเมื่อเกิดการชะงักงัน

ป้องกันการชะงักงัน

คุณไม่สามารถขจัดทางตันทั้งหมดได้ แต่คุณสามารถลดความถี่ที่เกิดขึ้นได้อย่างมาก

• วัตถุ Access ในลําดับที่สอดคล้องกัน: ถ้าธุรกรรมทั้งหมดปรับเปลี่ยนตาราง A ก่อนตาราง B การขึ้นต่อกันแบบวงกลมจะไม่สามารถสร้างได้ กําหนดรูปแบบการเข้าถึงให้เป็นมาตรฐานผ่านกระบวนงานที่เก็บไว้
• ทําให้ธุรกรรมสั้น: ธุรกรรมที่สั้นลงจะล็อคไว้เป็นเวลาน้อยลง ซึ่งจะช่วยลดกรอบเวลาสําหรับการขึ้นต่อกันแบบวงกลม
• ใช้ระดับการแยกการกําหนดเวอร์ชันแถว: RCSI กําจัดการล็อกที่ใช้ร่วมกันสําหรับการดําเนินการอ่าน ซึ่งจะลบแหล่งที่มาทั่วไปของวงจรการชะงักงันหนึ่งแหล่ง การล็อกที่ปรับให้เหมาะสมในฐานข้อมูล Azure SQL ช่วยลดโอกาสในการชะงักงันได้อีก
• เพิ่มดัชนีที่เหมาะสม: เมื่อการสืบค้นสแกนหลายแถว จะได้รับการล็อกในข้อมูลที่หลากหลาย การเพิ่มดัชนีที่จํากัดการสแกนให้แคบลงให้เหลือแถวน้อยลงจะช่วยลดความขัดแย้งในการล็อก
• ใช้การบังคับแผนกับ Query Store: ถ้าการเปลี่ยนแปลงแผนทําให้คิวรีสแกนแถวมากขึ้นและได้รับการล็อกมากขึ้นการบังคับแผนก่อนหน้านี้สามารถลดการชะงักงันในขณะที่คุณตรวจสอบได้

จัดการกับการชะงักงันในรหัสแอปพลิเคชัน

แอปพลิเคชันควรมีตรรกะการลองใหม่สําหรับข้อผิดพลาดการชะงักงันเสมอ เมื่อธุรกรรมถูกเลือกเป็นเหยื่อการชะงักงัน กลไกจัดการฐานข้อมูลจะย้อนกลับและส่งกลับข้อผิดพลาด 1205 แอปพลิเคชันของคุณควรตรวจพบข้อผิดพลาดนี้ หยุดชั่วคราว และส่งธุรกรรมอีกครั้ง

BEGIN TRY
    BEGIN TRANSACTION;
    -- Your data modification statements
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1205
    BEGIN
        ROLLBACK TRANSACTION;
        WAITFOR DELAY '00:00:01';  -- Brief pause before retry
        -- Retry logic here
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        THROW;
    END
END CATCH;

เคล็ดลับ

สุ่มการหน่วงเวลาการลองใหม่ระหว่างความพยายามเพื่อป้องกันไม่ให้ธุรกรรมสองรายการเดียวกันหยุดชะงักกันอีกครั้งทันที รูปแบบทั่วไปคือการรอระหว่างหนึ่งถึงสามวินาทีด้วยส่วนประกอบแบบสุ่ม

ประเด็นสําคัญ

การบล็อกเป็นเรื่องปกติ แต่การบล็อกแบบขยายจะส่งผลกระทบต่อผู้ใช้ ดังนั้นคุณจึงใช้ sys.dm_exec_requests เพื่อค้นหาตัวบล็อกส่วนหัวและทําความเข้าใจว่ากําลังทําอะไรอยู่ สถานการณ์ทั่วไป ได้แก่ การสืบค้นที่ทํางานเป็นเวลานาน เซสชันสลีปที่มีธุรกรรมที่ไม่ได้ผูกมัด และการเชื่อมต่อที่ไม่ได้ใช้งาน ซึ่งทั้งหมดนี้คุณจัดการได้โดยทําให้ธุรกรรมสั้น โดยใช้ SET XACT_ABORT ONและทําให้แน่ใจว่าแอปพลิเคชันจัดการการเชื่อมต่อและชุดผลลัพธ์อย่างเหมาะสม การชะงักงันเกิดขึ้นเมื่อธุรกรรมสร้างการขึ้นต่อกันของล็อคแบบวงกลม และกลไกจัดการฐานข้อมูลจะแก้ไขโดยอัตโนมัติโดยการยุติธุรกรรมที่มีราคาแพงที่สุดและส่งคืนข้อผิดพลาด 1205 คุณลดความถี่การชะงักงันโดยการเข้าถึงอ็อบเจ็กต์ในลําดับที่สอดคล้องกัน ทําให้ธุรกรรมสั้น โดยใช้ระดับการแยกเวอร์ชันแถว และเพิ่มดัชนีที่เหมาะสม รหัสแอปพลิเคชันของคุณควรมีตรรกะการลองใหม่สําหรับข้อผิดพลาด 1205 เสมอ เพื่อให้สามารถกู้คืนได้โดยอัตโนมัติเมื่อถูกเลือกให้เป็นเหยื่อการชะงักงัน