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 Order | Scenario | |
|---|---|---|
| Session 1 | Session 2 | |
| 1 | Create table (TEST1.RXTEST) | |
| 2 | Insert 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 Order | Scenario | |
|---|---|---|
| Session 1 | Session 2 | |
| 1 | Create table (TEST1.RXTEST) | |
tbsql TEST1/TEST1 TmaxData Corporation Copyright (c) 2008- . All rights reserved.
CREATE TABLE TEST1.RXTEST(ID NUMBER, TABLE 'TEST1.RXTEST' CREATED. | ||
| 2 | Insert data into table (TEST1.RXTEST) | |
INSERT INTO TEST1.RXTEST VALUES (1, 'TEST'); 1 ROW INSERTED. COMMIT; COMMIT COMPLETED.
SELECT * FROM TEST1.RXTEST; ----------- 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;
2017/06/07 12:13:26 1 row selected.
UPDATE TEST1.RXTEST SET NAME = 1 ROW UPDATED.
SELECT * FROM TEST1.RXTEST; ------------ 1 TESTTEST 1 ROW SELECTED. | ||
| 4 | Session 2 modifies data in table (TEST1.RXTEST) | |
tbsql TEST2/TEST2 tbSQL 7 TmaxData Corporation Copyright (c) 2008- . All rights reserved.
ALTER SESSION SET Session altered.
SELECT SYSDATE FROM DUAL;
2017/06/07 12:18:26 1 row selected.
UPDATE TEST1.RXTEST SET NAME = <HANG> | ||
| 5 | Modify data in table (TEST1.RXTEST) and commit | |
COMMIT; COMMIT COMPLETED. | ||
| 6 | Confirm that the HANG in Session 2 is released | |
| 1 ROW UPDATED. | ||
MVCC Function
Execution
| Execution Order | Scenario | |
|---|---|---|
| Session 1 | Session 2 | |
| 1 | Create table (TEST1.MVCCTEST) | |
| 2 | Insert data into table (TEST1.MVCCTEST) | |
| 3 | Modify data in table (TEST1.MVCCTEST) | |
| 4 | Query the modified data in table (TEST1.MVCCTEST) | |
| 5 | Query data in table (TEST1.MVCCTEST) | |
| 6 | Commit the modified data | |
| 7 | Query the modified data in table (TEST1.MVCCTEST) | |
Result
| Execution Order | ||
|---|---|---|
| Session 1 | Session 2 | |
| 1 | Create table (TEST1.MVCCTEST) | |
tbsql TEST1/TEST1
| ||
| 2 | Insert data into table (TEST1.MVCCTEST) | |
INSERT INTO TEST1.MVCCTEST VALUES(1, 'TEST');
| ||
| 3 | Modify data in table (TEST1.MVCCTEST) | |
| UPDATE TEST1.MVCCTEST SET NAME = 'TEST123' WHERE ID = 1; 1 ROW UPDATED. | ||
| 4 | Query 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;
----------------------- 2017/06/07 12:20:26 1 row selected.
SELECT * FROM TEST1.MVCCTEST ID NAME ------------ 1 TEST123 1 ROW SELECTED. | ||
| 5 | Session 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; -------------------------------- 2017/06/07 12:22:26 1 row selected.
SELECT * FROM TEST1.MVCCTEST ID NAME ------------- 1 TEST 1 ROW SELECTED. | ||
| 6 | Commit the modified data | |
SELECT SYSDATE FROM DUAL; ----------------------- 2017/06/07 12:23:26 1 row selected.
COMMIT; COMMIT COMPLETED | ||
| 7 | Session 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 ID NAME ----------- 1 TEST123 1 ROW SELECTED. | ||