Locked rows

[Jan Bohuš]

Hello,
I have problem with the DB locking. I'm stuck with this for 2 days,
I've rewritted it twice, so this mailing list is my last hope :).

I have JPA model called Log. Then, class like this:

public class MyPrimitive implements Serializable {
public void SendRequest(Log a) {
a.lastTry = new Date();
a.save();
this.apcommandlog = a.id;
RabbitMQPublisher.publish("apirequest",
a.deliverykey.name(), this);
}
}

This class is called from controller, like this:
myprimitive = new MyPrimitive();
Log a = new Log();
a.event = "test";
myprimitive.SendRequest(a)

This creates new record in Log model and sends the RabbitMQ request.
In the request, there is ID of the new row in Log table.

And then, there is async job, which collects the reply from RabbitMQ:

@OnApplicationStart(async = true)
public class ReplyMqConsumer extends
RabbitMQConsumer<MyReplyPrimitive> {
@Override
protected void consume(MyReplyPrimitive message) {
Log a = Log.findById(message.getLog());
if(a!=null) {
this.processReply(message, apcl);
}
}
}

And the deal is, that I cannot access/edit the original Log row - the
row is locked.

I don't know why, I can't see anything in mySQL PROCESSES. I just
save()d the model and in the consume(), the row is still locked (the
a.id equals to message.getLog()).

Even if I wait few minutes and try to update the row using my own
mySQL query, the row is still locked. What am I missing? Please, could
you help me? Thank you.

Jan Bohus

[canavar]

Hi,

I assume that you are using InnoDB.

I have not used RabbitMQ. Is the publish method async? Does it really return and the transaction can be committed succesfully? Could you confirm the log is saved and the transaction is committed by using a third party tool(i.e mysql workbench)?

04 Eki 2011 00:03 tarihinde "Jan Bohuš" <jedna...@gmail.com> yazdı:

> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>

[Jan Bohuš]

Yes, the INNODB is correct, mySQL 5.5.16. The publish method just
starts a job - doJobWithResult.

The this.apcommandlog = a.id; returns row ID every time. And this
rows exists, I can confirm it using phpMyAdmin.

I set a breakpoint on the "Log a = Log.findById(message.getLog());"
and I recognized, there are
two cases:
1) - message.getLog() returns numeric value - row with this ID is in
mySQL and is unlocked (editable using phpMyAdmin)
- Log a == null - it doesn't find the row which is editable using
phpMyAdmin

2) Log a = Log.findById(message.getLog()); succeed (a = Log.class).
But even then, update of this record failed (well. it silently fails
without any error, probably waiting for lock release). In this case, I
cannot edit the row even using phpMyAdmin (but I see it).

I checked the processes in phpMyAdmin and the Play connections were in
Sleep state, this means, transactions are commited, right? I even
tried Thread.sleep(100); before calling the .findById(), but without
any result.

On Oct 3, 11:39 pm, canavar <fehmican.sag...@gmail.com> wrote:
> Hi,
>
> I assume that you are using InnoDB.
>
> I have not used RabbitMQ. Is the publish method async? Does it really return
> and the transaction can be committed succesfully? Could you confirm the log
> is saved and the transaction is committed by using a third party tool(i.e
> mysql workbench)?

>  04 Eki 2011 00:03 tarihinde "Jan Bohuš" <jednaba...@gmail.com> yazdı:

[canavar]

Interesting. Have you tried MyISAM? If it works we could say that the cause of the problem is uncommitted transactions.

AFAIK alter table engine=MyISAM query is enough for this.

04 Eki 2011 01:55 tarihinde "Jan Bohuš" <jedna...@gmail.com> yazdı:

[Jan Bohuš]

Well, good idea. It works like charm, with MyISAM. Thank you! If
nothing else came up, I can live with MyISAM on this table. But it
would be nice to find out, what's wrong with transactions ;).

On Oct 4, 1:09 am, canavar <fehmican.sag...@gmail.com> wrote:
> Interesting. Have you tried MyISAM? If it works we could say that the cause
> of the problem is uncommitted transactions.
>
> AFAIK alter table engine=MyISAM query is enough for this.

> 04 Eki 2011 01:55 tarihinde "Jan Bohuš" <jednaba...@gmail.com> yazdı:

[canavar]

Hard to say. What type does message.getLog() return? Is it correctly casted to Long?

