DB Table Lock 걸기

멀티 쓰레드 환경에서 다수의 DB 커넥션이 테이블 값에 접근하여 쿼리를 수행하려 할때

발생할 수 있는 동시성을 해결하기 위한 방법 중 하나로 "for update" 구문을 사용할 수 있다.

/* 이미 Lock을 걸어 수행 중인 쿼리가 있으면 대기한다. */

select * from table_a for update;

/* 이미 Lock을 걸어 수행 중인 쿼리가 있으면 3초까지만 대기하고 빠져 나온다.(오라클) */

select * from table_a for update wait 3;

/* 이미 Lock을 걸어 수행 중인 쿼리가 있으면 대기하지 않고 바로 빠져 나온다.(오라클) */

select * from table_a for update no wait;

/*

예를 들어 다음 데이터가 들어갈 시퀀스 값을 알고 싶은 경우,

Oracle 의 경우 nextval 기능을 사용하면 다음 시퀀스 값을 유니크하게 얻어 오지만

MySQL 에는 nextval 이 없어 for update 구문을 이용하도록 한다.

*/

select ifnull(max(my_seq+1),1) from table_a for update;

DB 커넥션 끊김 방지하기

보통 DB에서는 쿼리 요청이 없는 Connection 은 일정시간이 지나면 끊어 버린다.

# MySQL 경우 기본 커넥션유지는 interactive_timeout 28800 (8시간)이다.
# https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_interactive_timeout
# 각종 타임 아웃 시간 보기
# 대기시간(연결 유지)은 wait_timeout
show variables like '%timeout%';

따라서 일정 주기만 커넥션을 유지를 위한 더미 쿼리(ex "Select 1;")를 요청해주도록 한다.
아니면 각 DB 마다 연결끊김 에러코드를 인지하여 다시 연결하도록 한다.

oracle
ORA-00028: your session has been killed
ORA-01012: not logged on
ORA-01033: ORACLE initialization or shutdown in progress
ORA-01034: ORACLE not available
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-03113: end-of-file on communication channel
ORA-03114: not connected to ORACLE
ORA-03127: no new operations allowed until the active operation ends
ORA-12571: TNS:packet writer failure

mssql
10054 : Connection Reset By peer (tcp 의 RST 로 비정상 종료로 연결을 끊는 경우)
10061 : Network Error

mysql
2003 : Can't connect to MySQL server
2013 : Lost connection to MySQL server during query
2006 : MySQL server has gone away

ODBC 사용시 각 DBMS 별 Connection String

ODBC 사용시 각 DBMS 별 Connection String

Oracle
Driver={Microsoft ODBC for Oracle};Server=10.10.10.10;Port=3306;Uid=ysoftman;Pwd=abcd1234;

MySQL
Driver={MySQL ODBC 5.2 ANSI Drive};Server=10.10.10.10;Port=3306;Database=testdb;User=ysoftman;Password=abcd1234

MS-SQL
Driver={SQL Server};Server=10.10.10.10,3306;DSN=dsn_test;Database=testdb;Uid=ysoftman;Pwd=abcd1234;

MySQL / Oracle 다음 시퀀스 값 가져오기

db 테이블에서 Oracle sequence 나 MySql auto_increment 속성으로 설정된 컬럼에서 다음 값을 파악해야 하는 경우가 있다.

[Oracle]
# 시퀀스 생성
create sequence id_seq start with 1 increment by 1 maxvalue 9999999999;
# 다음 시퀀스 파악
select nextval() from table_a;

nextval() 할 때마다 자동 증가된 값을 파악할 수 있다.
멀티쓰레드 환경에서 다수의 db 커넥션으로 수행해도 겹치지 않는 증가 값을 가져올 수 있다.

