18 Dec 2010 01:40
Re: No exception raised when hitting database locking error
Hi there,
Thanks, yes if I do commit() (surprisingly the flush() does not help) in every object modification - it works BUT it is painfully slow with postgres. I have a reasonable performance and no problem with MySQL / My ISAM so for now I sticked with it.
As problem shows up in both database server using transaction in this case, I am not sure if it is the database fault (do table level locking rather than row locking) or storm does something in the picture. I would like to remove storm and try to use plainly psycopg2 and MySQLdb to see if it happened but have not time to do it yet.
Another thing is, I expect storm to raise exception in that case which is not the case - it just pass the commit() call and continue the execution as normal.
Many thanks,
On Fri, Dec 17, 2010 at 11:06 PM, Mario Zito <mazito-bCKsg03D/nBBDgjK7y7TUQ@public.gmane.org> wrote:
Hello All,May be completely wrong but the problem may be that the DB (for some reason) is locking the full table and itś not doing row level locking.There is info at http://www.postgresql.org/docs/8.1/static/explicit-locking.html about Postgres locking.I had a similar problem some days ago, using Postgres too, and the same error, but in a different situation (multiple users updating the same object, in different server processes, but NOT at the same time), even when doing a commit at the end of each update transaction.It also happened that different processes showed different versions of the same data (even after the commits) when the data has been changed in a diferent process.I solved it by doing a commit() BEFORE starting all requests (even read requests) to clear Storm caches. After this, everything worked fine.This explained why this solved the problem: http://www.mail-archive.com/storm-33AaDErTWvAAkhNd+W/JZ0EOCMrvLtNR@public.gmane.org/msg00704.html
Maybe this can help you tooMario2010/12/17 james-whk3OXXaEJgXC2x5gXVKYQ@public.gmane.org <james <at> jamesh.id.au>On Thu, Dec 16, 2010 at 3:25 PM, Steve Kieu <msh.computing-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:It isn't unusual for serialisation errors to be reported prior to
>
> Hello all,
>
> It is kind of hard to explain - basically I wrote an application that
> trying to retrieve a set of objects from the db - then opening a config file
> and loop over the lines regex matching and if match, update a field from the
> object. At the end of function - call store.commit()
>
> Run it at the same time in multiple servers to connect to one central
> database (tested with postgre and mysql). The set of object mentioned above
> are a different set (as they run in different servers)
>
> If I use postgres and I often get error - the program quit with an error:
>
> could not serialize access due to concurrent update
>
> I tried to wrap store.commit() in try and except but it never runs into
> except: it just passed the store.commit() as normal and exited.
commit: the database usually reports them as soon as it detects the
problem. The traceback from the error you saw probably tells you
where it occurred. Try wrapping your try/except block around the
entire transaction logic rather than just the commit() call.
If you're using PostgreSQL, the
psycopg2.extensions.TransactionRollbackError exception should cover
the cases you're interested in.I would guess MySQL is pretty much the same: report the error as soon
> The same problem with MySQL with innoDB (different error message though,
> something abt deadlock detected
as the problem is detected rather than letting you continue until
commit.Well MyISAM doesn't support transactions, so you shouldn't expect it
> What surprises me is that why the db server refuses to work. I am positive
> that they are completely different set and then it should be in different
> rows modifications.
>
> And then storm does not throw exception on this case so I can catch using
> except and try again or do something about it.
>
> The problem is fixed if: I call store.commit() after each object value
> update (store.flush() is not enough) ; but it is painfully slow
>
>
>
> MyISAM does not have that problem as well
to report transaction serialisation errors ...
James.
--
storm mailing list
storm <at> lists.canonical.com
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/storm
--
Mario A. Zito
ANALYTE SRL
Parana 457, piso 2, of. 'A'
(C1033AAI) Buenos Aires, Argentina
tel: (54-11) 5258-0205 int 138
mazito-bCKsg03D/nBBDgjK7y7TUQ@public.gmane.org
www.analyte.com
--
Steve Kieu
Ph: +61-7-3367-3241
<div> <p>Hi there,</p> <div><br></div> <div>Thanks, yes if I do commit() (surprisingly the flush() does not help) in every object modification - it works BUT it is painfully slow with postgres. I have a reasonable performance and no problem with MySQL / My ISAM so for now I sticked with it.</div> <div><br></div> <div>As problem shows up in both database server using transaction in this case, I am not sure if it is the database fault (do table level locking rather than row locking) or storm does something in the picture. I would like to remove storm and try to use plainly psycopg2 and MySQLdb to see if it happened but have not time to do it yet.</div> <div><br></div> <div>Another thing is, I expect storm to raise exception in that case which is not the case - it just pass the commit() call and continue the execution as normal.</div> <div><br></div> <div>Many thanks,</div> <div><br></div> <div> <br><br><div class="gmail_quote">On Fri, Dec 17, 2010 at 11:06 PM, Mario Zito <span dir="ltr"><<a href="mailto:mazito <at> analyte.com">mazito@...</a>></span> wrote:<br><blockquote class="gmail_quote"> Hello All, <div><br></div> <div>May be completely wrong but the problem may be that the DB (for some reason) is locking the full table and itś not doing row level locking.</div> <div>There is info at <a href="http://www.postgresql.org/docs/8.1/static/explicit-locking.html" target="_blank">http://www.postgresql.org/docs/8.1/static/explicit-locking.html</a> about Postgres locking.</div> <div><br></div> <div>I had a similar problem some days ago, using Postgres too, and the same error, but in a different situation (multiple users updating the same object, in different server processes, but NOT at the same time), even when doing a commit at the end of each update transaction.</div> <div>It also happened that different processes showed different versions of the same data (even after the commits) when the data has been changed in a diferent process.</div> <div><br></div> <div>I solved it by doing a commit() BEFORE starting all requests (even read requests) to clear Storm caches. After this, everything worked fine.</div> <div>This explained why this solved the problem: <a href="http://www.mail-archive.com/storm-33AaDErTWvAAkhNd+W/JZ0EOCMrvLtNR <at> public.gmane.org/msg00704.html" target="_blank">http://www.mail-archive.com/storm-33AaDErTWvAAkhNd+W/JZ0EOCMrvLtNR <at> public.gmane.org/msg00704.html</a> </div> <div> <br> </div> <div>Maybe this can help you too</div> <div><br></div> <div>Mario</div> <div> <br><br><div class="gmail_quote">2010/12/17 <a href="mailto:james@..." target="_blank">james@...</a> <span dir="ltr"><<a href="mailto:james@..." target="_blank">james <at> jamesh.id.au</a>></span><br><blockquote class="gmail_quote"> <div> <div></div> <div class="h5"> <div>On Thu, Dec 16, 2010 at 3:25 PM, Steve Kieu <<a href="mailto:msh.computing <at> gmail.com" target="_blank">msh.computing@...</a>> wrote:<br> ><br> > Hello all,<br> ><br> > It is kind of hard to explain - basically I wrote an application that<br> > trying to retrieve a set of objects from the db - then opening a config file<br> > and loop over the lines regex matching and if match, update a field from the<br> > object. At the end of function - call store.commit()<br> ><br> > Run it at the same time in multiple servers to connect to one central<br> > database (tested with postgre and mysql). The set of object mentioned above<br> > are a different set (as they run in different servers)<br> ><br> > If I use postgres and I often get error - the program quit with an error:<br> ><br> > could not serialize access due to concurrent update<br> ><br> > I tried to wrap store.commit() in try and except but it never runs into<br> > except: it just passed the store.commit() as normal and exited.<br><br> </div>It isn't unusual for serialisation errors to be reported prior to<br> commit: the database usually reports them as soon as it detects the<br> problem. The traceback from the error you saw probably tells you<br> where it occurred. Try wrapping your try/except block around the<br> entire transaction logic rather than just the commit() call.<br><br> If you're using PostgreSQL, the<br> psycopg2.extensions.TransactionRollbackError exception should cover<br> the cases you're interested in.<br><div> <br><br> > The same problem with MySQL with innoDB (different error message though,<br> > something abt deadlock detected<br><br> </div>I would guess MySQL is pretty much the same: report the error as soon<br> as the problem is detected rather than letting you continue until<br> commit.<br><div> <br><br> > What surprises me is that why the db server refuses to work. I am positive<br> > that they are completely different set and then it should be in different<br> > rows modifications.<br> ><br> > And then storm does not throw exception on this case so I can catch using<br> > except and try again or do something about it.<br> ><br> > The problem is fixed if: I call store.commit() after each object value<br> > update (store.flush() is not enough) ; but it is painfully slow<br> ><br> ><br> ><br> > MyISAM does not have that problem as well<br><br> </div>Well MyISAM doesn't support transactions, so you shouldn't expect it<br> to report transaction serialisation errors ...<br> </div> </div> <div> <div></div> <div> <br> James.<br><br> --<br> storm mailing list<br><a href="mailto:storm@..." target="_blank">storm <at> lists.canonical.com</a><br> Modify settings or unsubscribe at: <a href="https://lists.ubuntu.com/mailman/listinfo/storm" target="_blank">https://lists.ubuntu.com/mailman/listinfo/storm</a><br> </div> </div> </blockquote> </div> <br><br clear="all"><br>-- <br>Mario A. Zito<br>ANALYTE SRL<br>Parana 457, piso 2, of. 'A'<br>(C1033AAI) Buenos Aires, Argentina<br>tel: (54-11) 5258-0205 int 138<br><a href="mailto:mazito@..." target="_blank">mazito@...</a><br><a href="http://www.analyte.com" target="_blank">www.analyte.com</a><br> </div> </blockquote> </div> <br><br clear="all"><br>-- <br>Steve Kieu<div>Ph: +61-7-3367-3241 <br> </div> <div>sip:*<a href="mailto:01161428 <at> sipbroker.com" target="_blank">01161428@...</a> </div> <br> </div> </div>
RSS Feed