But the main problem seems to be concurrency. What if you wait for 10seconds before findById? Thread.sleep(10000)?

04 Eki 2011 02:24 tarihinde "Jan Bohuš" <jedna...@gmail.com> yazdı:

[Alison Winters]

If you have a transaction that's not committing it's probably a code
error not a database error. You could try running "show innodb status"
to confirm that there is at least one long transaction underway. You
could also do a Java thread dump (kill -QUIT pid) to investigate
whether you have any worker threads stuck in a loop somewhere. Try to
isolate all other activity on your server so the only active thread
will be the stuck one.

Another approach might be to use the c3p0 timeout setting to
automatically close your DB connection if it's idle for a certain
number of seconds. I think there is a debug setting together with that
that will print a stack trace of where the connection was opened. It
might not be super-useful in Play where all the connection handling is
in the job API, but you never know.

Hope that helps,
Alison

[Jan Bohuš]

I have a theory - regarding to the Play docs, the transactions are
managed in controllers - if the controller generates the exception,
the rollback is started, otherwise commit. But what about asynchronous
jobs? Are there the transactions managed too?

Because if I added this after my save(), it's working:
JPA.em().getTransaction().commit();
JPA.em().getTransaction().begin();
JPA.em().flush();
JPA.em().clear();

I just don't know if is it just some "hack", or correct solution. And
another thing, the DB connection in the async job dies after a while
(the job is waiting most of the time) and I become this error:
01:55:11,172 WARN ~ SQL Error: 0, SQLState: 08003
01:55:11,172 ERROR ~ No operations allowed after connection
closed.Connection was implicitly closed by the driver.
01:55:11,176 ERROR ~ Error processing message
(ext.ReplyMqConsumer@622d795d) with consumer (jobs.ReplyMqConsumer).
Exception:
javax.persistence.PersistenceException:
org.hibernate.exception.JDBCConnectionException: could not load an
entity: [models.device.Log#12]

Again, the controllers are working, this problem is only with my async
job. I didn't find any section about this in Play docs, but isn't my
main problem the fact, that my job is in asynchronous mode?

> > > > without any error, probably waiting forlockrelease). In this case, I

[Alison Winters]

There are definitely transactions opened/closed in jobs - see
JPAPlugin.beforeInvocation(). I guess it's possible that this is
happening:

1. open transaction on HTTP thread
2. save() on HTTP thread
3. open transaction on job thread
4. select() on job thread
5. commit on HTTP thread
6. commit on job thread (commits nothing)

In this scenario the select in 4. will not see the save in 2. until
the commit in 5., so you will get no results. It might make sense to
explicitly commit on the HTTP thread before kicking off the job,
though I would hope you could do that just by calling commit() and not
having to do another begin/flush/clear. This would explain the missing
row problem, but it doesn't explain the locked row problem you talked
about in your original mail.

Your connection timeout issue is possibly related to the locked row.
If you have a background job that's supposed to run for infinity you
should open it without a transaction so you don't consume a database
connection and leave things uncommitted (this could lock a row forever
if you are doing an update). I think there is a way to do this in Play
by annotating your job with @NoTransaction. Then you will have to open/
close connections around each DB access yourself (or spawn them off in
a new Play-managed job). I don't think it's documented in the Jobs
section of the documentation, though, just the Controllers section.

Alison

[Jan Bohuš]

Thank you Alison, very helpful! I decorated the infinite job with
@NoTransaction and created seperate job for DB manipulations. This
works completely without any additional commands like
JPA.em().getTransaction().commit();. It looks, the problem is
solved :).

On 9 říj, 21:14, Alison Winters <alisonatw...@gmail.com> wrote:
> There are definitely transactions opened/closed in jobs - see
> JPAPlugin.beforeInvocation(). I guess it's possible that this is
> happening:
>
> 1. open transaction on HTTP thread
> 2. save() on HTTP thread
> 3. open transaction on job thread
> 4. select() on job thread
> 5. commit on HTTP thread
> 6. commit on job thread (commits nothing)
>
> In this scenario the select in 4. will not see the save in 2. until
> the commit in 5., so you will get no results. It might make sense to
> explicitly commit on the HTTP thread before kicking off the job,
> though I would hope you could do that just by calling commit() and not
> having to do another begin/flush/clear. This would explain the missing

> row problem, but it doesn't explain thelockedrow problem you talked

> > > > > >> > mySQL query, the row is stilllocked. What am I missing? Please, could