Thread: Savepoint and prepared transactions
Hi,
I'm working on a solution that utilizes 2 phase commit protocol (between SQL Server and PostgreSQL). Normally PostgreSQL statements sequense is:
1. START
2. <inserts, updates, etc.>
3. PREPARE TRANSACTION 'uuid'
4. COMMIT PREPARED 'uuid'
What if on step 2 user application issues statements with SAVEPOINTs, e.g.
2.1. SAVEPOINT svp1
2.2. <inserts, updates, etc.>
2.3. SAVEPOINT svp2
2.4. <inserts, updates, etc.>
2.5. RELEASE SAVEPOINT svp2
2.6. ROLLBACK TO SAVEPOINT svp1
2.7. <inserts, updates, etc.>
Is this allowed and safe to use?
Thanks,
Konstantin
On Tue, May 4, 2010 at 11:52 PM, Konstantin Izmailov <pgfizm@gmail.com> wrote: > Hi, > I'm working on a solution that utilizes 2 phase commit protocol (between SQL > Server and PostgreSQL). Normally PostgreSQL statements sequense is: > 1. START > 2. <inserts, updates, etc.> > 3. PREPARE TRANSACTION 'uuid' > 4. COMMIT PREPARED 'uuid' > > What if on step 2 user application issues statements with SAVEPOINTs, e.g. > 2.1. SAVEPOINT svp1 > 2.2. <inserts, updates, etc.> > 2.3. SAVEPOINT svp2 > 2.4. <inserts, updates, etc.> > 2.5. RELEASE SAVEPOINT svp2 > 2.6. ROLLBACK TO SAVEPOINT svp1 > 2.7. <inserts, updates, etc.> > > Is this allowed and safe to use? What are you expecting to happen?
I expect that only changes on step 2.7 persisted in DB.
On Tue, May 4, 2010 at 11:18 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
What are you expecting to happen?On Tue, May 4, 2010 at 11:52 PM, Konstantin Izmailov <pgfizm@gmail.com> wrote:
> Hi,
> I'm working on a solution that utilizes 2 phase commit protocol (between SQL
> Server and PostgreSQL). Normally PostgreSQL statements sequense is:
> 1. START
> 2. <inserts, updates, etc.>
> 3. PREPARE TRANSACTION 'uuid'
> 4. COMMIT PREPARED 'uuid'
>
> What if on step 2 user application issues statements with SAVEPOINTs, e.g.
> 2.1. SAVEPOINT svp1
> 2.2. <inserts, updates, etc.>
> 2.3. SAVEPOINT svp2
> 2.4. <inserts, updates, etc.>
> 2.5. RELEASE SAVEPOINT svp2
> 2.6. ROLLBACK TO SAVEPOINT svp1
> 2.7. <inserts, updates, etc.>
>
> Is this allowed and safe to use?
Should work. I'm not sure 2.5 release savepoint is necessary. On Wed, May 5, 2010 at 12:53 AM, Konstantin Izmailov <pgfizm@gmail.com> wrote: > I expect that only changes on step 2.7 persisted in DB. > > On Tue, May 4, 2010 at 11:18 PM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> On Tue, May 4, 2010 at 11:52 PM, Konstantin Izmailov <pgfizm@gmail.com> >> wrote: >> > Hi, >> > I'm working on a solution that utilizes 2 phase commit protocol (between >> > SQL >> > Server and PostgreSQL). Normally PostgreSQL statements sequense is: >> > 1. START >> > 2. <inserts, updates, etc.> >> > 3. PREPARE TRANSACTION 'uuid' >> > 4. COMMIT PREPARED 'uuid' >> > >> > What if on step 2 user application issues statements with SAVEPOINTs, >> > e.g. >> > 2.1. SAVEPOINT svp1 >> > 2.2. <inserts, updates, etc.> >> > 2.3. SAVEPOINT svp2 >> > 2.4. <inserts, updates, etc.> >> > 2.5. RELEASE SAVEPOINT svp2 >> > 2.6. ROLLBACK TO SAVEPOINT svp1 >> > 2.7. <inserts, updates, etc.> >> > >> > Is this allowed and safe to use? >> >> What are you expecting to happen? > > -- When fascism comes to America, it will be intolerance sold as diversity.
yep, thank you!
On Wed, May 5, 2010 at 5:47 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
Should work. I'm not sure 2.5 release savepoint is necessary.--
On Wed, May 5, 2010 at 12:53 AM, Konstantin Izmailov <pgfizm@gmail.com> wrote:
> I expect that only changes on step 2.7 persisted in DB.
>
> On Tue, May 4, 2010 at 11:18 PM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Tue, May 4, 2010 at 11:52 PM, Konstantin Izmailov <pgfizm@gmail.com>
>> wrote:
>> > Hi,
>> > I'm working on a solution that utilizes 2 phase commit protocol (between
>> > SQL
>> > Server and PostgreSQL). Normally PostgreSQL statements sequense is:
>> > 1. START
>> > 2. <inserts, updates, etc.>
>> > 3. PREPARE TRANSACTION 'uuid'
>> > 4. COMMIT PREPARED 'uuid'
>> >
>> > What if on step 2 user application issues statements with SAVEPOINTs,
>> > e.g.
>> > 2.1. SAVEPOINT svp1
>> > 2.2. <inserts, updates, etc.>
>> > 2.3. SAVEPOINT svp2
>> > 2.4. <inserts, updates, etc.>
>> > 2.5. RELEASE SAVEPOINT svp2
>> > 2.6. ROLLBACK TO SAVEPOINT svp1
>> > 2.7. <inserts, updates, etc.>
>> >
>> > Is this allowed and safe to use?
>>
>> What are you expecting to happen?
>
>
When fascism comes to America, it will be intolerance sold as diversity.