|
centos : 5.2
mysql : 5.5.11
was : tomcat 6.0
특정 쿼리에서 where 조건의 값이 특정한 값일때 락이 걸립니다.
계속 동일한 쿼리에서만 문제가 생기는데요.
insert나 delete가 아닌 select 시에 발생을 합니다.
SHOW ENGINE INNODB STATUS;로 해서 보면 아래와 같이 걸린 로그가 보입니다.
이게 락이 걸린게 맞는건가요?
아래 쿼리에 짤린 부분이 있는데 select에 아래 쿼리를 추가 하고서 발생했습니다.
아래 부분을 제거하면 정상적으로 호출이 가능 하구요.
, case when b.parent_no > 0 then
(select
case
when LENGTH(substring(htmlStrip(contents), 1, 15)) = 0
then get_code_name('006', checkin_type)
else substring(htmlStrip(contents), 1, 15) end
from tb_talking where talking_no = b.parent_no
)
else '' end as parent_title
계속 걸려 있는걸 풀려면 어떻게 해야 하나요?
그리고 어떤 문제 때문에 발생하는지도 궁금 합니다.
조언 부탁드립니다.
------------
TRANSACTIONS
------------
Trx id counter 97E40A
Purge done for trx's n:o < 97DA87 undo n:o < 0
History list length 1052
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 97E384, not started, process no 3408, OS thread id 1241823552
MySQL thread id 138, query id 1356907629 localhost 127.0.0.1 subway
---TRANSACTION 0, not started, process no 3408, OS thread id 1242089792
MySQL thread id 32, query id 1416738236 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 97E408, not started, process no 3408, OS thread id 1241291072
MySQL thread id 19, query id 1414440545 localhost 127.0.0.1 didueattherat
---TRANSACTION 97E407, not started, process no 3408, OS thread id 1240492352
MySQL thread id 10, query id 1414137099 localhost 127.0.0.1 didueattherat
---TRANSACTION 97DA85, ACTIVE 2499 sec, process no 3408, OS thread id 1241024832
mysql tables in use 11, locked 0
MySQL thread id 16, query id 1416738300 localhost 127.0.0.1 subway
select
b.talking_no
, b.parent_type
, b.line_no
, (select concat(city_type, '_', line_code) from tb_line where line_no = b.line_no) as city_line
, b.contents
, b.ip_address
, date_format(b.regist_date, '%Y?%m?%d? %H:%i:%S') as regist_date
, date_format(b.regist_date, '%Y%m%d%H%i%S') as regist_date2
, b.user_no
, b.reply_cnt
, (select user_name from tb_user where user_no = b.user_no) as user_name
, get_user_photo(b.user_no) as user_profile_path
, a.station_name
from
tb_talking b
Trx read view will not see trx with id >= 97DA86, sees < 97DA86
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3532 OS file reads, 9019 OS file writes, 1771 OS fsyncs
|
