Skip to main content

Search

Welcome to Tibero GTS!

TTA BMT Function Verification Scenario (SINGLE Configuration) - Concurrency Control

Document Type | Technical Information

Category | Administration

Applicable Product Versions | Tibero5, Tibero6, Tibero7

Document Number | TADTI220

 

Overview

This document describes a BMT scenario to verify the proper operation of concurrency control functions in a SINGLE environment.

 

 

Method

Row-Level Locking

Explains the row-level locking and MVCC functions.

 

Execution

Execution OrderScenario
 Session 1Session 2
1Create table (TEST1.RXTEST) 
2Insert data into table (TEST1.RXTEST) 
3

Modify data in table (TEST1.RXTEST)

- Do not commit

 
4 Modify data in table (TEST1.RXTEST)
5

Modify data in table (TEST1.RXTEST)

Commit

 
6 Confirm that the HANG is released

 

Result

Execution OrderScenario
 Session 1Session 2
1Create table (TEST1.RXTEST)

tbsql TEST1/TEST1
tbSQL 7

TmaxData Corporation Copyright (c) 2008-

. All rights reserved.
Connected to Tibero.

 

CREATE TABLE TEST1.RXTEST(ID NUMBER,
NAME VARCHAR(20));

TABLE 'TEST1.RXTEST' CREATED.

 
2Insert data into table (TEST1.RXTEST)

INSERT INTO TEST1.RXTEST VALUES (1, 'TEST');

1 ROW INSERTED.

COMMIT;

COMMIT COMPLETED.

 

SELECT * FROM TEST1.RXTEST;
ID NAME

-----------

1 TEST

 
3

Modify data in table (TEST1.RXTEST)

- Do not commit

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

Session altered.

 

SELECT SYSDATE FROM DUAL;
SYSDATE

 

2017/06/07 12:13:26

1 row selected.

 

UPDATE TEST1.RXTEST SET NAME =
'TESTTEST' WHERE ID = 1;

1 ROW UPDATED.

 

SELECT * FROM TEST1.RXTEST;
ID NAME

------------

1 TESTTEST

1 ROW SELECTED.

 
4Session 2 modifies data in table (TEST1.RXTEST)
 

tbsql TEST2/TEST2

tbSQL 7

TmaxData Corporation Copyright (c) 2008-

. All rights reserved.
Connected to Tibero.

 

ALTER SESSION SET
NLS_DATE_FORMAT='YYYY/MM/DD
HH24:MI:SS';

Session altered.

 

SELECT SYSDATE FROM DUAL;
SYSDATE

 

 

2017/06/07 12:18:26

1 row selected.

 

UPDATE TEST1.RXTEST SET NAME =
'TESTTESTTEST' WHERE ID = 1;

<HANG>

5Modify data in table (TEST1.RXTEST) and commit

COMMIT;

COMMIT COMPLETED.

 
6Confirm that the HANG in Session 2 is released
 1 ROW UPDATED.

 

MVCC Function

Execution

Execution OrderScenario
 Session 1Session 2
1Create table (TEST1.MVCCTEST) 
2Insert data into table (TEST1.MVCCTEST) 
3Modify data in table (TEST1.MVCCTEST) 
4Query the modified data in table (TEST1.MVCCTEST) 
5 Query data in table (TEST1.MVCCTEST)
6Commit the modified data 
7 Query the modified data in table (TEST1.MVCCTEST)

 

Result

Execution Order  
 Session 1Session 2
1Create table (TEST1.MVCCTEST)

tbsql TEST1/TEST1
tbSQL 7
TmaxData Corporation Copyright (c) 2008-
. All rights reserved.
Connected to Tibero.


CREATE TABLE TEST1.MVCCTEST(ID NUMBER, NAME VARCHAR(20));
TABLE 'TEST1.MVCCTEST' CREATED.

 
2Insert data into table (TEST1.MVCCTEST)

INSERT INTO TEST1.MVCCTEST VALUES(1, 'TEST');
1 ROW INSERTED.


COMMIT;
COMMIT COMPLETED.

 
3Modify data in table (TEST1.MVCCTEST)
UPDATE TEST1.MVCCTEST SET NAME = 'TEST123' WHERE ID = 1;
1 ROW UPDATED.		 
4Query the modified data in table (TEST1.MVCCTEST)

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

Session altered.

 

SELECT SYSDATE FROM DUAL;


SYSDATE

-----------------------

2017/06/07 12:20:26

1 row selected.

 

SELECT * FROM TEST1.MVCCTEST
WHERE ID = 1 ;

ID NAME

------------

1 TEST123

1 ROW SELECTED.

 
5Session 2 queries data in table (TEST1.MVCCTEST)
 

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

Session altered.

 

SELECT SYSDATE FROM DUAL;
SYSDATE

--------------------------------

2017/06/07 12:22:26

1 row selected.

 

SELECT * FROM TEST1.MVCCTEST
WHERE ID = 1 ;

ID NAME

-------------

1 TEST

1 ROW SELECTED.

6Commit the modified data

SELECT SYSDATE FROM DUAL;
SYSDATE

-----------------------

2017/06/07 12:23:26

1 row selected.

 

COMMIT;

COMMIT COMPLETED

 
7Session 2 queries the modified data in table (TEST1.MVCCTEST)
 

SELECT SYSDATE FROM DUAL;

SYSDATE

----------------------

2017/06/07 12:25:26

1 row selected.

 

SELECT * FROM TEST1.MVCCTEST
WHERE ID = 1 ;

ID NAME

-----------

1 TEST123

1 ROW SELECTED.

 

 

Related to