3

I found a contradiction in a Wikipedia article and not sure where the mistake is (or maybe I don't understand it correctly).

According to Wikipedia in Read committed isolation level:

"In this isolation level, a lock-based concurrency control DBMS implementation keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed (so the non-repeatable reads phenomenon can occur in this isolation level, as discussed below)"

Further the explanation of Non-repeatable reads phenomena which can happen in Read committed isolation level:

Transaction 1: 

SELECT * FROM users WHERE id = 1;

Transaction 2: 

UPDATE users SET age = 21 WHERE id = 1; COMMIT;

Transaction 1: 

SELECT * FROM users WHERE id = 1; COMMIT;

According the the first quote a write lock should have been acquired after the 1st select statement in the 1st transaction. How come the second transaction can successfully aquire the write lock and commit if this lock type is supposed to be exclusive? Does the DBMS really keep write locks on selected data?

medvedev1088
  • 3,645
  • 24
  • 42

1 Answers1

3

Wikipedia is wrong in two ways:

  1. This is an implementation detail. An RDBMS doesn't have to do it that way. READ COMMITTED can be implemented without locks for reads at all by using snapshot isolation.
  2. No RDBMS I know of acquires write locks for reads. That sentence is ambiguous.
usr
  • 168,620
  • 35
  • 240
  • 369
  • `READ COMMITTED can be implemented without locks for reads at all by using snapshot isolation`: I am interested in such an implementation. Would you mind providing some references for this claim? Thanks. – hengxin Oct 20 '15 at 09:52
  • @hengxin when you read from a read-only snapshot you only read committed data. That's all there is to it. It's a trivial result because snapshot reads are much stronger than RC reads. – usr Oct 20 '15 at 09:57
  • Yes, I see. Thanks. BTW, are you aware of any lock-free (without write locks either) RC implementations? – hengxin Oct 20 '15 at 10:04
  • 1
    @hengxin SQL Server Hekaton has it. They do not use locks under any circumstances yet they offer SI and even full (not fake) serializable. Fascinating engineering I think. – usr Oct 20 '15 at 10:34