Re: [Bodington-developers] Locking Issues (Java, Explicit DB, Optimistic DB)

[Matthew B. <mat...@ou...>]

Peter Crowther wrote:
>> From: Matthew Buckett
>> If we updated to TRANSACTION_REPEATABLE_READ 
>> then we don't have to 
>> worry about a read row being modified under our feet.
> 
> Sorry, doesn't work - you have to go to SERIALIZABLE on MSSQL at least.
> Otherwise:
> 
> - A reads (acquires read lock);
> - B reads (acquires read lock);
> - A adds;
> - B adds;
> - A updates (tries to upgrade to write lock, waits on B's read lock);
> - B updates (tries up upgrade to write lock, system detects deadlock and
> rolls back one transaction).
> 
> ... Causes problems at best, deadlock at worst, I think.

Yep your right. As a side note PostgreSQL implements 
TRANSACTION_REPEATABLE_READ as SERIALIZABLE.

-- 
  -- Matthew Buckett, VLE Developer
  -- Learning Technologies Group, Oxford University Computing Services
  -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/