sql rollback commit 연산과 작업 단위

 이 글은 commit과 rollback에 대해서 깊게 들어가지 않습니다. 큰 그림만 그리는 글이니, 심도 있는 글을 원하신다면 다른 (좋은) 글을 보시는 것을 권장합니다. Transaction이란, 작업 단위를 의미합니다. 그러면, 작업을 했을 때, 성공적으로 수행이 된 경우가 있고, 실패가 된 경우가 있을 겁니다. 이 때 어떻게 처리해야 할까요?

 

 

---

 이전에 제가 Thread에서 stop을 호출하는 게 왜 위험할까요? 라는 질문을 한 적이 있었습니다. 그에 대한 답은 절반만 수행된 상태가 나타나기 때문입니다. 원소가 삭제는 되었는데, 크기가 바뀌지 않은 경우가 그 예입니다. 이것을 데이터베이스의 '작업'에도 적용을 시켜 보겠습니다.

 

 

 school 데이터 베이스의 takes 릴레이션에서 ID가 1000이고, 수강 년도가 2006인 레코드를 뽑아보겠습니다.

 

 

 그러면, 위와 같이 2개의 레코드가 나옵니다.

 

 

 이 쿼리는 무엇을 의미하나요? 천천히 해석해 보시면, takes에 있는 릴레이션에서, ID가 1000인 학생이, 2006년에 들은 수업에 대한 grade를 모두 A+로 바꾸라는 것입니다.

 

 

 만약에, 모두 성공적으로 수행되었고, 그것이 반영 되었다면, ID가 1000인 학생이 2006년에 들은 수업에 대한 정보는 다음과 같이 업데이트 되었을 겁니다. 여기까지를 그림으로 그려보면 다음과 같습니다.

 

 

 ID가 1000이고, 2006년에 들은 수업에 대해서 A+로 업데이트 하라는 작업이 시작되었고, 2개의 레코드를 업데이트 하는 작업이 done 되었고, 그것을 실제 table에 반영하였다.

 

 

---

 그런데, 이런 경우를 생각해 보겠습니다. takes 릴레이션을 보겠습니다.

 

 

 여기서, 저는 semester의 첫 글자를 소문자로 바꾸고 싶습니다.

 

 

 그러면, semester 전체 글자를 소문자로 바꾸는 쿼리와 동일하므로, mysql에서는 이런 식으로 쓰면 됩니다. lower 함수는 대문자를 소문자로 바꿔주는 함수입니다. 이것이 모두 성공적으로 수행되었다면, 'Spring'은 'spring'으로, 'Fall'은 'fall'로 바뀔 겁니다. 이 작업을 수행하는 중간에 꺼 보도록 하겠습니다.

 

 

 semester의 앞 글자를 소문자로 바꾸는 쿼리를 수행해 보곘습니다. 대충 11 ~ 12초가 걸립니다.

 

 

 Query가 시작되고 나서 약 2초후, Interrupt가 걸렸음을 볼 수 있습니다. 그래서, Query가 취소되었습니다.

 

 

 semester에 어떤 값이 있는지 알아보기 위해서 distinct를 썼습니다.

 

 

 

 그랬더니, Fall과 Spring이 있었습니다. fall, spring이 없었습니다. 이는, 쿼리가 수행되기 이전의 상태로 돌아갔다는 것을 의미합니다. 이를 도식화 시켜 보면 아래와 같습니다.

 

 

 update 쿼리가 수행되고 나서 2초 후에, 멈추라는 인터럽트가 들어왔습니다. 실행 중간에, fall이라던지, spring으로 업데이트가 된 경우가 있을 겁니다. Fall이나 Spring인 레코드도 있을 거고요. 이를 중간만 완료된 어중간한 상태라고 하겠습니다. 이 과정에서 fail이 떨어졌기 때문에 update Query를 수행하는 것이 abort가 되었고, 쿼리가 수행되기 이전의 상태로 돌아간 것입니다. 이를 rollback이라고 합니다.

 

 

---

 Autocommit을 꺼 보겠습니다.

 

 

 5번 쿼리까지 반영이 되었고, 6 ~ 8번까지 수행하고 아직 commit을 하지 않았습니다. 여기서 핵심은 7번이 semester가 'Fall'인 경우에, 그 값을 'fall'로 업데이트를 한다는 것입니다. write 연산을 수행하는 겁니다.

 

 

 workbench에서는 요렇게 보입니다.

 

 

 그런데, Shell에서는 다르게 보입니다. 왜 그럴까요? 자세한 이유는 isolation level을 간단하게 설명하면서 다시 다루겠습니다. 쿼리 6 ~ 8번을 잘 보았다면, 그 어디에서도 commit은 없었다는 것을 의미합니다. 그런데, 분명 7번 update 연산은 성공적으로 끝났습니다.

 

 그러면, done 상태인 것을 의미합니다. 교과서에는 부분 완료로 되어 있으니, 그게 더 적합하겠군요.

 

 

 이 단계에서는 commit이 된 상태가 아닙니다. 즉, 실제로 반영된 상태는 아니기 때문에, 두 결과가 다르게 나올 수 있습니다. GUI mysql에서는, 두 개의 메뉴가 활성화가 되어 있습니다.

 

 

 v과 x입니다. 각각, commit과 rollback입니다. 당연하게도, 두 번째 메뉴인 rollback을 클릭하게 되면, 해당 트랜잭션은 무효화가 되고 rollback이 될 겁니다. x를 눌러보겠습니다.

 

 

 이 말인 즉슨 6 ~ 8까지 수행한 것을 무시하겠다는 의미입니다. done, 그러니까 Transaction이 부분적으로 완료된 상태에서 그것들을 취소해서 원상태로 돌아갔습니다.

 

 

 select 쿼리를 수행해서 semester 값만 얻어와 보면 알 수 있는 부분입니다.

 

 

 작업이 commit이 되었거나 abort가 되면 그 작업은 '종료되었다'고 이야기 합니다. commit을 한 경우, rollback 작업으로 해당 트랜잭션을 수행하기 이전으로 되돌릴 수 없습니다. 프로그램 상에서 데이터 베이스를 연결해서 다루다 보면 많이 나오는 용어들이니, 큰 그림만 그릴 수 있으면 좋겠습니다.