Re: counting rows

On 2006-04-01, Tony Rogerson <tonyrogerson_at_sqlserverfaq.com> wrote:

> I'll answer that question with another question - do you want it accurate? 
> In which case you'll need to serialise anyway.
>

> Locking will be there anyway because of the inserting of the picture row 
> data.
>

No locking is only an issue on /some/ databases. e.g. in read commited isolation level (the default) with two transactions both inserting into the same table neither Oracle nor PostgreSQL lock the table. Both give the correct reply for count(*).

> The update of count is simply modifying 4 bytes on a single row which would 
> be accessed via a unique index (very little IO), do you really think the 
> contention will be with the update?
>

The contention will be because locking will be mandatory in the trigger since in order for the count to be correct the transactions will *have* to be serialised. IO has *nothing* to do with it. If you have to serialize you take a performance hit on concurrent transactions, no two ways about it.

> By doing COUNT(*) and serialising the query will cause considerable 
> contention because the COUNT(*) query will be a lot longer.
>

As I said before, in PostgreSQL in transaction isolation level serializable you you don't have contention. I just tried the following experiment:

open two sessions s1 & s2

s1: create table s (id int4 primary key);
s1: set session characteristics as transaction isolation level serializable;
s2: set session characteristics as transaction isolation level serializable;
s1: begin work;
s2: begin work;
s1: insert into s values(1);
s1: select * from s;

     1
(1 row)

s2: select * from s;
 id

     0
(1 row)

s2: insert into s values(2);
s2: select * from s;
 id

     1
(1 row)

s1: select * from s;
 id

     2
(1 row)

s2: commit;
s1: select * from s;
 id

     2
(1 row)

s1: commit;
s1: select * from s;
 id

     3
(1 row)

No locks, no contention (mind you I was careful not to insert using the same primary key - if I had done so then I would have had the following:

s1: begin work;
s2: begin work;
s1: insert into s values(4);

Lefty Kreouzis Received on Sat Apr 01 2006 - 23:41:13 CEST