Các Mức Isolation Level

Vũ Huy Tâm

Isolation level là một thuộc tính của transaction, qui định mức độ cô lập của dữ liệu mà transaction có thể truy nhập vào khi dữ liệu đó đang được cập bởi một transaction khác. Khi một transaction cập nhật dữ liệu đang diễn ra, một phần dữ liệu sẽ bị thay đổi (ví dụ một số bản ghi của bảng được sửa đổi hoặc bị xóa bỏ, một số được thêm mới), vậy các transaction hoặc truy vấn khác xảy ra đồng thời và cùng tác động vào các bản ghi đó sẽ diễn ra thế nào? Chúng sẽ phải đợi đến khi transaction đầu hoàn thành hay có thể thực hiện song song, kết quả dữ liệu nhận được là trong khi hay sau khi cập nhật? Bạn có thể điều khiển những hành vi này thông qua việc đặt isolation level của từng transaction. SQL Server cung cấp các mức isolation level sau xếp theo thứ tự tăng dần của mức độ cô lập của dữ liệu: Read Uncommitted, Read Commited, Repeatable Read, và Serializable. Từ bản 2005 bắt đầu bổ sung thêm một loại mới là Snapshot. Phần còn lại của bài này sẽ đi vào chi tiết của từng loại.

1. Read Uncommitted

Khi transaction thực hiện ở mức này, các truy vấn vẫn có thể truy nhập vào các bản ghi đang được cập nhật bởi một transaction khác và nhận được dữ liệu tại thời điểm đó mặc dù dữ liệu đó chưa được commit (uncommited data). Nếu vì lý do nào đó transaction ban đầu rollback lại những cập nhật, dữ liệu sẽ trở lại giá trị cũ. Khi đó transaction thứ hai nhận được dữ liệu sai. Hãy tìm hiểu qua ví dụ sau:

CREATE TABLE dbo.Item (id INT, NAME VARCHAR(50))
 
INSERT INTO dbo.Item SELECT 1,'a'
INSERT INTO dbo.Item SELECT 2,'b'
INSERT INTO dbo.Item SELECT 3,'c'
 
SELECT * FROM dbo.Item

Nay bạn hãy mở hai cửa sổ trong Management Studio, ở cửa số thứ nhất bạn nhập vào:

BEGIN TRAN
UPDATE dbo.Item
SET name = 'x'
WHERE id>2
WAITFOR DELAY '00:00:10' --wait for 10 seconds
ROLLBACK

Và ở cửa số thứ hai bạn nhập:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM dbo.Item

Giờ bạn thực hiện đoạn lệnh ở cửa sổ thứ nhất rồi nhanh chóng chuyển sang thực hiện đoạn lệnh ở cửa sổ thứ hai. Bạn sẽ thấy cửa số thứ hai trả về bản ghi số 3 với name = ‘x’. Tuy nhiên sau đó transaction ở cửa số thứ nhất bị rollback và sau khi cả hai transaction kết thúc, bản ghi số 3 lại trở lại giá trị ban đầu name=’c’. Như vậy là transaction ở cửa số thứ hai đã nhận được dữ liệu sai vì dữ liệu này chưa được commit. Hiện tượng này gọi là uncommited read, hay còn gọi là dirty read. Ưu điểm của mức isolation này là tăng độ tương tranh trong database, các tiến trình đọc không cần đợi đến khi tiến trình ghi hoàn tất mà có thể lấy dữ liệu ra được ngay. Nói nôm na là yêu cầu đọc của nó là “tôi không cần biết dữ liệu có đang được cập nhật hay không, hãy cho tôi dữ liệu hiện có ngay tại thời điểm này”. Tùy theo ứng dụng của bạn mà bạn có thể đặt mức isolation này không, nếu việc đọc sai như trên là không thể chấp nhận được bạn cần đặt mức isolation cao hơn. Còn nếu có thể dung thứ được thì đặt mức này sẽ giúp tăng hiệu năng đọc cho hệ thống.
Chú ý là mức isolation này tương được với gợi ý “NOLOCK” khi truy vấn bảng, đoạn lệnh ở cửa sổ thứ hai tương đương với:

SELECT * FROM dbo.Item WITH (NOLOCK)

2. Read Commited

Đây là mức isolation mặc định, nếu bạn không đặt gì cả thì transaction sẽ hoạt động ở mức này. Transaction sẽ không đọc được dữ liệu đang được cập nhật mà phải đợi đến khi việc cập nhật thực hiện xong. Vì thế nó tránh được dirty read như ở mức trên. Giờ hãy sửa lại đoạn lệnh ở cửa số thứ hai thành:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM dbo.Item
WHERE id>2

Và thực hiện lại hai cửa sổ theo trình tự như trên, bạn sẽ thấy cửa sổ thứ hai không trả về kết quả ngay mà phải đợi đến khi cửa số thứ nhất thực hiện xong. Và lần này cửa sổ thứ hai trả về dữ liệu đúng. Tuy nhiên nếu transaction thứ hai insert thêm bản ghi nằm trong phạm vi cập nhật của transaction thứ nhất, nó vẫn được phép làm như vậy và gây nhiễu đến transaction thứ nhất. Giờ hãy sửa lại code ở hai cửa sổ thành:
cửa số 1

BEGIN TRAN
UPDATE dbo.Item
SET name = 'x'
WHERE id>2
WAITFOR DELAY '00:00:10' --wait for 10 seconds
--ROLLBACK
COMMIT
SELECT * FROM dbo.Item
WHERE id>2

Cửa sổ hai:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
INSERT INTO dbo.Item SELECT 5,'e'

Sau khi thực hiện cả hai cửa sổ bạn sẽ thấy kết quả trả về có chứa bản ghi 5 với name = ‘e’. Điều này hoàn toàn bất ngờ vì theo trình tự thực hiện đoạn lệnh ở cửa sổ thứ nhất, tất cả các bản ghi với id>2 đều được cập nhật. Trong tình huống trên, bản ghi 5 đã xuất hiện sau khi bảng được cập nhật nhưng trước khi transaction kết thúc. Vì thế nó được gọi là bản ghi ma (phantom row).

