Erik, please send me the lock monitor output. Version 3.23.44 has a better monitor where it is easier to correlate who locked what. But the way to proceed with an older lock monitor is to run individual SQL statements and look what they lock. In a single user environment all locks should be due to the operations of the single user.
At 12:56 AM 10/28/01 -0400, you wrote: >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, I think you can just go the the NT control panel, the Services icon, and manually disable the mysqld service. Then go to the bin directory and do ...>mysqld-max --console When doing debugging it is always advisable to run mysqld from the MS-DOS prompt. Then you see what is happening. >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. That suggests it produced deadlocks also on MS SQL Server. Setting to 'read uncommitted' reduces locking and prevents some deadlocks. But the consistency of transactions is then compromised. >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. Does all this happen in a single transaction within a single connection? Do you call COMMIT in between? Are there several concurrent users? What is an exact sequence of operations which leads to a deadlock? Does it do a SELECT ... FOR UPDATE? These questions would be answered by looking at the lock monitor output. Why do you read the rows several times? If you read the rows and use a locking read (...FOR UPDATE or ...LOCK IN SHARE MODE), then no one else can change them in the meantime until you call COMMIT. If you insert new rows, bear in mind InnoDB does next-key locking to prevent phantoms: reading all the rows with a locking read also prevents all inserts by others to the result set. This is different from Oracle which does not prevent phantoms. See the manual at http://www.innodb.com/ibman.html >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. Are there concurrent operations? A deadlock should only happen if there are at least 2 users, or if the client is badly written and it divides the work of a single user to two 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! The solution is to analyze the locking behavior of your application. What it locks at what phase, and when does it call COMMIT to release the locks. > JDBC Driver: mm.mysql-2.0.6.1.jar > MySQL: mysql-max-3.23.42-win > >Thanks, > >Erik Regards, Heikki http://www.innodb.com >-----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
