rollback the update query

[Simran Singh]

I am really new to Django and I am using Django 1.8. Many of the manual_transaction features have been depreciated in this. I have used @transaction.atomic(None, True) to pack the transaction and rollback the updates if at any point the condition is not met.
I tried to store the transaction in savepoint and used  savepoint_rollback or savepoint_commit as per the condition. But I am having no luck here. No matter where the control goes but as soon as atomic block ends, it is committing the changes in mysql db. 
Any kind of help or leads would be highly appreciated.

[Tom Evans]

On Mon, May 11, 2015 at 11:44 AM, Simran Singh
<er.simra...@gmail.com> wrote:
> I am really new to Django and I am using Django 1.8. Many of the
> manual_transaction features have been depreciated in this. I have used
> @transaction.atomic(None, True)

nit; these are the defaults. You might as well just say:

@transaction.atomic

> to pack the transaction and rollback the
> updates if at any point the condition is not met.
> I tried to store the transaction in savepoint and used savepoint_rollback
> or savepoint_commit as per the condition. But I am having no luck here. No
> matter where the control goes but as soon as atomic block ends, it is
> committing the changes

Show the code. All you have shared so far is that you are using the
API; how you use it matters!

> in mysql db.

What storage engine are you using with mysql? MyISAM is
non-transactional; it accepts the SQL statements for transactions, but
always operates in autocommit mode.

Cheers

Tom

[Simran Singh]

Thanks Tom for your response. I am attaching piece of code of what I have done. I hope this could help.

@transaction.atomic(None, True)
def reservation(request):
   
    if request.method == 'POST':
        reservation_form = ReservationForm(request.POST)
        if reservation_form.is_valid():
            for i in xrange(0, len(exclusive_assets)):

                    inventory = Inventory.objects.filter(asset_id = exclusive_assets[i])
                    asset_spec = AssetSpecification.objects.filter(asset_id_id=inventory,utilized_value=0).values_list('asset_id', flat=True)
                    trans = transaction.savepoint()  //  Here I am trying not to commit the code and store it in transaction.

                    if asset_spec.count():
                        asset_port = AssetPorts.objects.filter(asset = asset_spec,usage='No')

                        for portNumber in asset_port:
                            AssetPorts.objects.filter(port_number = portNumber.port_number).update(usage='Yes')
                            trans = transaction.savepoint()  //  Here I am trying not to commit the code and store it in transaction.

                    else:
                        transaction.savepoint_rollback(trans) // If any time the condition is not met then there should be no update
                       

            transaction.savepoint_commit(trans) // Commit changes if all the conditions are met
            return success(request)


and in settings file, I have done
'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'lab_on_demand_db',
        'USER': 'root',
        'PASSWORD': 'root',
        'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
        'PORT': '3306',
        'AUTOCOMMIT': 'False',
        }

I am using InnoDB engine with mysql.


Thanks,
Simran

[Tom Evans]

I don't understand what you expect this code to do. You make a
savepoint, and if you do nothing to the database, you roll it back.
You only have one statement that modifies your data.

When do you want to rollback, and what do you want it to rollback?

Cheers

Tom

[Simran Singh]

Hi Tom,

If you see then in if block, I am creating my savepoint. At any point of time, if the condition is not met and control goes to else block then I want to rollback all the changes that are saved in transaction and redirect it to some other page and not commit anything to db. I am able to redirect the flow to some template but all the transactions are being committed and rollback functionality is not working.

Thanks,
Simran

On Monday, May 11, 2015 at 4:27:50 PM UTC+5:30, Simran Singh wrote:

[Tom Evans]

On Wed, May 13, 2015 at 6:36 AM, Simran Singh
<er.simra...@gmail.com> wrote:
> Hi Tom,
>
> If you see then in if block, I am creating my savepoint. At any point of
> time, if the condition is not met and control goes to else block then I want
> to rollback all the changes that are saved in transaction and redirect it to
> some other page and not commit anything to db. I am able to redirect the
> flow to some template but all the transactions are being committed and
> rollback functionality is not working.
>

Then you don't need savepoints, just abort the transaction by raising
an exception. Eg:


class UnexpectedNoAssetSpecificationException(Exception):
pass

class ReservationForm(...):
...
def process(self):
# asset_port and exclusive_assets are undefined in your sample code
# globals are bad
for asset in exclusive_assets:
inventory = Inventory.objects.filter(asset_id=asset)
asset_spec = AssetSpecification.objects.filter(
asset_id_id=inventory,
utilized_value=0).values_list('asset_id', flat=True)
if not assec_spec.count():
raise UnexpectedNoAssetSpecificationException()
port_numbers = [ port.port_number for port in asset_port ]
AssetPorts.objects.filter(
port_number__in=port_numbers).update(usage='Yes')