3. Repeatable read

Mức isolation này hoạt động nhứ mức read commit nhưng nâng thêm một nấc nữa bằng cách ngăn không cho transaction ghi vào dữ liệu đang được đọc bởi một transaction khác cho đến khi transaction khác đó hoàn tất. Trở lại hai cửa sổ:
Cửa số 1:

BEGIN TRAN
SELECT * FROM dbo.Item
WAITFOR DELAY '00:00:10' --wait for 10 seconds
SELECT * FROM dbo.Item
COMMIT

Cửa sổ 2:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
UPDATE dbo.Item
SET name = 'x'
WHERE id>2
SELECT * FROM item

Khi thực hiện code ở hai cửa sổ liên tiếp nhau, hai lệnh select ở cửa sổ 1 cho cùng kết quả và cửa sổ 2 phải đợi đến khi cửa sổ 1 hoàn tất mới được thực hiện. Mức isolation này đảm bảo các lệnh đọc trong cùng một transaction cho cùng kết quả, nói cách khác dữ liệu đang được đọc sẽ được bảo vệ khỏi cập nhật bởi các transaction khác. Tuy nhiên nó không bảo vệ được dữ liệu khỏi insert hoặc delete: nếu bạn thay lệnh update ở cửa sổ thứ hai bằng lệnh insert, hai lệnh select ở cửa sổ đầu sẽ cho kết quả khác nhau. Vì thế nó vẫn không tránh được hiện tượng bản ghi ma.

4. Serializable

Mức isolation này tăng thêm một cấp nữa và khóa toàn bộ dải các bản ghi có thể bị ảnh hưởng bởi một transaction khác, dù là UPDATE/DELETE bản ghi đã có hay INSERT bản ghi mới. Nếu bạn thay cửa sổ 1 bằng đoạn code

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM dbo.Item
WAITFOR DELAY '00:00:10' --wait for 10 seconds
SELECT * FROM dbo.Item
COMMIT

và cửa sổ 2 bằng

INSERT INTO dbo.Item SELECT 4,'d'

Cửa sổ 2 sẽ bị treo đến khi cửa sổ 1 thực hiện xong, và hai lệnh SELECT trong cửa sổ 1 trả về kết quả giống nhau.

5. Snapshot

Mức độ này cũng đảm bảo độ cô lập tương đương với Serializable, nhưng nó hơi khác ở phương thức hoạt động. Khi transaction đang select các bản ghi, nó không khóa các bản ghi này lại mà tạo một bản sao (snapshot) và select trên đó. Vì vậy các transaction khác insert/update lên các bản ghi đó không gây ảnh hưởng đến transaction ban đầu. Tác dụng của nó là giảm blocking giữa các transaction mà vẫn đảm bảo tính toàn vẹn dữ liệu. Tuy nhiên cái giá kèm theo là cần thêm bộ nhớ để lưu bản sao của các bản ghi, và phần bộ nhớ này là cần cho mỗi transaction do đó có thể tăng lên rất lớn. Để thiết lập isolation mức này bạn cần đặt lại option của database:

ALTER DATABASE TestDB
SET ALLOW_SNAPSHOT_ISOLATION ON

Về phạm vi áp dụng các mức isolation

Các mức isolation từ 1 – 4 kể trên tăng theo thứ tự mức độ cô lập dữ liệu, giúp tăng tính toàn vẹn dữ liệu và nhất quán của transaction. Đồng thời nó cũng tăng thời gian chờ lẫn nhau của các transaction. Khi càng lên mức cao, đòi hỏi về tính toàn vẹn dữ liệu càng cao và càng có nhiều tình huống một transaction ngăn không cho các transaction khác truy nhập vào dữ liệu mà nó đang thao tác. Do đó nó càng tăng tình trạng locking và blocking trong database (ngoại trừ với snapshot thì tăng lượng bộ nhớ cần sử dụng). Hiệu năng của hệ thống do đó bị giảm đi. Thông thường, mức isolation read commited (mức mặc định) là phù hợp trong đa số các ứng dụng. Có thể một vài chức năng quan trọng (ví dụ chức năng ở trang admin update dữ liệu có ảnh hưởng đến toàn hệ thống) bạn cần tính toàn vẹn cao và phải chọn mức isolation cao hơn. Hoặc có những chức năng cần ưu tiên tốc độ thực hiện và có thể chấp nhận một chút dữ liệu không nhất quán, bạn có thể đặt xuống mức read uncommited. Bảng dưới đây tóm tắt các tính năng của từng mức isolation.

Mức Isolation Dirty read Nonrepeatable read Phantom read
Read Uncommitted Yes Yes Yes
Read Committed No Yes Yes
Repeatable read No No Yes
Serializable No No No
Snapshot No No No



Tags: , , , , ,

52 Comments
Posted on 23/10/2012 | Categories: SQL Server Programming

Các bài viết tương tự

