변경 관리 주의사항
컬럼 속성 변경 시 항상 DEFAULT, NULLABLE, COMMENT 등 컬럼에 부여된 속성을 ALTER문에 포함하여 변경해야 한다. DBA가 오브젝트를 변경할 때 어떤식으로 변경해야 하고, 고려해야할 사항이 무엇인지 기술해 본다. 우선 Meta Lock이란 개념을 알아야 한다.
Meta Lock이란?
오브젝트의 변경을 방지해주는 Lock으로 트랜잭션이 종료되지 않은 오브젝트를 변경할 수 없도록 한다. 이때의 오브젝트는 procedure, function, trigger 등이 포함된다.
주의할 것은 SELECT만해도 Meta Lock이 발생하여 해당 트랜잭션이 종료되지 않으면 다른 사용자가 변경을 할 수 없다. 물론 DML(INSERT, UPDATE, DELETE, SELECT)는 Lock 없이 잘 수행된다.
개발 간 주의사항으로 HeidiSQL이나 기타 툴을 사용하여 개발 시에 SELECT 후 ROLLBACK/COMMIT을 하면 이러한 현상을 미연에 방지할 수 있다. 이러한 Meta Lock이 발생한 오브젝트는 변경이 까다롭다. 변경 시 Metadata Lock을 잡고 있는 모든 쓰레드를 죽인 후에 적용해야 하는데 Meta Lock을 조회하여 누가 사용했고, 어떠한 행위를 하는지 확인 후 KILL을 하고 오브젝트를 변경해야 한다.
컬럼의 타입이나 null 여부 변경
아래와 같이 test_table의 REGION_ID 컬럼에 타입을 varchar(10)으로 변경해보자.
CREATE TABLE `test_table` (
`STATION_ID` varchar(5) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'Station ID',
`DEVICE_ID` varchar(9) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'Device ID',
`CARD_NUM` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'Card Number',
`USE_DATE` varchar(14) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'Use DateAndTime',
`REGION_ID` varchar(3) COLLATE utf8_bin DEFAULT 'XXXX' COMMENT 'Region ID',
`AGENCY_ID` varchar(3) COLLATE utf8_bin DEFAULT 'Z01' COMMENT 'Agency ID',
PRIMARY KEY (`STATION_ID`,`DEVICE_ID`,`CARD_NUM`,`USE_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1. show create table test_table
ddl을 뽑은 후 REGION_ID 라인을 복사한다.
`REGION_ID` varchar(3) COLLATE utf8_bin DEFAULT 'XXXX' COMMENT 'Region ID',
2. ALTER의 MODFIY를 이용하여 변경하면 되는데 위에서 뽑은 내용을 이용한다.
ALTER TABLE test_table MODIFY `REGION_ID` varchar(10) COLLATE utf8_bin DEFAULT 'XXXX' COMMENT 'Region ID' ;
3. SHOW PROCESSLIST로 상태 조회
4. meta lock이 잡혀 있으면 META LOCK 현황 조회하여 KILL 수행 후 ALTER문 수행한다.
SELECT meta.table_schema AS schema_name
,meta.table_name AS object_name
,REPLACE(meta.lock_type, ' metadata lock', '') AS meta_lock_type
,meta.lock_mode AS meta_lock_mode
,meta.lock_duration AS meta_lock_duration
,ps.id AS thread_id
,concat(ps.user, '@', substring_index(ps.host, ':', 1)) AS user
,ps.command AS thread_status
,ps.time AS thread_time_sec
,info AS thread_info
,concat('KILL ', ps.id) as kill_thread
FROM information_schema.metadata_lock_info meta
LEFT OUTER JOIN information_schema. processlist ps
ON meta.thread_id = ps.id
5. KILL ID; or KILL QUERY ID;