innodb rollback 30x slower than commit normal?
We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTED. We use Hibernate and optimistic concurrency, so periodically concurrent write attempts cause app-level Exceptions that trigger rollbacks (and then we retry tx). We've added app-level caching and turned down our tomcat NIO thread count to just 8 (very little contention inside the app) but now we're seeing that rollbacks appear to be up to 30x slower than commits?! Is that normal? Here's a typical TX: Set autocommit=0; Select * from users where name=bob; Update users set visit_count=X where id=bobId and version=Y Commit; Set autocommit=1; When this tx is executed about 100 times/sec, appserver latency is about 10-15 ms per http request (including db time). However, when instead of commit a 'rollback' is issued, the latency spikes to 600-1100 ms (nearly all of that time in appserver appears to be spent waiting on db). So is that expected cost of a rollback? Can anything be done to speed it up? Thanks! -nikita
please help reading DB deadlock notice
Hi, I'd really appreciate help with reading this db deadlock notice. Essentially, I'd like to understand: -what holds the lock that TX2 is waiting on index `token` of table `eviltwin/user_sessions`? I thought it'd be TX1 (hence deadlock ?), yet it appears that TX1 holds no locks -if the answer to the above cannot be found in attached report, is there a setting I should turn on the server to get the necessary info next time this deadlock occurs? Our setup: MySQL 5, InnoDB, Repeatable Read relevant columns in table user_sessions: -id (PK) -token (unique, nullable) -serverSessionId (FK, nullable) TX1 does: -select for update on a given 'token' -set serverSessionId to null TX2 does: -select for update on the same 'token' -delete selected record TX2 seems to start a little earlier, and succeed on 'select for update'. TX1 then tries to do the same and is forced to wait on a lock. This I understand. But why does TX2 need to wait on a lock to deleted already 'selected for update' user_session? Who's holding that lock? thanks, -nikita *** 1. row *** Status: = 070402 12:24:38 INNODB MONITOR OUTPUT = Per second averages calculated from the last 29 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 6313003, signal count 5099409 Mutex spin waits 819355967, rounds 1424470313, OS waits 4424501 RW-shared spins 1000685, OS waits 629116; RW-excl spins 760423, OS waits 175362 LATEST DETECTED DEADLOCK 070402 12:22:41 *** (1) TRANSACTION: TRANSACTION 0 12012950, ACTIVE 0 sec, process no 7328, OS thread id 1161120096 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1216 MySQL thread id 93849, query id 1913636508 lf20.eviltwinstudios.net 192.168.100.163 eviltwin Sending data select usersessio0_.id as id37_, usersessio0_.hibernateVersion as hibernat2_37_, usersessio0_.serverSessionId as serverSe3_37_, usersessio0_.userId as userId37_, usersessio0_.loginTime as loginTime37_, usersessio0_.logoutTime as logoutTime37_, usersessio0_.boot as boot37_, usersessio0_.token as token37_, usersessio0_.macAddressMD5 as macAddre9_37_, usersessio0_.expirationTime as expirat10_37_, usersessio0_.creationDate as creatio11_37_ from user_sessions usersessio0_ where usersessio0_.token='82ff6193-1216-449a-9e33-5426fb8e10ef' for update *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 130062 n bits 144 index `PRIMARY` of table `eviltwin/user_sessions` trx id 0 12012950 lock_mode X locks rec but not gap waiting Record lock, heap no 9 PHYSICAL RECORD: n_fields 13; compact format; info bits 32 0: len 30; hex 37653530373061382d393537362d343363352d623138342d656266633763; asc 7e5070a8-9576-43c5-b184-ebfc7c;...(truncated); 1: len 6; hex 00b74d95; asc M ;; 2: len 7; hex 00803c29ee; asc ) ;; 3: len 4; hex 8001; asc ;; 4: len 30; hex 39373939383430622d323938372d346630382d613539342d663736303831; asc 9799840b-2987-4f08-a594-f76081;...(truncated); 5: len 30; hex 34343431316237362d386663312d343332652d386332342d653837646433; asc 44411b76-8fc1-432e-8c24-e87dd3;...(truncated); 6: len 8; hex 80001241013060af; asc A 0` ;; 7: SQL NULL; 8: len 1; hex 00; asc ;; 9: len 30; hex 3832363139332d313231362d343439612d39652d35343232; asc 82ff6193-1216-449a-9e33-5426fb;...(truncated); 10: len 30; hex 62653637616563323661316363613261646561616135373430646130; asc be67aec26a1cca2adeffaaa5740da0;...(truncated); 11: len 8; hex 80001241014ee557; ascA N W;; 12: len 8; hex 80001241013060af; ascA 0` ;; *** (2) TRANSACTION: TRANSACTION 0 12012949, ACTIVE 0 sec, process no 7328, OS thread id 1182153056 updating or deleting, thread declared inside InnoDB 499 mysql tables in use 1, locked 1 8 lock struct(s), heap size 1216, undo log entries 3 MySQL thread id 93773, query id 1913636516 lf20.eviltwinstudios.net 192.168.100.163 eviltwin updating delete from user_sessions where id='7e5070a8-9576-43c5-b184-ebfc7c288d69' and hibernateVersion=1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 130062 n bits 144 index `PRIMARY` of table `eviltwin/user_sessions` trx id 0 12012949 lock_mode X locks rec but not gap Record lock, heap no 9 PHYSICAL RECORD: n_fields 13; compact format; info bits 32 0: len 30; hex 37653530373061382d393537362d343363352d623138342d656266633763; asc 7e5070a8-9576-43c5-b184-ebfc7c;...(truncated); 1: len 6; hex 00b74d95; asc M ;; 2: len 7; hex 00803c29ee; asc ) ;; 3: len 4; hex 8001; asc ;; 4: len 30; hex 39373939383430622d323938372d346630382d613539342d663736303831; asc 9799840b-2987-4f08-a594-f76081;...(truncated); 5: len 30; hex 34343431316237362d386663312d343332652d386332342d653837646433; asc 44411b76-8fc1-432e-8c24-e87dd3;...(truncated); 6: len 8; hex 80001241013060af; asc
rename a DB schema?
Hi, Is it possible to rename a DB schema? if so how? Using mysql 5.0, innodb engine. -nikita -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
make mysqldump to sort columns alphabetically
Hi, I'm diffing two versions of a schema produced using mysqldump and would like to know if there's a way to make mysqldump sort entries inside CREATE statements (say alphabetically or in some other way)? Currently some of the column declarations are juxtaposed between the versions and thus produce false diffs. Mysql 5.0, InnoDB thanks a lot -nikita -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: make mysqldump to sort columns alphabetically
Thanks, Douglas! That seems OK, but I'd prefer to avoid altering the schemas in any way. In particular altering order of constraints seems error-prone, given that one is essentially re-defining these, not simply rearranging the order. Am I asking for impossible? ;- -nikita Douglas Sims wrote: One way you could solve this is to conform the column orders between the two versions. For example, if one table, t6, has columns id, name, and address and the same table in the second database is id, address, name, you could just ALTER the second database t6 table to be id, name, address: mysql describe t6; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | int(9) | NO | PRI | 0 | | | address | varchar(32) | YES | | | | | name| varchar(32) | YES | MUL | | | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql alter table t6 change column address address varchar(32) after name; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql describe t6; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | int(9) | NO | PRI | 0 | | | name| varchar(32) | YES | MUL | | | | address | varchar(32) | YES | | | | +-+-+--+-+-+---+ 3 rows in set (0.00 sec) Here is the mysql documentation on ALTER TABLE: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Douglas Sims [EMAIL PROTECTED] On Sep 8, 2006, at 12:27 PM, Nikita Tovstoles wrote: Hi, I'm diffing two versions of a schema produced using mysqldump and would like to know if there's a way to make mysqldump sort entries inside CREATE statements (say alphabetically or in some other way)? Currently some of the column declarations are juxtaposed between the versions and thus produce false diffs. Mysql 5.0, InnoDB thanks a lot -nikita --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: effects of aquiring exclusive lock on subsequent reads inside same TX?
I'm new to MySQL mailing lists. Is there a more appropriate list for this type of question? thanks -nikita Nikita Tovstoles wrote: Hi, I'm running into lost of DB deadlocks would really appreciate answers to the questions below which will help me diagnose the problem. I am running MySQL 5 InnoDB, SERIALIZABLE mode. Let's say we have: 1.TX1 START //assume autocommit is off 2.TX1 read on TableA 3.TX1 update on TableA 4.TX1 read on TableB 5.TX1 COMMIT Is the following correct? -In step2, TX1 obtains a SHARED lock. Is it applied to all rows in TableA or only those returned by select statement? -In step3, TX1 obtains an EXCLUSIVE lock. Does that lock out all of TableA or only those rows that are being updated? -In step4, does TX1 apply SHARED or EXCLUSIVE lock? is the lock applied to all of TableB or only to rows returned by the select statement? In general what is the effect (w/r/t locking) on selects performed after a write operation within the same TX? thanks a lot! -nikita Nikita Tovstoles http://doppelganger.com [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Doppelganger, Inc. Re: effects of aquiring exclusive lock on subsequent reads inside same TX? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
effects of aquiring exclusive lock on subsequent reads inside same TX?
Hi, I'm running into lost of DB deadlocks would really appreciate answers to the questions below which will help me diagnose the problem. I am running MySQL 5 InnoDB, SERIALIZABLE mode. Let's say we have: 1.TX1 START //assume autocommit is off 2.TX1 read on TableA 3.TX1 update on TableA 4.TX1 read on TableB 5.TX1 COMMIT Is the following correct? -In step2, TX1 obtains a SHARED lock. Is it applied to all rows in TableA or only those returned by select statement? -In step3, TX1 obtains an EXCLUSIVE lock. Does that lock out all of TableA or only those rows that are being updated? -In step4, does TX1 apply SHARED or EXCLUSIVE lock? is the lock applied to all of TableB or only to rows returned by the select statement? In general what is the effect (w/r/t locking) on selects performed after a write operation within the same TX? thanks a lot! -nikita -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