Comments
  • yugiking0 (15/11/2012 9:18 pm)

    Chức năng trên có phải được sử dụng cho trường hợp truy xuất thông tin cho Bảng thông tin cổ phiếu ở sàn chứng khoán hay là cập nhật các tỷ giá tại các ngân hàng không ? Vì việc insert và update các bảng này liên tục nên cần lấy thông tin trong quá trình chạy. Có giải pháp nào đối với việc cập nhật thông tin bảng hiển thị bảng giá của Thông tin cổ phiếu (hay tương tự như vậy ) nghĩa là 1 table luôn được cập nhật liên tục và được truy xuất thay đổi chỉ 1 hay vài ô thôi, nếu như truy xuất cả 1 bảng như vậy rất mất thời gian.
    Cảm ơn anh rất nhiều.

    • Vũ Huy Tâm (16/11/2012 4:01 pm)

      Đây là tính năng của SQL Server và có thể dùng trong mọi ứng dụng. Bảng thông tin cổ phiếu là một trường hợp của một hệ thống giao dịch với tần suất cao và mỗi giao dịch thực hiện trong thời gian rất ngắn. Các mức isolation chỉ qui định mức độ cô lập của các bản ghi đang được cập nhật chứ không phải cả bảng. Tôi chưa hiểu câu hỏi của bạn liên hệ thế nào với cái này. Nếu truy xuất cả 1 bảng trong đó chỉ 1 vài ô thay đổi, khả năng cao là SQL Server sẽ lưu (cache) kết quả và lần truy vấn sau sẽ đọc từ cách thay vì từ bảng.
      Tôi chưa làm về lĩnh vực này nên không có nhiều kinh nghiệm, nhưng theo tôi hiểu thì cần có bảng lưu tất cả các thay đổi giá cổ phiếu và thời điểm thay đổi, chứ không chỉ có giá hiện tại. Nếu cần như vậy, bạn phải có một bảng riêng, được liên tục insert cho mỗi lần giá thay đổi và lần insert cuối cùng (thời điểm gần đây nhất) của mỗi công ty là giá cổ phiếu hiện tại của công ty đó. Đồng thời, tôi cũng không rõ là sau mỗi lần thay đổi giá, bảng hiển thị phải được refresh ngay tức thì, hay vẫn được refresh theo định kỳ (ví dụ 30 giây hay 1 phút…)?

      • pé lạc đường (18/06/2014 9:04 am)

        Vấn để transaction isolation lock gì gì này mình còn mơ hồ quá, hiện tại đồ án lập trình web bằng java của mình là quàn lý bán quàn áo gồm có cá bảng sau:
        -khách hàng: thông tin và tài khoản đăng nhập
        -admin
        -sản phẩm:
        -loại sản phẩm
        -thương hiệu
        -hóa đơn
        -chi tiết hóa đơn
        hiện cô giáo đang yêu cầu đặt các transaction rồi lock vào đồ án , nói chung phải có giải quyết tranh chấp. mình k bit là phải đặt cái yeu cầu đó vào chức năng nào trong cái đồ án của mình và phải viết nó ra sao nữa, bạn tư vấn giúp mìh đc k.
        cảm ơn bạn nhìu nhé!

        • tung (19/06/2014 3:33 am)

          Bạn có thể giả định tình huống 1 sản phẩm có nhiều khách hàng cùng đặt tại 1 thời điểm :
          VD : trong kho số lượng còn 10 nhưng có 2-3 khách hàng cùng mua hàng tại 1 thời điểm .
          Về cái này nó còn liên quan đến lock nữa nhưng dạo này chưa thấy bác Tâm viết về vấn đề này . Cái này minh cũng đang cần tìm hiểu thêm.

          • pé lạc đường (19/06/2014 1:08 pm)

            uhm đúng ùi đó bạn, mình đang k biết viét lock chỗ này ra sao nữa, hic, không biết bạn Vũ Huy Tâm đâu rồi ha

            • Red Devilic (23/06/2014 3:29 am)

              Mình cũng chưa làm về hệ thống booking online bao giờ nhưng mình nghĩ có thể xử lý như sau:

              - Khách hàng được quyền đặt mua tại 1 thời điểm, miễn là số hàng mua <= Số tồn kho. Tuy nhiên khi bắt đầu thực hiện update đơn hàng nếu số tồn kho < số hàng mua thì bạn thông báo ko còn hàng.

              Điều này về logic bán hàng, như mua vé máy bay khi bạn mua còn vé, nhưng đến bước thanh toán thì vé đã hết, ai mua trước được nhận hàng trước.

              - Mỗi món hàng mua có ID riêng. Ví dụ bạn mua vé xem phim hay vé tàu thì mỗi vé bạn mua đều có 1 ID duy nhất. Bạn có thể set trạng thái thành đang mua, sau đó xử lý như bình thường, nếu ko thanh toán nữa thì set lại trạng thái available.

              Có thể đặt 1 job quét 10p/lấn để update lại trạng thái trong trường hợp khách hàng out giao dịch khiến hệ thống ko update lại trạng thái từ đang xác nhận thành available.

              Bạn nào biết cách xử lý tốt hơn mời chia sẻ.

        • nguoihanoi (05/07/2014 1:16 am)

          Cô giáo nào mà rắc rối quá thế, chắc là còn trẻ và rất xinh rồi :) , “yêu cầu đặt transaction rồi lock vào đồ án” là thế nào ?

          Cô ấy mà còn yêu cầu lăng nhăng thì cứ bảo “cô có làm được hay không mà bảo tui, hay là cô làm không được nên giả bộ ra bài tập cho học trò để có lời giải?” :) :( :)

  • nguoihanoi (20/04/2013 11:01 am)

    Tui không hiểu tại sao chủ xị lại không đề cập đến read_committed_snapshot. Mỗi loại đều có những trường hợp thích hợp để sử dụng, không có loại nào thích hợp cho tất cả mọi trường hợp, nhưng read_committed_snapshot là thích hợp với nhiều trường hợp nhất.

  • Vũ Huy Tâm (23/04/2013 10:25 am)

    Ừ tôi thiếu cái này, hay là bác viết đi tôi sẽ thêm vào.
    *Nói là thích hợp với nhiều trường hợp nhất thì tôi e là quá, snapshot kéo theo overhead rất lớn vì nó phải lưu copy của bản ghi vào tempdb cho mỗi truy vấn. Xem khuyến cáo của Microsoft: http://msdn.microsoft.com/en-us/library/ms188277.aspx

  • nguoihanoi (24/04/2013 4:37 pm)

    “Ừ tôi thiếu cái này, hay là bác viết đi tôi sẽ thêm vào.”

    Chào bác chủ xị. Tui tửu lượng kém, xin bác tha cho.

    “*Nói là thích hợp với nhiều trường hợp nhất thì tôi e là quá, snapshot kéo theo overhead rất lớn vì nó phải lưu copy của bản ghi vào tempdb cho mỗi truy vấn. Xem khuyến cáo của Microsoft: http://msdn.microsoft.com/en-us/library/ms188277.aspx

    Tui học hỏi được nhiều điều từ trang web của bác, tuy nhiên có 1 số điểm tui nghĩ hơi khác, và đây là 1 vài ví dụ.

    Mỗi level đều có trường hợp ứng dụng thích hợp. Nếu nói là được sử dụng nhiều nhất thì phải là read committed vì nó là default và phần lớn người sử dụng chẳng lưu tâm nó hoạt động thế nào, chạy được là mừng rồi, còn chạy đúng trên môi trường thử đã là tuyệt vời. Nhưng môi trường thực khác môi trường thử rất nhiều, lỗi chỉ xuất hiện trong môi trường dữ liệu lớn, nhiều người sử dụng.

    Tui vẫn cho rằng read_committed_snapshot là thích hợp với nhiều trường hợp nhất (không phải tất cả), chỉ vì người ta không lưu tâm, ý thức mà sử dụng thôi. Xin đưa ra đây 1 ví dụ: Nếu sử dụng read committed (default), ta vẫn có thể nhận được 1 kết quả sai, tức dữ liệu chưa bao giờ tồn tại ở bất cứ thời điểm nào, bất kể đã committed hay chưa. Điều này thường không xảy ra trong môi trường thử nên nhiều người không để ý, chỉ đến khi chay trên môi trường thực, rất nhiều người sử dụng, mới bỗng nhiên thấy mà chẳng hiểu vì sao kết quả lại không đúng như ý mình.

    read_committed_snapshot và snapshot chỉ copy data khi sửa đổi dữ liệu thôi. Với câu truy vấn, nó không copy dữ liệu, nên overhead có lẽ không quá lớn. Dĩ nhiên, không có bữa trưa nào miễn phí, nhưng ta phải xét đến lợi ích mà nó đưa lại.

    Còn về khuyến cáo thì tất cả level đều được khuyến cáo chứ không chỉ read_committed_snapshot. Không có level nào đúng và chẳng có cái nào sai. Quan trong là hiểu đúng và sử dụng mỗi loại cho thích hợp với yêu cầu của mình.

    Rất vui được trao đổi kiến thức với chủ xị.

  • Vũ Huy Tâm (29/04/2013 11:16 am)

    Hì hì, tôi thì lúc nào cũng thấy có cái mình biết mà người khác không biết và ngược lại, có cái người khác biết mà mình không biết. Viết ở đây cũng là cách tự kéo mình và mọi người lên. Tôi không nghĩ trình phải đến mức nào mới được viết.
    Bác nói read_committed_snapshot là thích hợp với nhiều trương hợp nhất, tôi không thể đồng tình với bác và tôi chưa thấy một thống kê nào như vậy. Các ứng dụng có yêu cầu rất khác nhau và trong bản thân một ứng dụng các tính năng cũng đã có những đòi hỏi khác nhau, nên người làm phải chọn lấy mức isolation thích hợp để cân bằng giữa tính tương tranh (giảm thiểu khóa) và yêu cầu nhất quán dữ liệu. Cái này thì tôi đồng ý với bác, nó không phải là thuốc chữa bách bệnh mà chỉ là thêm một lựa chọn cho mình. read_committed_snapshot giúp tránh blocking giữa đọc và ghi, lợi ích có thể rất lớn trong một số trường hợp, nhưng tôi có thể thấy ngay có những trường hợp không thể chấp nhận được điều này. Và đây là một ví dụ tôi nghĩ khá điển hình về vấn đề của nó:
    http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

  • tung (12/06/2013 7:03 am)

    Chào 2 bác , tôi cũng vào trang web này nhiều và có điều thắc mắc với 2 bác là : nếu sử dụng read_committed_snapshot thì sẽ không bị lock khi mình lấy dữ liệu ra phải không ạ ?

  • nguoihanoi (13/06/2013 12:37 am)

    Cũng như snapshot, read_committed_snapshot không khóa dữ liệu khi đọc. Điều này tránh được hiện tượng chờ nhau giữa đọc và viết, và dẫn đến việc tránh được deadlock ở nhiều trường hợp.

    Tuy nhiên, để sử dụng trong thực tế, bác phải hiểu rõ cách thức hoạt động cúa nó (và loại khác cũng thế), nếu không bác sẽ gặp những kết quả ngoài dự định, mà nổi bật nhất là update lost.

    Để giải thích cụ thể thì dài dòng, tui chỉ nói ngắn gọn thế này : nó không lock dữ liệu. Nhưng điều bác cần lưu tâm hơn là dữ liệu nó lấy ra là dữ liệu nào.

    Dữ liệu nó lấy ra là dữ liệu đã được commited ngay trước thời điểm câu lệnh được thực hiện. Ý thức điều này, bác có thể hình dung ra những hệ lụy mà nó mang lại.

    Tuy vậy, so sánh với những loại khác nó vẫn thích hợp với nhiều trường hợp nhất, mặc dù trong thức tế hầu hết đều sử dụng read committed (mặc định). (Tui vẫn bất đông với bác chủ xị về vấn đề này hehe)

  • Vũ Huy Tâm (13/06/2013 10:52 am)

    Lâu lắm mới thấy bác nguoihanoi trở lại :)

  • Red Devilic (14/06/2013 3:31 am)

    Hi bác nguoihanoi. Có đoạn này mình không rõ lắm, bác chỉ giáo thêm

    Xin đưa ra đây 1 ví dụ: Nếu sử dụng read committed (default), ta vẫn có thể nhận được 1 kết quả sai, tức dữ liệu chưa bao giờ tồn tại ở bất cứ thời điểm nào, bất kể đã committed hay chưa. Điều này thường không xảy ra trong môi trường thử nên nhiều người không để ý, chỉ đến khi chay trên môi trường thực, rất nhiều người sử dụng, mới bỗng nhiên thấy mà chẳng hiểu vì sao kết quả lại không đúng như ý mình.

    Theo mình hiểu ý bác nói là 1 trong 2 trường hợp nonrepeatable read hoặc phantom read nhưng sau khi đọc kĩ đoạn bác viết lại thấy nó ko rơi vào trường hợp nào hết :D

    Tiếp theo thảo luận 1 chút về vấn đề READ COMMITTED và read_committed_snapshot, thực sự mình thấy vấn đề này của các bác chẳng khác gì đang thảo luận sử dụng EXISTS hay sử dụng IN tối ưu hơn vậy.

    Nếu nói về default, thì ban đầu sơ khai của các RDBMS đều để chế độ mặc định là Serializable. Cũng không rõ từ phiên bản nào mà hiện giờ cả Oracle hay SQL Server đều để chế độ là READ COMMITTED. Có lẽ là để chấp nhận nâng cao hiệu năng thì phải reduce lock.

    Thứ 2, vì read_committed_snapshot là cái ra đời sau, từ bản 2k5 thì phải, nên tất cả các ứng dụng trước đó đều có default là READ COMMITED, vì thế chắc chắn là rất nhiều code cũ đã viết dựa trên READ COMMITTED hoặc các isolation khác. Có thể ở một số chỗ sử dụng read_committed_snapshot thì sẽ hiệu quả hơn nhưng để thay thế và viết lại cả một hệ thống như vậy là không đơn giản.

    Mình cũng ko hay làm về SQL Server nữa, bác Tâm và nguoihanoi có thể trả lời giúp mình 1 vấn đề không. read_committed_snapshot chắc là đọc last commited value, dựa trên row version, như vậy chắc phải lưu vào tempdb đúng không. Nếu cả hệ thống chuyển hẳn sang read_committed_snapshot thì phải tốn thêm tầm bao nhiêu % dung lượng cho cái tempdb nữa ?

  • nguoihanoi (14/06/2013 1:43 pm)

    Chào bác Đỏ

    “Theo mình hiểu ý bác nói là 1 trong 2 trường hợp nonrepeatable read hoặc phantom read nhưng sau khi đọc kĩ đoạn bác viết lại thấy nó ko rơi vào trường hợp nào hết ”

    Không, tui nói về read committed, chứ không phải 2 trường hợp bác nêu. Và tui có nói rõ mà : “Nếu sử dụng read committed (default)”.

    “Tiếp theo thảo luận 1 chút về vấn đề READ COMMITTED và read_committed_snapshot, thực sự mình thấy vấn đề này của các bác chẳng khác gì đang thảo luận sử dụng EXISTS hay sử dụng IN tối ưu hơn vậy.”

    Khác nhiều lắm.

    “Cũng không rõ từ phiên bản nào mà hiện giờ cả Oracle hay SQL Server đều để chế độ là READ COMMITTED. Có lẽ là để chấp nhận nâng cao hiệu năng thì phải reduce lock.”

    Isolation Level của 2 cái này không tương đương nhau nên cũng khó diễn tả. Đại khái là :
    - SQL Server có chế độ mặc định là read committed. Câu truy vấn khóa nguồn mà nó đang truy cập với shared lock
    - Oracle có chế độ mặc định là “read_committed_snapshot”. Sở dĩ tui để trong ngoặc kép là vì nó hoạt động tương tự như thế chứ không phải cái tên. Trong Oracle, câu truy vấn không lock (default)

    Tui nghĩ cái read_committed_snapshot là ý tưởng Microsoft copy từ Oracle.

    “Thứ 2, vì read_committed_snapshot là cái ra đời sau, từ bản 2k5 thì phải, nên tất cả các ứng dụng trước đó đều có default là READ COMMITED, vì thế chắc chắn là rất nhiều code cũ đã viết dựa trên READ COMMITTED hoặc các isolation khác. Có thể ở một số chỗ sử dụng read_committed_snapshot thì sẽ hiệu quả hơn nhưng để thay thế và viết lại cả một hệ thống như vậy là không đơn giản.”

    Tui đồng ý với bác. Và đó là lý do tui nói khá nhiều lần rằng với SQL Server, read committed được sử dụng nhiều nhất trong thực tế. Nhưng read_committed_snapshot thích hợp với nhiều trường hợp nhất.

    “Mình cũng ko hay làm về SQL Server nữa, bác Tâm và nguoihanoi có thể trả lời giúp mình 1 vấn đề không. read_committed_snapshot chắc là đọc last commited value, dựa trên row version, như vậy chắc phải lưu vào tempdb đúng không. Nếu cả hệ thống chuyển hẳn sang read_committed_snapshot thì phải tốn thêm tầm bao nhiêu % dung lượng cho cái tempdb nữa ? ”

    Với SQL Server, data dùng cho read_committed_snapshot được lưu ở tempdb. Còn Oracle, chúng được lưu ở undo segment, ngoài ra undo segment còn dùng cho rollback, nên xét tổng quát nó quan trọng và phức tạp hơn tempdb ở SQL Server nhiều.

    Với ứng dụng lớn, nhiều người sử dụng, dữ liệu thay đổi liên tục, với những transactions lớn thì bảo trì tempdb là việc rất đáng lưu tâm, còn các ứng dụng thông thường thì tui nghĩ không đến nỗi nào vì SQL Server tự động xóa những dữ liệu không còn cần thiết trong tempdb.

    Nếu bác không đồng ý chỗ nào, xin nêu lên. Rất vui được học hỏi.

  • Red Devilic (16/06/2013 10:31 am)

    Hi bác hanoi

    “Nếu sử dụng read committed (default), ta vẫn có thể nhận được 1 kết quả sai, tức dữ liệu chưa bao giờ tồn tại ở bất cứ thời điểm nào, bất kể đã committed hay chưa. Điều này thường không xảy ra trong môi trường thử nên nhiều người không để ý, chỉ đến khi chay trên môi trường thực, rất nhiều người sử dụng, mới bỗng nhiên thấy mà chẳng hiểu vì sao kết quả lại không đúng như ý mình.”

    Mình cần dẫn chứng ở đoạn này. Tức là mình muốn có 1 ví dụ cụ thể, đọc câu trên của bác hơi khó hiểu nên mình cũng không tự nghĩ ra được một ví dụ nào hợp lý. Và mình cho là trường hợp kết quả sai này nằm ở nonrepeatable read và phantom read

    - Cái READ COMMITTED trong Oracle đúng là được based trên READ COMMITTED default của SQL. Và sau khi tìm hiểu lại thì cơ chế của nó đúng là giống read_committed_snapshot trên SQL Server thật.

    “Với SQL Server, data dùng cho read_committed_snapshot được lưu ở tempdb. Còn Oracle, chúng được lưu ở undo segment, ngoài ra undo segment còn dùng cho rollback, nên xét tổng quát nó quan trọng và phức tạp hơn tempdb ở SQL Server nhiều” => Đồng ý, nhưng việc quản lý này thường là automatic (với các phiên bản >= 9i) và việc quản lý này là quản lý ở mức rộng hơn trên undo tablespace. Mình cũng thường kiểm tra nhưng chỉ dừng ở mức dung lượng của undo tablespace này có khả năng bị đầy hay không.

  • tung (16/07/2013 4:34 am)

    Cái read_committed_snapshot này thì nó sẽ đọc ở thời điểm trước khi lệnh comit đúng không ạ ? Thực tế sử dụng cái read_committed_snapshot kết hợp với SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED với tùy trường hợp trong cùng 1 database được không ạ ?

    • giotnuoc (16/07/2013 10:54 am)

      Cái read_committed_snapshot này thì nó sẽ đọc ở thời điểm trước khi lệnh comit đúng không ạ?
      ==> Những dữ liệu đã commit (dĩ nhiên phải thỏa điều kiện của câu DML) Bạn có thể kết hợp tuần tự nhiều isolation level khác nhau.

      Ví dụ :

      time session1

      08:05 am SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

      80:10 am select * from t1

      08:30 am SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

      80:40 am select * from t2

      Từ 08:05am -> 08:30am : isolation level sẽ là READ UNCOMMITTED
      Từ 08:30am trở về sau : isolation level sẽ là SERIALIZABLE

      • giotnuoc (16/07/2013 11:02 am)

        Do reply trước của tôi bị lỗi, tôi xin trả lời ý thứ nhất của bạn Tung:

        Cái read_committed_snapshot này thì nó sẽ đọc ở thời điểm trước khi lệnh comit đúng không ạ?
        ==> Những dữ liệu đã commit (dĩ nhiên phải thỏa điều kiện của câu DML) <= thời điểm bạn thực thi câu lệnh DML (delete , update , insert, select…).

        • giotnuoc (16/07/2013 11:11 am)

          ví dụ : có table t1(id, name) trong đó dòng có id=1 thì name=’A’

          Lúc 08:05 , session1 thực hiện câu lệnh

          SELECT * FROM t1

          , giả sử câu select này đến 08:15 mới trả về kết quả.
          Trong khi đó lúc 08:07 session2 thự hiện câu lệnh

          UPDATE t1 SET t1.name='B' WHERE id=1

          , câu lệnh này mất 3s đẻ hoàn thành.

          Mặc dù session2 đã update giá trị của cột name thành ‘B’ nhưng trong dữ liệu trả về ở session1 dòng có id=1 thì name vẫn có giá trị là ‘A’.

  • tung (15/09/2013 11:59 pm)

    Cảm ơn bạn giọt nước nhé ! Để hiểu và làm cai transaction này làm sao cho không bị block dữ liệu khi có nhiều người truy cập cũng khó nhỉ ? Có cách nào nó chỉ khóa theo row mà không khóa nguyên bảng không bạn ?

  • liem (03/10/2013 5:59 am)

    Hi mọi người,

    Mình gặp một vấn đề là khi 2 câu lệnh update cùng 1 table, nhưng update dữ liệu của 2 tháng khác nhau. dữ liệu hơi lớn, câu lệnh chạy lâu nên bị lock, cho dùng mình đã set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. có cách nào khắc phục được không

    cảm ơn

  • nguoihanoi (04/10/2013 1:33 pm)

    1 – update không bị hưởng bởi … SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    2 – với update, các rows luôn bị lock cho đến khi thực hiên xong.

    3 – 2 câu lệnh update phải chờ nhau. Có lẽ đây là vấn đề bạn muốn giải quyết. Nếu thế thì bạn cần tạo indexes để khi update, 2 câu lệnh đọc dữ liệu theo 2 đường khác nhau. Nếu số lượng rows cần update quá nhiều, bạn cần lưu tâm đến lock escalation, nếu không sẽ dẫn đến deadlock

    • tung (07/10/2013 10:40 pm)

      Bạn nguoihanoi cho hỏi ngoài trường hợp update luôn bị lock , còn có thêm trường hợp nào khác bị như vậy nữa không. Cảm ơn bạn đã chia sẻ.

      • nguoihanoi (14/10/2013 5:02 pm)

        Khi truy cập hay sửa đổi dữ liệu trong db, dữ liệu luôn bị lock (trừ trường hợp sử dụng nolock), tuy nhiên có nhiều loại lock khác nhau, có nhiều cấp độ khác nhau, và có nhiều khác biệt về thời gian giữ lock.

        Lock là 1 chủ đề cực kỳ quan trọng nếu 1 người muốn thực sự hiểu về cơ sở dữ liệu. Vận hành Lock là 1 trong những yếu tố chính làm nên sự khác biệt giữa tập tin và cơ sở dữ liệu, cũng như sự các biệt giữa các cơ sở dữ liệu với nhau như SQL Server và Oracle. Và phải nói nó khá phức tạp.

        Nhưng nó lại không ảnh hưởng tức thời, tức nó chỉ thể hiện rõ khi có rất nhiều người truy cập trong cùng 1 thời điểm, và dữ liệu lớn. Nên nhiều người không lưu tâm. Ở môi trường chạy thử, lock hầu như không đóng vai trò gì vì dữ liệu quá ít và chỉ có 1 vài người truy cập trong cùng 1 thời điểm.

        Tui đã gặp nhiều lập trình viên, tốt nghiệp ngành công nghệ thông tin hẳn hoi, với kinh nghiệm 5 năm, vẫn không hiểu về lock. Điều này không phải họ dở, chỉ vì không có nhu cầu.

        Vì thế khó có thể giải thích cho bạn với câu hỏi trên. Hình như chủ xị có 1 bài viết chuyên về lock, bạn nên tìm hiểu, có khái niệm tổng quát về nó, khi ấy câu hỏi bạn sẽ cụ thể hơn. Nếu bạn có câu hỏi về vấn đề này xin cứ nêu lên, tui nghĩ trong này có nhiều người có thể giải thích cho bạn.

        • Vũ Huy Tâm (15/10/2013 9:50 am)

          Hiện tôi chưa có bài nào về lock, bác nguoihanoi có lòng chia xẻ xin mời viết 1 bài

  • tung (25/10/2013 12:17 am)

    Vâng em chính là người cần tìm hiểu về lock như trường hợp bác nói đấy . Bác có tài liệu hay cách giải quyết lock thì chia sẽ cho mọi người với ạ ? Như bác nói :
    “Nhưng nó lại không ảnh hưởng tức thời, tức nó chỉ thể hiện rõ khi có rất nhiều người truy cập trong cùng 1 thời điểm, và dữ liệu lớn. Nên nhiều người không lưu tâm. Ở môi trường chạy thử, lock hầu như không đóng vai trò gì vì dữ liệu quá ít và chỉ có 1 vài người truy cập trong cùng 1 thời điểm.”

    Cái này em đang bị và đang đau đầu tìm cách giải quyết ạ ?

  • liem (31/10/2013 4:11 am)

    Chính mình cũng đang gặp vấn đề này, đau đầu thiệt, mình mong bác nguoihanoi chia sẽ kinh nghiệm vấn đề này

  • nguoihanoi (01/11/2013 1:07 pm)

    Tui đã thử viết 1 bài về lock nhưng đọc thấy lủng củng quá, viết rồi xóa 2,3 lần. Thế mới phục bác chủ xị.

    Bác có thể nêu trường hợp cụ thể về vấn đề lock của bác không? Tui trả lời linh tinh thì được chứ viết thành 1 bài hẳn hoi thì khó quá.

    • Vũ Huy Tâm (04/11/2013 2:18 pm)

      Bác làm gì mà như viết tiểu thuyết vậy ?! Mục đích truyền đạt thông tin là chính, mấy cái trình bày câu cú phiên phiến thôi

  • tung (04/11/2013 2:37 am)

    Trường hợp của em thế này ạ :

    - em có thể có nhiều transaction : những cái này thực hiên tác vụ vừa select bản ghi tìm xem đã có bản ghi đó thông qua trường status = true thì thực hiện lệnh update. Cái khó ở đây là từ câu lệnh select đến câu lệnh update time của nó cũng kha khá ạ . Nên nếu lượng người thực hiện thao tác cùng 1 lúc lớn thì nó hay dẫn tới trường hợp dealock ạ. Ví dụ như sau :

      SELECT status FROM jobs WHERE jobid = 5
    //Nếu status = TRUE thực hiện lệnh 1 loạt tác vụ UPDATE
     
    UPDATE status SET status = FALSE WHERE jobid = 5

    Khi có nhiều transaction như trên xảy ra thì hay xảy ra hiện tượng dealock.

    • Vũ Huy Tâm (05/11/2013 2:17 pm)

      Trước khi có 1 bài về lock thì tôi có 1 version ngắn hơn cho bạn để có thể áp dụng được liền:
      1. Phân tích mấy câu UPDATE để xem tạo index thế nào để trợ giúp cho chúng. Index là giải pháp cho mấy vấn đề sau:
      - nó giúp các câu UPDATE chạy nhanh hơn, giảm bớt tương tranh giữa các lệnh cùng chạy tại thời điểm đó.
      - quan trọng hơn, nếu lượng bản ghi update đủ nhỏ, nó có thể giữ lock ở mức thấp (page hoặc extent). Khi đó các bản ghi của mỗi tiến trình update nằm ở page hoặc extent khác nhau sẽ không bị lock lẫn nhau. Bác nguoihanoi cũng đã nói điều này trong 1 comment ở trên, trong slide hội thảo về index (sql-server-seminar-he-2011) tôi cũng đề cập đến chuyện này.

      2. Đồng thời, bạn cần giữ nguyên thứ tự các bảng cần update. Ví dụ update 3 bảng a, b, và c thì luôn update theo thứ tự này để tránh deadlock (tôi nghĩ bạn đã làm thế rồi).

      3. Bạn cần đưa toàn bộ đoạn code trên vào 1 transaction

      • tung (05/11/2013 9:48 pm)

        vâng các vấn đề bác nói thì em đã thực hiện rồi ạ? Cái khó của e là giữa các lệnh update thành công thì phải thực thi 1 số thao tác như move file hoặc copy file nữa ==> thời gian đợi giữa các lần update là khá lâu .

        • Vũ Huy Tâm (06/11/2013 10:19 am)

          tính năng ở trên là cho người dùng bình thường hay cho một nhóm người dùng? Bác mô tả bài toán kỹ hơn chút đi

          • tung (07/11/2013 5:01 am)

            Yêu cầu của bài toán là :
            – Có 1 jobs ảnh được lưu trên database (job tổng) , và sẽ chia các ảnh trong job cho nhân viên làm khi nhân viên click vào 1 button trên trang giao diện .
            Em có thể mô tả cho bác thông qua ví dụ tạm dưới đây nhé :

            //đầu tiên tìm tất cả các IMAGE của jobId = 5 mà chưa được chia (ở đây em vd là status = TRUE thì chia rồi FALSE là chưa chia )
            BEGIN TRAN
            BEGIN TRY
               SELECT status FROM images WHERE status = FALSE and jobId = 5
            //nếu tồn tại ảnh chưa chia thì chia cho nhân viên thông qua việc UPDATE cái images đấy = FALSE
            UPDATE images SET status = FALSE WHERE jobId = 5
            //tiếp tục thực hiện copy FILE sang thư mục nhân viên
            COMMIT
            END TRY
            BEGIN CATCH
             ROLLBACK
            END CATCH
            • Vũ Huy Tâm (08/11/2013 10:23 am)

              À thế thì tôi gợi ý cho bác thế này:
              - không cần dùng transaction, việc chính là copy file mà copy file không thuộc SQL Server (nó phải nhờ OS làm hộ) nên transaction không có vai trò gì.
              - trường status thay vì dùng boolean true/false thì dùng 3 trạng thái, ví dụ 0 – chưa được xử lý, 1 – đang được xử lý, 2 – đã được xử lý.
              Làm như vậy bạn không cần lock bảng trong suốt quá trình copy file. Trình tự sẽ như thế này:
              a. select * from images where status = 0 and jobid = 5 –chưa được xử lý
              b. nếu tồn tại ảnh chưa chia:
              b.1. update image set status = 1 where jobid=5 –đánh dấu nó là đang xử lý
              b.2. copy ảnh
              b.3. nếu copy thành công, update image set status = 2 where jobid=5 ELSE
              b.4. (copy không thành công), update image set status = 0 where jobid=5 –đặt trở lại để xử lý lần sau

              • tung (08/11/2013 10:59 pm)

                Để em thử rồi báo lại cho bác . Nhưng làm thế này có sợ trường hợp nhiều người cùng lấy 1 file sẽ sinh ra lỗi không ạ . Em cũng chưa hiểu rõ cơ chế làm việc của SQL lắm . Không biết câu lệnh select nếu nhiều người cùng dùng tại 1 thời điểm liệu nó còn chính xác không ạ ?

                • Vũ Huy Tâm (11/11/2013 1:57 pm)

                  về lý thuyết thì có thể xảy ra xung đột, nhưng 3 lệnh a, b, và b1 chạy chắc mất 1 mili giây nên khả năng gần như bằng 0 trừ khi bác có hàng ngàn user chạy liên tục. Để đảm bảo tuyệt đối thì bác bao giữa 3 lệnh trên trong 1 transaction và đóng transaction sau khi update đươc status thành 1 (tức là đang xử lý).
                  Tuần này tôi hơi bận nên sẽ trả lời dần các câu hỏi khác của các bác

                  • tung (11/11/2013 9:58 pm)

                    Vâng ạ . Cảm ơn bác nhiều !

        • Vũ Huy Tâm (06/11/2013 10:47 am)

          Tôi không định bới bèo ra bọ nhưng một nguyên tắc chung là phải giữ transaction thật ngắn, thực hiện càng nhanh càng tốt để giảm thiểu xung đột với các transaction khác; tất nhiên ngoại trừ những chức năng quan trọng để bảo trì dữ liệu (ví dụ các chức năng admin). Nếu bác đưa những lệnh update dài và cả các bước copy file thì độ tương tranh sẽ giảm xuống, chưa kể đến chuyện bước copy file nằm ngoài kiểm soát của SQL Server, nếu trong transaction có lỗi và rollback thì những file đã được copy rồi vẫn nằm yên đấy không bị rollback. Mấy cái này tất nhiên thuộc về yêu cầu bài toán và thiết kế của bạn, nếu chỉ cố gắng tối ưu về khóa chưa chắc đã giải quyết được.

          • tung (06/11/2013 10:32 pm)

            Vâng cái bác nói em cũng suy nghĩ rồi ạ ? Chẳng qua yêu cầu của người dùng nó là vậy . E cũng cố gắng đọc và tìm hiểu để làm sao cho câu lệnh trong transaction thời gian giảm xuống đáng kể nhất . Cảm ơn bác và chúc bác sớm ra bài viết về lock để em có thể bổ sung thêm kiến thức của mình .

  • tung (04/11/2013 10:09 pm)

    Bác tâm cho mình hỏi chút về READ COMMITTED nếu mình viết :

    INSERT INTO dbo.Item SELECT 5,'e'
     
    //so với 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    INSERT INTO dbo.Item SELECT 5,'e'
    //la không khác nhau phải không ạ ?
    • Vũ Huy Tâm (05/11/2013 10:28 am)

      Không khác gì nhau, vì cái set isolation level này chỉ tác động đến các tiến trình đọc dữ liệu đang diễn ra.
      Lúc nào rảnh tôi sẽ viết 1 bài về lock cho các bác ném đá

      • tung (05/11/2013 11:52 am)

        vâng . Cảm ơn bác ạ ! Bác viết luôn cho mọi người mở rộng thêm kiến thức . Mong mỏi từng ngày (vì em đang dính cái này :( )

      • tung (05/11/2013 11:59 am)

        Vâng ý em hỏi ở đây là tại vì thấy bác bảo default của nó là READ COMMITTED nên khi mình dùng default thì không cần thêm cái dòng đấy vào nữa . Hiểu như vậy có đúng không ạ ?

        • Red Devilic (07/11/2013 10:18 am)

          Đúng !

          • tung (07/11/2013 9:31 pm)

            Thanks bác nhé !

  • Minh Nguyễn (21/12/2014 11:03 am)

    Cho 2 transaction T1, T2 lần lượt thực hiện transaction như thứ tự như sau:

    T1: begin tran
    T2: begin tran
    T1: select* form SACH
    T2: Insert SACH(MaSach) Values (‘S003′)
    T1: SET @Msach = (SELECT Top 1 MaSach From SACH Order by MaSach DESC)\
    T2: Rollback
    T1: …
    T2: Commit

    phân tích lỗi và đưa ra cách sữa lỗi. Mong bác giúp đỡ.

    • Minh Nguyễn (21/12/2014 11:06 am)

      Xử lý lỗi ở mức cô lập (Isolation) và cấp độ khóa.

  • Viết chưa chính xác. (10/11/2016 11:40 am)

    Chào
    Ví dụ hay, nhưng một vài chổ chưa chính xác.

  • nguyenhieu1295 (18/04/2017 12:48 pm)

    Em test trên SQL 2016 ở mức 3. Repeatable read
    Trường hợp insert thì Repeatable read không bảo vệ được, còn update với delete thì Repeatable read bảo vệ được. (bảo vệ được tức là dữ liệu trước và sau giống nhau, và câu lệnh update/delete phải chờ tran ở query1 thực thi xong thì câu lệnh update/delete mới được thực thi.)

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>