[MySQL]
# 테이블 생성시 컬럼 속성을 auto_increment 로 지정
create table table_a (
seq_id bigint(19) not null AUTO_INCREMENT, # (자동증가)
col1 int(9) default null,
col2 int(9) default null,
PRIMARY KEY (seq_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 최근에 inset 된 후 id 값 파악
# last_insert_id() 를 통해 가장 최근에 insert id 를 파악할 수 있다.
select last_insert_id();


하지만 mysql 의 경우 db 커넥션 마다 독립된 id 값을 별도로 관리하기 때문에
다수의 db 커넥션에서 last_insert_id() 시 같은 값을 가질 수 있다.
예를 들면...
A, B 가 각각 다른 db 커넥션을 가지고 같은 테이블에 insert 를 한다고 했을때
A가 insert 후 last_insert_id() 하면 1
B가 insert 후 last_insert_id() 하면 1
즉 세션마다 유니크한 값을 유지할 수는 있지만 모든 세션에서 유닉크한 값은 될 수 없다.

실제 테스트 해보니 커넥션마다 독립적으로 마지막으로 insert 한 id(auto increment)값을 파악할 수 있었다.
다음과 같은 순서로 쿼리했을때
커넥션1로 insert 3번
커넥션2로 insert 1번
커넥션1에서 select last_insert_id();  -> 3
커넥션2에서 select last_insert_id();  -> 1
와 같이 원하는 값을 얻을 수 있었다.


다음과 같은 다른 방법을 생각해 볼 수 있다.

# 자동 커밋을 안되게 하여 트랜잭션 처리를 가능하게 한다.
set autocommit=0;

# InnoDB 에서의 락 방법
# for update 는 쿼리(트랜잭션)를 실행한 세션을 제외한 모든 세션은 row 에 대한 접근 대기 상태가 된다. (for update 외에 select 는 가능한 lock in share mode 가 있다.)
# 쿼리가 종료되면 락은 풀린다.
# insert 하기전 미리 자동 증가 값을 파악할 수 있다.
select ifnull(max(seq_id)+1, 1) from table_a for update;

# insert 하면 자동증가 값은 위에서의 max(seq_id)+1 이 된다.
insert into tabe_a(col1, col2) values(1, 2);

SQL Query Browser

[free SQL Query Browser]

Oracle SQL Developer http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

MySQL WorkBench http://www.mysql.com/downloads/workbench/

MySQL(sqlite, oracle, mssql, postgresql...) dbeaver https://dbeaver.io/
MySQL SQLyog http://www.webyog.com/
MySQL HeidiSQL http://www.heidisql.com/ (추천)

MSSQL SSMS(SQL Server Management Studio) http://www.microsoft.com/en-us/download/details.aspx?id=7593

ARM 계열 맥북 M1 등에서 mysql workbench 최신 버전(8.0.x)이 계속 크래시 되는 이슈가 있다.

https://forums.developer.apple.com/forums/thread/724378

대신 dbeaver 를 사용하자.

brew install --cask dbeaver-community

[dbeaver shortcut : https://dbeaver.com/docs/dbeaver/Shortcuts/]

ctrl+enter : 쿼리 하나 실행

ctrl+\ : 현재탭의 모든 쿼리 실행

ctrl+/ : 현재 라인 주석 토글

ctrl+shift+F : 쿼리 포맷팅

alt+up : 이전 쿼리 위치로 이동

alt+down : 다음 쿼리 위치로 이동

ctrl+shift+j : 라인 합치기

ctrl+shift+\ : 실행 결과 창 닫기

cmd+shift-x : to uppercaes

cmd+shift-y : to lowercase

sql query examples

-- show command 로 정보 확인

show databases;

show tables;

show variables;

show processlist;

show variables like 'log_output%';

show variables like '%timeout%';

-- db 선택

use ysoftman;

-- create

create table `test_info` (

`id` bigint(20) unsigned not null auto_increment,

`age` int(11) default null,

`name` varchar(45) default null,

`enable` tinyint(1) default 1,

`created_at` datetime default current_timestamp,

`updated_at` datetime default current_timestamp,

`deleted_at` datetime default current_timestamp,

`last_date` datetime default null,

primary key (`id`)

) engine=innodb default charset=utf8;

-- select

select * from user;

select * from user limit 10;

select * from user where name like '%ysoftman%' or name like '%bill%';

select * from user order by id;

-- update

update ysoftman.user set nickname = 'xelloss' where name = 'bill';

update ysoftman.user set nickname = 'xelloss' where name in ('bill', 'yoon', 'naruto');

-- 3개의 테이블 left, right join

select

svc1.name as svc1_name,

svc1.description as svc1_desc,

ifnull(svc2.aaa, '') as svc2_aaa,

ifnull(svc3.aaa, '') as svc3_aaa,

from service1 svc1

right join service2 svc2 on svc1.service2_id=svc2.id

left join service3 svc3 on svc1.service3_id=svc3.id

where s.name='bill' order by name asc;

-- major, minor 별 path 가장 높은 버전으로 조회

select * from version_dha_resource as t1

join (

select

  version_major,

    version_minor,

  max(version_patch) as patch

from version_dha_resource

group by version_major, version_minor

) as t2 on t1.version_major = t2.version_major and t1.version_minor = t2.version_minor and t1.version_patch = t2.patch;

/*
mssql 저장 프로시져 테스트
*/
use [TestSP]
create proc SPQuery(
@a varchar(20),
@b varchar(20),
@c int,
@d float,
@e bigint,
@f int)
as
declare @idx int;
set @idx = 0;
while (@idx < 10)
begin
insert into mytable(col1, col2, col3, col4, col5, col6) values(@a, @b, @c, @d, @e, @f)
set @idx = @idx + 1
end

/*
저장 프로시져 실행
*/
exec SPQuery 'ysoftman', yoon, 123, 1.0, 12345, 123

Linux Oracle libocci.so: undefined reference to 에러 해결 방법

리눅스에서 Oracle Call Interface (OCI) 를 라이브러를 링크하여 빌드할때
-locci 옵션만 사용할 경우 다음과 같은 링크 에러가 발생한다.
libocci.so: undefined reference to ....

[해결방법]
-lclntsh -lnnz11 -locci(또는 -lociei) 를 사용하도록 한다.

// 11 버전일때
Client Code Library (libclntsh.so.11.1) --> ln -s libclntsh.so.11.1 libclntsh.so 로 링크
Security Library (libnnz11.so)
OCCI Library (libocci.so.11.1)  --> ln -s libocci.so.11.1 libocci.so 로 링크
OCI Library (libociei.so)

(자세한 설명)
http://docs.oracle.com/cd/B28359_01/appdev.111/b28390/install.htm

Linux Oracle 11g 설치시 한글깨짐 현상 해결 방법

Oracle 11g 설치시  ./runinstaller 를 실행시켰을때 글자자 네모상자로 깨져 나올 경우
export LANG=C
export LC_ALL=C

를 실행후

./runinstaller

Linux odbc / sqlite mysql oracle 설치 및 환경 설정

// unixODBC 설치
sudo yum -y install glibc-devel.i686 unixODBC-devel.i686 glibc-devel.x86_64 unixODBC-devel.x86_64

// oracle odbc 드라이버 설치
(64bit일경우, 다운로드 : http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html)
sudo rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
sudo rpm -ivh oracle-instantclient11.2-odbc-11.2.0.3.0-1.x86_64.rpm

//mysql odbc 드라이버 설치
sudo yum -y install mysql-connector-odbc mysql-devel

// sqlite 드라이버 설치
sudo yum -y install sqlite-*

// sqliteodbc-0.94 설치(gcc 4.1.2 이하에선 sqliteodbc-0.89 이하 버전설치)
./configure
make
sudo make install

// odbcinst.ini 및 odbc.ini 설정파일 위치 파악
odbcinst -j

// odbc driver 설정
// driver 를 찾을 수 없다는 실패 메시지가 발생하면 /usr/local/etc/odbcinst.ini 에 만들도록 한다.
sudo vi /etc/odbcinst.ini 또는 /usr/local/etc/odbcinst.ini
[SQLite]
Description=SQLite ODBC Driver
Driver=/usr/local/lib/libsqlite3odbc.so
Setup=/usr/local/lib/libsqlite3odbc.so
Threading=2

[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc3_r.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc3_r.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

[OracleODBC-11g]
Description = Oracle ODBC driver for Oracle 11g
Driver = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
FileUsage = 1
Driver Logging = 7

// DSN 설정하기
sudo vi /etc/odbc.ini 또는 /usr/local/etc/odbc.ini
[oracle]
Description=ODBC for oracle client 64
Driver=OracleODBC-11g

// odbc driver 파악
odbcinst -q -d

// odbc dsn 파악
odbcinst -q -s

// 참고 ORACLE 클라이언트 환경 설정(.bashrc 에 설정)
export ORACLE_SID=ysoftman_service_id
export TNS_ADMIN=/usr/lib/oracle/11.2/client64/
export PATH=/usr/lib/oracle/11.2/client64/bin:$PATH
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export TWO_TASK=ysoftman_service_id

// isql 로 접속해보기
isql oracle userid password

// sqlplus 로 접속해보기(TWO_TASK로 접속대상을 설정해서 호스트는 생략)
sqlplus userid/password

Windows 7 Oracle 10g Client 설치시 운영체제 요구 사항 오류 해결 방법

윈도우 7에서 Oracle 10g 를 설치 할때 아래와 같이 운영체제를 알 수 없어 설치를 할수 없게 된다.


[해결방법]
다음의 2개 파일에 아래와 같이 윈도우 7 버전 정보를 추가한다.
Disk1\stage\prereq\client\refhost.xml
Disk1\stage\prereq\client_prereqs\client\refhost.xml

... 생략 ...

<CERTIFIED_SYSTEMS>
<OPERATING_SYSTEM>
<!--Microsoft Windows 2000-->
<VERSION VALUE="5.0"/>
<SERVICE_PACK VALUE="1"/>
</OPERATING_SYSTEM>
<OPERATING_SYSTEM>
<!--Microsoft Windows XP-->
<VERSION VALUE="5.1"/>
<SERVICE_PACK VALUE="1"/>
</OPERATING_SYSTEM>
<OPERATING_SYSTEM>
<!--Microsoft Windows 2003-->
<VERSION VALUE="5.2"/>
</OPERATING_SYSTEM>
<!--Microsoft Windows Vista-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.0"/>
</OPERATING_SYSTEM>
<!--Microsoft Windows 7-->
<OPERATING_SYSTEM>
<VERSION VALUE="6.1"/>
</OPERATING_SYSTEM>
</CERTIFIED_SYSTEMS>

MSSQL 계정 암호 변경하기

MSSQL 에서 sa 나 기타 로그인 아이디에 대한 패스워드를 잊어 버렸다면 아래와 같이 재설정 할 수 있다.
cmd 콘솔에서 sqlcmd 을 실행한다. (참고로 :help 하면 도움말이 표시된다.)
1> alter login ysoftman with password="abc123";
2> go
3> exit