[Building Sakai] MySQL table lock; could you check innodb status for me?

[Charles Hedrick]

I'd like to ask for some help in debugging the table lock problem. Could a few sites with mysql 5.0 and 5.1 try "show innodb status" and look for the section with current transactions:

---TRANSACTION 0 151485, not started, OS thread id 4451168256
MySQL thread id 7499, query id 1904644 localhost 127.0.0.1 sakaiuser
---TRANSACTION 0 151183, not started, OS thread id 4450893824
MySQL thread id 7498, query id 1904622 localhost 127.0.0.1 sakaiuser


The question is whether you have indications of current locks, such as this:

---TRANSACTION 0 128440, ACTIVE 291 sec, OS thread id 165
2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 154, query id 8867 kissaki.oirt.rutgers.edu 128.6.31.98 sakaiuser
---TRANSACTION 0 128436, ACTIVE 291 sec, OS thread id 161
2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 150, query id 8854 kissaki.oirt.rutgers.edu 128.6.31.98 sakaiuser
---TRANSACTION 0 128432, ACTIVE 291 sec, OS thread id 157
2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 146, query id 8841 kissaki.oirt.rutgers.edu 128.6.31.98 sakaiuser
---TRANSACTION 0 128429, ACTIVE 291 sec, OS thread id 155

I see nothing like this on our production system (4.1) or my own development system (5.0). It's pretty clear that idle connections shouldn't be holding open locks, and that if they do, eventually there will be trouble. I'm wondering whether this is specific to 5.1 or whether it is also present in 5.0. If 5.0 is OK, I'd probably prefer to move from 4.1 to 5.0. We need to solve it for 5.1, but at least 5.0 is more current, and should have better performance with multiple cores.

So I'd appreciate having a few sites tell me whether they see these hanging locks, and what version of Mysql you're running.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2421 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20100309/72444ef8/attachment.bin