@transaction.atomic
def view(request):
if request.method == 'POST':
form = ReservationForm(request.POST)
if form.is_valid():
try:
with transaction.atomic():
form.process()
except UnexpectedNoAssetSpecificationException:
return error(request)
return success(request)
form = ReservationForm()
return render(request, 'reservation.html', { 'form': form, })

I'm interested what is considered "valid" in your form, given this
form processing code doesn't seem to access anything from the form.

Cheers

Tom

[Tom Evans]

On Wed, May 13, 2015 at 9:37 AM, Simran Singh
<er.simra...@gmail.com> wrote:
> Tom, basically I want to discard everything that is stored in
> transaction.savepoint. As per my current case, it is getting committed no
> matter where the flow goes.

Yes, so you don't want savepoints. Savepoints are for rolling back a
*subset* of the uncommitted transaction, and you want to rollback the
whole transaction, which simplifies things.

Did you read the code I posted? Wrap a block in atomic() and raise an
exception if you want it to be rolled back, handling the exception
outside of the atomic().

Cheers

Tom

[James Schneider]

>                     asset_spec = AssetSpecification.objects.filter(asset_id_id=inventory,utilized_value=0).values_list('asset_id', flat=True)
>                     trans = transaction.savepoint()  //  Here I am trying not to commit the code and store it in transaction.
>
>                     if asset_spec.count():
>                         asset_port = AssetPorts.objects.filter(asset = asset_spec,usage='No')

Why are you setting save points when all you are doing is running select queries?

I agree with Tom on all his points. Going back to the OP code though:

You have this:

>
>                         for portNumber in asset_port:
>                             AssetPorts.objects.filter(port_number = portNumber.port_number).update(usage='Yes')
>                             trans = transaction.savepoint()  //  Here I am trying not to commit the code and store it in transaction.
>

Wouldn't you want this instead?

for portNumber in asset_port:

    trans = transaction.savepoint()  //  Here I am trying not to commit the code and store it in transaction.

    AssetPorts.objects.filter(port_number = portNumber.port_number).update(usage='Yes')

You are making a save point *after* the update() operation, meaning that when you roll back later, you're effectively not doing anything because the update() would be part of the previous save point, not the latest one you just stored.

Per the docs: https://docs.djangoproject.com/en/1.8/topics/db/transactions/#django.db.transaction.clean_savepoints

Check out the example just below the section I linked.

You also haven't been explicit about whether or not you only want to roll back the save point during a single iteration of the for loop, or if you want to roll back *all* of the changes that would be implemented by the for loop (or rather, any changes made anywhere in the view).

Apologies if the formatting is off, doing this on my phone.

-James

[Simran Singh]

Hi Tom,

I tried the code that you posted but somehow it is giving UnexpectedNoAssetSpecificationException at /reservation/ error. I am not sure but when this exception is raised, then control is not going to
class UnexpectedNoAssetSpecificationException(Exception):
    pass

and when it goes into except, it gives above error message.

Regards,

 Simran

On Monday, May 11, 2015 at 4:27:50 PM UTC+5:30, Simran Singh wrote:

[Simran Singh]

Hi James,

Thanks for your feedback. I basically want to rollback that query where I update the db. Basically I want to rollback all the updates that were made during that transaction. Tried out the method of custom Exception as suggested by Tom but somehow my bad luck that it is still not working and giving error. Lets hope to close this issue soon :(

Regards,
Simran

On Monday, May 11, 2015 at 4:27:50 PM UTC+5:30, Simran Singh wrote:

[James Schneider]

Did you try changing the position of the save point like I mentioned?

-James

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/58a21382-40c5-49b5-b79a-06b289bebd5c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Simran Singh]

Hi James,

Yes, it workedd :)

I created the object outside the method and called it at every transaction point in the loop and when there is rollback, it rollbacks all the queries.
Thanks James

Regards,
Simran

On Monday, May 11, 2015 at 4:27:50 PM UTC+5:30, Simran Singh wrote:

[Simran Singh]

Hi Tom,
Thanks a lot for your efforts. It worked with savepoint called before update query.
Thank you

Regards,
Simran

On Monday, May 11, 2015 at 4:27:50 PM UTC+5:30, Simran Singh wrote: