> > being able to read an existing row that another process has an exclusive
> > lock on.
> Not a problem. At this stage everything is relatively simple.
> The table the user is writing to is just:
> table1 (prim integer primary key,
> sec integer);
> The table that the trigger is writing to is:
> audittable (user_id varchar(128) not null,
> utcdate timestamp not null,
> sql_statement varchar(8000) not null,
> primary key(user_id, utcdate));
> table1 has the update trigger on it, so that when a user updates the
> table the trigger inserts a row in to the audittable, recording the
> user, date/time and a string of information.
> So, user1 begins a transaction, updates table1, audittable gets
> written to (row n), but as user1 has not issued a commit statement
> neither the change to table1 nor the insert to the audittable have
> been committed. I would still expect user2 to be able to read rows 1
> ... n-1 (that have been committed previously) from the audittable and
> only be able to read row n when user1 has issued a commit statement.
> user1: begin;
> update table1 set sec = 4 where prim = 1;
> -- table1 is updated and a trigger is fired which does:
> -- insert into audittable ('user1', '23-jul-03 09:34...', 'user1 has
> done something!');
> user2: select * from audittable;
> -- user2's application sits and waits ... I would like user2 to see
> everything that's been committed except the row above
> user1: commit
> -- user2's application now returns all rows from audittable
> end of SQL]
> I can't see why user1 would need locks on any other rows in audittable
> other than the one the trigger has inserted. I most definitely don't
> want user2 to be able to do dirty reads on the audittable either.
> I hope this example makes things clearer!
> TIA (again!)