I tried the lock monitor, and the info it produced seemed to be impossible
to correlate with the actual data operations. On top of that, I run it as a
service, so I have to uninstall the service just to do use the lock monitor,
so hesitate to run it. If you think you can actually make sense out of its
output, let me know, and I'll do it again and e-mail it.
I have had similar problems using SQL Server and Microsoft's JDBC driver,
but was able to get rid of them by setting the transaction isolation to
TRANSACTION_READ_UNCOMMITTED. This didn't help with MySQL/JDBC, though. I
tried all isolation levels to no avail.
Here is the pattern... The JSP reads all the rows in the table. I can then
successfully update individual rows. With each update it rereads all rows
again. Even if I don't update any rows, it still deadlocks when I then try
to create a new row. The only time it doesn't deadlock is when I restart
everything, and then create before I read anything.
Keep in mind that all of this is managed via EJBs and the container, and I
am using connection pooling. This means that this activity may occur over
several connections.
Considering that I lead three systems over the past two years using
VB/COM/ADO and Oracle and SQL Server without a single deadlock, even though
they all use a lot of transactions, this is not encouraging. I need to be
able to deliver quickly, yet I'm stuck on my first EJB because I insist on
using open source. PLEASE HELP!
JDBC Driver: mm.mysql-2.0.6.1.jar
MySQL: mysql-max-3.23.42-win
Thanks,
Erik
-----Original Message-----
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 19, 2001 3:11 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: innodb problem (with JDBC/transactions)
Erik,
run the MySQL server mysqld from a command prompt and do with
the mysql client:
mysql>create table innodb_lock_monitor(a int) type = innodb;
(assuming you run a recent version).
Then mysqld will print lock information to the standard output
and you see what is happening.
Regards,
Heikki
http://www.innodb.com/ibman.html
>Has anyone encountered locking problems using JDBC with InnoDB tables and
>transactions? I am wondering if the MySQL JDBC was designed to handle
>transactions properly since InnoDB is a relatively recent addition to
MySQL.
>The problem I am running into is the 1000000 locking error, as if the
>previous transaction hasn't committed.
>
>What complicates this is that I am using Bean Managed Persistance (BMP)
with
>container managed transactions with JBoss as my EJB container. This means
>that I don't actually write the transaction code, so it's hard to tell
>exactly what's going on. A transaction is generally defined by the
>container as each method call that the client makes.
>
>I have the transaction option set to "Required" for all the bean's methods,
>and the 100000 error occurs when the client makes a second call for the
same
>table row. An example would be creating a row, then trying to make a
second
>method call to recreate the same row (resubmitting the JSP page.) This
>should return a duplicate row error, but instead produces a locking error,
>which the client never sees (shows up on JBoss log.) This makes it seam as
>though the lock from the original transaction wasn't released, and perhaps
>the database did not even receive or process the COMMIT.
>
>Is anyone having similar problems, and hopefully found a solution?
>Thanks,
>Erik
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
