RFE: Add session id to log

[Gili]

Hi,

I have the maximum logging level enabled and dumping the output to file using slf4j. A few minutes ago I got this deadlock:

org.h2.jdbc.JdbcSQLException: Deadlock detected. The current transaction was rolled back. Details: "

Session #7 (user: SA) is waiting to lock PUBLIC.COMPANIES while locking PUBLIC.COMPANIES (shared), PUBLIC.PERMISSIONS (exclusive), PUBLIC.PERMISSIONS_CLOSURE (exclusive).

Session #6 (user: SA) is waiting to lock PUBLIC.PERMISSIONS while locking PUBLIC.COMPANIES (shared)."; SQL statement:

I'd like to reverse engineer what led to this deadlock so I tried stepping back through the log looking for what Session #6 and #7 did recently. Unfortunately, H2 never seems to indicate the current session ID so I have no way of linking each database thread to a session ID and no way of knowing which ones correspond to Session #6 and #7.

Is it possible to add this information to the log? At the very least, I need H2 to declare "The current thread corresponds to session #X" when creating the session initially. That way I can track the thread id to the session id for the rest of its lifetime.

Thanks,

Gili

[Noel Grandin]

Look at:
http://h2database.com/html/functions.html#session_id

If you give some examples of what the log contains and where you'd like
to see session ID's, I'll see what I can do.

[Thomas Mueller]

Hi,

Well, you should know what statement was executed in the current session when the exception occurred, so you should be able to understand the message.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[cowwoc]

Hi Thomas,

    I already understand what the current session was doing because H2 provides a stack-trace (the deadlock causes an exception to be thrown). What's not clear is what the *other* session was doing.

    I am aware of SESSION_ID(). I used to think you should log this value when a new database connection is opened, but I just realized connection pools kill that approach ;)

    Probably the easiest solution is to replace "Session <id>" in the deadlock message with "Thread <name>". That way I'd know what threads were involved with the deadlock and be able to walk back up the log. Is that possible?

Thanks,
Gili

On 23/08/2013 11:02 AM, Thomas Mueller wrote:

Hi,

Well, you should know what statement was executed in the current session when the exception occurred, so you should be able to understand the message.

Regards,

Thomas

On Fri, Aug 23, 2013 at 9:09 AM, Noel Grandin <noelg...@gmail.com> wrote:

Look at:
http://h2database.com/html/functions.html#session_id

If you give some examples of what the log contains and where you'd like to see session ID's, I'll see what I can do.

On 2013-08-23 00:15, Gili wrote:

Hi,

I have the maximum logging level enabled and dumping the output to file using slf4j. A few minutes ago I got this deadlock:

org.h2.jdbc.JdbcSQLException: Deadlock detected. The current transaction was rolled back. Details: "
Session #7 (user: SA) is waiting to lock PUBLIC.COMPANIES while locking PUBLIC.COMPANIES (shared), PUBLIC.PERMISSIONS (exclusive), PUBLIC.PERMISSIONS_CLOSURE (exclusive).
Session #6 (user: SA) is waiting to lock PUBLIC.PERMISSIONS while locking PUBLIC.COMPANIES (shared)."; SQL statement:

I'd like to reverse engineer what led to this deadlock so I tried stepping back through the log looking for what Session #6 and #7 did recently. Unfortunately, H2 never seems to indicate the current session ID so I have no way of linking each database thread to a session ID and no way of knowing which ones correspond to Session #6 and #7.

Is it possible to add this information to the log? At the very least, I need H2 to declare "The current thread corresponds to session #X" when creating the session initially. That way I can track the thread id to the session id for the rest of its lifetime.

Thanks,
Gili

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/Q6TBVIHW31M/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

Thread name: I'm not sure if that's possible, but it sounds like a good idea.

Regards,

Thomas

[cowwoc]

Hi Thomas,

    I'm glad that you agree. Do you need anything further from my end, or will you get back to me on this issue?

Thanks,
Gili

[Thomas Mueller]

Hi,

I'm not sure when exactly I have time; for me personally it's not such an urgent issue. If you (or somebody else) could provide a patch that would be great!

Regards,

Thomas

[cowwoc]

Hi Thomas,

    No problem. I filed https://code.google.com/p/h2database/issues/detail?id=506 for now so we can refer to this later.

Thanks,
Gili