Skip site navigation (1) Skip section navigation (2)

Re: Transaction Questions

From: William Yu <wyu(at)talisys(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Transaction Questions
Date: 2005-08-25 11:42:49
Message-ID: dekarq$1hmc$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-novice
Here's a possible solution that would maintain concurrency. It would 
involve breaking down your booking periods into discrete units. Create a 
table w/ unique constraint based on the discrete time block. So instead 
of inserting a single start/end record, you would insert a record for 
each unit inside timeframe.

Example, let's say the minimum booking period is 1 hour and somebody 
wants the timeframe between 8/25/05 10PM to 8/26/05 9PM. So you would 
have a loop that attempts to insert 8/25/05 10PM, 8/25/05 11PM, ..., 
8/26/05 7PM, 8/26/05 8PM. With an unique constraint on DATE+TIME_BLOCK, 
an unavailable period would return a duplicate key error -- which if you 
detect, you'd break out of your loop. Put this loop inside a transaction 
and the entire job will just rollback so the booked time units up to 
that point would auto-unbook themselves.

You are getting concurrency in exchange for doing more work so the point 
where the extra records decreases performance more than locking depends 
on how many period units within the average start/end timeframe.



neil(dot)saunders(at)accenture(dot)com wrote:
> Hi,
>  
> 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?
>  
> 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?
>  
> Just trying to get my head around concurrency - Have a good weekend!

In response to

Responses

  • Copy Failure at 2005-08-25 18:46:13 from operationsengineer1

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-08-25 18:46:13
Subject: Copy Failure
Previous:From: Stephan SzaboDate: 2005-08-24 17:15:50
Subject: Re: SQL problem?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group