Re: Transaction Questions

Neil,

> Following on from my last question, I'm attempting to write a function that
> inserts periods. Periods are either bookings, available, or unavailable,
> each stored in a seperate table, and all have start date and end date
> columns, all of which inherit a table 'calendar_entries'.
>
> The function needs to check that there are no overlapping periods, so I
> need to check all three tables - I need to prevent entries being added in
> parrell for the duration of the function. My question is - Is it sufficient
> to LOCK calendar_entries IN EXCLUSIVE MODE, or do I need to lock all three
> tables individually?

If calendar_entries is the first table being checked for all backends, it
would be sufficient to lock it. Personally, I would try to devise a more
elaborate strategy that allowed for some degree of concurrency, but possibly
you don't need that.

> Also, inside the function I need to use a transaction in order to DELETE
> one row, and UPDATE another. Will this 'inner' transaction have write
> access to all 3 tables?

Subtransactions (Savepoints) inherit the properties of their parent
transaction (the function).

--
Josh Berkus
Aglio Database Solutions
San Francisco