Re: Locking that will delayed a SELECT

Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
>> The problem is solved
>>
>> a) Using SERIALIZABLE XACTION ISOLATION LEVEL
>> b) in T2 using "select for update" instead of select. That way T2's
>> queries will wait untill T1's statements commit or rollback.

ISTM that SERIALIZABLE mode will not solve this problem, since by
definition you want T2 to see results committed after T2 has started.

A simple answer is to have T1 grab an ACCESS EXCLUSIVE lock on some
table to block T2's progress. If that locks out third-party
transactions that you'd rather would go through, you can probably use
a lesser form of lock --- but then both T1 and T2 will have to cooperate
since each will need to explicitly take a lock.

I gave a presentation at the O'Reilly conference this year that covered
some of these issues. Looks like you can still get the slides from
http://conferences.oreillynet.com/cs/os2002/view/e_sess/2681

regards, tom lane