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 -----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 1000 / 1000 > -------------- > ROW OPERATIONS > -------------- > InnoDB main thread state: sleeping > Number of rows inserted 160922, updated 176104, deleted 12104, read 8758211 > 187.34 inserts/s, 205.01 updates/s, 14.09 deletes/s, 10195.82 reads/s > ---------------------------- > END OF INNODB MONITOR OUTPUT > ============================ > > > > --------------------------------------------------------------------- > 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 --------------------------------------------------------------------- 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
