bodington-developers

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

[Peter C. <Pet...@me...>]

> From: Matthew Buckett
> If we updated to TRANSACTION_REPEATABLE_READ=20
> then we don't have to=20
> 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.

		- Peter

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

[Peter C. <Pet...@me...>]

> From: Matthew Buckett
> Yep your right. As a side note PostgreSQL implements=20
> TRANSACTION_REPEATABLE_READ as SERIALIZABLE.

Bah, these half-complete databases ;-).

(As an amusing piece of history, up until SQL Server 7.0, MSSQL
implemented SERIALIZABLE as REPEATABLE_READ - far more dangerous and
very poorly documented)

		- Peter

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

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

Peter Crowther wrote:
>> From: Matthew Buckett
>> Yep your right. As a side note PostgreSQL implements 
>> TRANSACTION_REPEATABLE_READ as SERIALIZABLE.
> 
> Bah, these half-complete databases ;-).
> 
> (As an amusing piece of history, up until SQL Server 7.0, MSSQL
> implemented SERIALIZABLE as REPEATABLE_READ - far more dangerous and
> very poorly documented)

But a nice performance improvement ;-)

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

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/