RE: innodb problem (with JDBC/transactions)
Hi, We are currently facing the same problem (Deadlock found when trying to get lock; Try restarting transaction) in our production environnement. We are using InnoDB tables (mysqk 3.23.48-max) with Jboss 2.4.4 and JDBC driver mm.mysql-2.0.11-bin.jar / RedHat 7.1. Could you please tell me how to fix this ? Thanks, jb - 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
RE: innodb problem (with JDBC/transactions)
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: mysqlcreate 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 100 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 10 error occurs when the client makes a second call for the same
Re: innodb problem (with JDBC/transactions)
Erik, run the MySQL server mysqld from a command prompt and do with the mysql client: mysqlcreate 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 100 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 10 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
RE: innodb problem (with JDBC/transactions)
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 100 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 10 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 -Original Message- From: jean-philippe [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 10, 2001 4:04 PM To: [EMAIL PROTECTED] Subject: Re: innodb problem Nope, it's not the SHOW TABLE STATUS that return the error but the java program that is doing the insert. For the ouput of the innodb monitor i'll see it tomorrow when i'll be back to the office. Thanks Jean-Philippe - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 10, 2001 7:34 PM Subject: Re: innodb problem Hi! Thanks for your help, but starting the innodb_lock_monitor with mysql create table innodb_lock_monitor(a int) type = innodb; doesn't output anything to the standard output. It makes the MySQL server mysqld to output to the standard output, not the client. Start your server from a command prompt. I have pasted below a sample output. But here is the new message i get with innodb .43b. It occurs when i do a SHOW TABLE STATUS during an insert with JDBC : SQLException: General error: Deadlock found when trying to get lock; Try restarting transaction Does the command SHOW TABLE STATUS return that error? Or some other SQL statement? I tried SHOW TABLE STATUS while a test program was running, and it returned the table list. Note: i'm just beginning to test innodb tables to use in place of MyISAM, everything seems to work ok except with this SQL statement that generate a deadlock if run it during an INSERT / UPDATE --Jean-Philippe Vignolo http://www.phonevalley.com A bus station is where buses stop. A train station is where trains stop. On my desk there is a work station Regards, Heikki = 011010 20:17:31 INNODB MONITOR OUTPUT = -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 3295, signal count 3295 Mutex spin waits 69407, rounds 68164, OS waits 155 RW-shared spins 6708, OS waits 2862; RW-excl spins 2800, OS waits 247 TRANSACTIONS Purge done for all trx's with n:o 0 1100739, undo n:o 0 0 Total number of lock structs in row lock hash table 0 ---TRANSACTION 0 1028205, OS thread id 10251, not started, runs or sleeps MySQL thread id 5, query id 93669 localhost heikki ---TRANSACTION 0 1038196, OS thread id 9226, not started, runs or sleeps MySQL thread id 4, query id 163875 localhost heikki FILE I/O I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 271 OS file reads, 1247 OS file writes, 497 OS fsyncs 0.32 reads/s, 1.45 writes/s, 0.58 fsyncs/s - INSERT BUFFER - Ibuf for space 0: size 1, free list len 5, seg size 7, 0 inserts, 0 merged recs, 0 merges --- LOG --- Log sequence number 1 1223862705 Log flushed up to 1 1223861076 Last checkpoint at 1 1223861076 0 pending log writes, 0 pending chkp writes 341 log i/o's done, 0.40 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 47466284; in additional pool allocated 233056 Free list length 49 LRU list length 1664 Flush list length 9 Buffer pool size 1792 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 238, created 2317, written 4229 0.28 reads/s, 2.70 creates/s, 4.92 writes/s Buffer pool hit rate
