Stored Procedures
- 일련의 쿼리를 마치 하나의 함수 처럼 실행하기 위한 쿼리의 집합이다.
DELIMITER //
구문을 만들 준비 를 하겠다.
CREATE PROCEDURE myProc()
myProc() 라는 이름의 프로시저 함수를 만들겠다. ( myProc 옆에 괄호를 붙여준 이유는 함수임을 파악하기 쉽게 하려고)
BEGIN
어떤 쿼리문인지!
END
여기가 끝인지!
DELIMITER;
;을 끝으로 구문을 끝내겠다.프로시저 호출
CALL [프로시저명];- VIEW를 사용하여 원천 데이터를 보호했던 것과 비슷하게 새로운 함수 선언을 통하여 직접적인 접근에서 SQL문을 보호하는 역할 도 해줌과 동시에, 많은 작업을 간편하게 해주는 역할 도 하는 것
장단점
1-1. 스토어드 프로그램의 장점
- 데이터베이스의 보안 향상
MySQL의 스토어드 프로그램은 자체적인 보안 설정 기능을 가지고 있으며, 스토어드 프로그램 단위로 실행 권한을 부여할 수 있습니다. 이러한 보안 기능을 조합해서 특정 테이블의 읽기와 쓰기 또는 특정 컬럼에 대해서만 권한을 설정하는 등 세밀한 권한 제어가 가능합니다.주요 기능을 스토어드 프로그램으로 작성한다면 SQL 인젝션과 같은 기본적인 보안 사고는 피할 수 있을 것 입니다.
- 기능의 추상화
복잡한 일련번호 생성 방식(대리점번호 + 시간정보 + 일련번호)의 경우 생상방식 복잡하여, MySQL의 Auto_increment를 이용할 수가 없습니다. 만약 애플리케이션에서 일련번호 생성용 모듈을 개발한다면 개발하는 언어별로 호환이 되지 않을 뿐더러 직접 SQL 클라이언트에서는 사용할 수 가 없습니다. 일련번호 생성용 프로그램을 MySQL 서버의 스토어드 프로그램으로 구현한다면 애플리케이션뿐 아니라 SQL 클라이언트에서도 쉽게 이용할 수 있습니다.각 애플리케이션에서는 일련번호가 어떻게 생성되고 어떤 구조인지 알 필요도 없으며, 그냥 스토어드 프로그램을 호출해서 값을 가져가기만 하면 됩니다.
- 네트워크 소요 시간 절감
일반적으로 애플리케이션과 데이터베이스 서버는 같은 네트워크 구간에 존재하므로 SQL의 실행 성능에서 네트워크를 경유하는 데 걸리는 시간은 그다지 중요하게 생각하지 않습니다. 하지만 하나하나의 쿼리가 아주 가볍고 빠르게 처리될 수 있다면 네트워크를 경유하는데 걸리는 시간이 문제가 될 것입니다. 하나의 프로그램에서 100번, 200번씩 실행해야 하는 쿼리를 스토어드 프로그램으로 구현한다면 스토어드 프로그램을 호출할 때 한번만 네트워크를 경유하면 되기 때문에 네트워크 소요 시간을 줄이고 성능을 개선할 수 있습니다.
- 절차적 기능 구현
DBMS 서버에서 사용하는 SQL 쿼리는 절차적인 기능을 제공하지 않습니다. 즉, SQL 쿼리에서는 IF나 WHILE과 같은 제어 문장을 사용할 수 없습니다. 그에 반해 스토어드 프로그램은 DBMS 서버에서 절차적인 기능을 실행할 수 있는 제어 기능을 제공합니다. 가끔 SQL 문장으로는 절대 처리할 수 없는 문제를 해결해야 할 때도 있습니다. 일반적으로 이런 상황에서는 데이터를 애플리케이션에서 가공한 후 다시 데이터베이스에 저장하는 형태로 개발을 진행합니다. 스토어드 프로그램을 이용해 절차적인 기능을 구현한다면 최소한 네트워크 경유에 걸리는 시간만큼은 줄일 수 있으며, 더 노력한다면 불필요한 애플리케이션 코드도 많이 줄일 수 있습니다.
- 개발 업무의 구분
순수하게 애플리케이션을 개발하는 조직과 DBMS 관련 코드(SQL이나 스토어드 프로그램)를 개발하는 조직이 별도로 구분돼 있다면 DBMS 코드를 개발하는 조직에서는 트랜잭션 단위로 데이터베이스 관련 처리를 하는 스토어드 프로그램을 만들어 API처럼 제공하고, 애플리케이션 개발자는 스토어드 프로그램을 호출해서 사용하는 형태로 역할을 구분해서 개발을 진행할 수도 있습니다.
1-2. 스토어드 프로그램의 단점
- 낮은 처리 성능
스토어드 프로그램은 MySQL 엔진에서 해석되고 실행됩니다. 하지만 MySQL 서버는 스토어드 프로그램과 같은 절차적 코드 처리를 주목적으로 하는 것이 아니라서 스토어드 프로그램의 처리 성능이 다른 프로그램 언어에 비해 상대적으로 떨어집니다. 또한 다른 DBMS의 스토어드 프로그램과 비교해서도
MySQL의 스토어드 프로그램은 성능이나 최적화가 부족한 상태입니다.위 벤치마킹은 문자열 조작이나 숫자 연산과 같은 CPU 위주의 연산만 측정한 것이므로 실제 업무에 적용한다면 이 정도의 차이를 보이지는 않을 것입니다. 하지만, 문자열 연산이나 숫자 연산에 스토어드 프로그램을 이용하는 것은 잘못된 선택입니다. 간단한 숫자나 문자열 연산 그리고 제어문을 이용하긴 하지만 한 번에 많은 쿼리를 실행해야 할 때 가장 효과가 높은 것입니다.
- 애플리케이션 코드의 조각화
애플리케이션 설치나 배포 작업이 갈수록 복잡해지고 있습니다. 각 기능을 담당하는 프로그램 코드가 자바와 MySQL 스토어드 프로그램으로 분산된다면 애플리케이션의 설치나 배포가 더 복잡해지고 유지보수 또한 어려워질 수 있습니다.
출처 : https://12bme.tistory.com/54
트랜잭션의 ACID 성질
트랜잭션이란 여러 개의 작업을 하나로 묶은 실행 유닛을 말한다.
- 각 트랜잭션은 하나의 특정 작업으로 시작을 해서 묶여 있는 모든 작업들을 다 완료해야 정상적으로 종료한다.
- 만약 하나의 트랜잭션에 속해있는 여러 작업 중에서 단 하나의 작업이라도 실패하면, 이 트랜잭션에 속한 모든 작업을 실패한 것으로 판단한다.
- 작업이 하나라도 실패를 하게 되면 트랜잭션도 실패이고, 모든 작업이 성공적이면 트랜잭션 또한 성공이다.
- 성공 또는 실패 라는 두 개의 결과만 존재하는 트랜잭션은, 미완료된 작업없이 모든 작업을 성공해야 한다.
데이터베이스 트랜잭션은 ACID라는 특성을 가지고 있다.
ACID는 데이터베이스 내에서 일어나는 하나의 트랜잭션(transaction)의 안전성을 보장하기 위해 필요한 성질이다.
⚡️ ACID
- ACID는 데이터베이스 내에서 일어나는 하나의 트랜잭션(transaction)의 안전성을 보장하기 위해 필요한 성질이다.
❗️Atomicity(원자성)
원자성이란 트랜잭션이 안전성 보장을 위해 가져야 할 성질 중의 하나이다.
원자성이란 시스템에서 한 트랜잭션의 연산들이 모두 성공하거나, 반대로 전부 실패되는 성질을 말한다.
원자성은 작업이 모두 반영되거나 모두 반영되지 않음으로서 결과를 예측할 수 있어야 한다.
하나의 단위로 묶여있는 여러 작업이 부분적으로 실행된다면, 업데이트가 일어났지만 누가 업데이트했는지 모르거나, 업데이트 날짜가 누락되는 등 데이터가 오염될 수 있다.
예를 들어 계좌이체를 할 때에는 다음과 같은 두 단계가 있다.
A 계좌에서 출금한다.
B 계좌에 입금한다.
계좌이체를 하려는데 A 계좌에서는 출금이 이뤄지고, B 계좌에 입금되지 않았다고 가정한다.
어디서 문제가 발생했는지 파악할 수 없다면, A 계좌에서 출금된 돈은 세상에서 사라지는 돈이 된다.
만약 은행에서 이런 일이 발생한다면, 은행은 더이상 제 기능을 할 수 없을 것이다다.
A 계좌에서 출금하는 일에 성공했지만, B 계좌에 입금하는 작업에 실패한다면 계좌 A에서 출금하는 작업을 포함하여 모든 작업이 실패로 돌아가야 한다는 것이 Atomicity(원자성)이다.
원자성을 지켰다면 1번과 2번, 두 작업이 모두 성공적으로 완료되어야 한다.
그렇지 않으면(둘 중 하나의 작업이라도 실패한다면), 하나의 단위로 묶여있는 모든 작업이 실패하게 만들어 기존 데이터를 보호한다. (롤백 시킨다.)
SQL에서도 마찬가지이다.
특정 쿼리를 실행했는데 부분적으로 실패하는 부분이 있다면, 전부 실패하도록 구현되어 있다.
때때로 충돌 요인에 대해서 선택지를 제공한다.
❗️Consistency(일관성)
일관성은 데이터베이스의 상태가 일관되어야 한다는 성질이다.
일관성은 하나의 트랜잭션 이전과 이후, 데이터베이스의 상태는 이전과 같이 유효해야 한다.
다시 말해, 트랜잭션이 일어난 이후의 데이터베이스는 데이터베이스의 제약이나 규칙을 만족해야 한다는 뜻이다.
예를 들어 ‘모든 고객은 반드시 이름을 가지고 있어야 한다’는 데이터베이스의 제약이 있다고 가정한다.
다음과 같은 트랜잭션은 Consistency(일관성)를 위반한다.
이름 없는 새로운 고객을 추가하는 쿼리
기존 고객의 이름을 삭제하는 쿼리
데이터베이스의 유효한 상태는 다를수 있지만, 데이터의 상태에 대한 일관성은 변하지 않아야 한다.
이 예시는 ‘이름이 있어야 한다’ 라는 제약을 위반한다.
따라서 예시 트랜잭션이 일어난 이후의 데이터베이스는 일관되지 않는 상태를 가지게 된다.
❗️Isolation(격리성, 고립성)
격리성은 모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다는 뜻이다.
실제로 동시에 여러 개의 트랜잭션들이 수행될 때, 각 트랜젝션은 고립(격리)되어 있어 연속으로 실행된 것과 동일한 결과를 나타낸다.
예를 들어 게좌에 만 원이 있다고 가정한다.
이 계좌로부터 계좌 B로 6천 원을, 계좌 C로 6천 원을 동시에 계좌 이체하는 경우, 계좌 B에 먼저 송금한 뒤 계좌 C에 보내는 결과와 동일해야 한다.
동시에 트랜잭션을 실행한다고 해서 계좌 B와 C에 각각 6천 원씩 송금하여 마이너스 통장이 되는 것이 아니다.
각각의 송금 작업을 연속으로 실행하는 것과 동일한 결과가 나타나야 한다.
격리성을 지키는 각 트랜젝션은 철저히 독립적이기 때문에, 다른 트랜젝션의 작업 내용을 알 수 없다.
그리고 트랜잭션이 동시에 실행될 때와 연속으로 실행될 때의 데이터베이스 상태가 동일해야 한다.
❗️Durability(지속성)
지속성은 하나의 트랜잭션이 성공적으로 수행되었다면, 해당 트랜잭션에 대한 로그가 남아야하는 성질을 말한다.
만약 런타임 오류나 시스템 오류가 발생하더라도, 해당 기록은 영구적이어야 한다는 뜻이다.
예를 들어 은행에서 게좌이체를 성공적으로 실행한 뒤에, 해당 은행 데이터베이스에 오류가 발생해 종료되더라도 계좌이체 내역은 기록으로 남아야 한다.
마찬가지로 계좌이체를 로그로 기록하기 전에 시스템 오류 등에 의해 종료가 된다면, 해당 이체 내역은 실패로 돌아가고 각 계좌들은 계좌이체 이전 상태들로 돌아가게 된다.
