GBase 8s Database Locking Issues and Performance Optimization Strategies

Database locking issues have always been a challenging aspect of database management. In the GBase 8s database, table locks can lead to the locking of table headers, data rows, and other components, which can result in various error messages. This article provides an in-depth understanding of the locking situations in GBase 8s and offers a series of effective resolution strategies.

1. Locking Situations

The 8s locking issue can lock components such as table headers and specific data rows. Different lock conflicts will trigger different error messages, such as 244: Could not do a physical-order read to fetch next row. These are essentially lock conflict issues.

2. DML Statements (INSERT | UPDATE | DELETE)

Take the INSERT statement as an example. In a transaction scenario, executing a single insert statement:

begin work;
insert into tab1 values(1,'test');

When checking the current lock status, you may observe HDR+IX and HDR+X. If there is an X lock on the table, other sessions executing a select * from tab1 under the default CR isolation level will throw the 244: Could not do a physical-order read to fetch next row error.

Locks
address   wtlist   owner     lklist     type
tblsnum   rowid    key#/bsiz
49ca7798  0        d864d868  49ca96f0   HDR+IX
100266    0        0
49ca7820  0        d864d868  49ca7798   HDR+X
100266    103      0 I

3. Checking Locked Sessions With SID

Using onstat -k combined with onstat -u can locate specific sessions, though this method is less intuitive and more suitable for technical support staff.

SQL Query Method

Note: Use lowercase table names.

-- Set isolation level to dirty read for quick system information access
SET ISOLATION TO DIRTY READ;

-- Check the lock status of a specific table
select username, sid, waiter, dbsname, tabname, rowidlk, keynum, type
from sysmaster:syslocks a, sysmaster:syssessions b
where b.sid = a.owner and a.tabname = 'tab1';

The returned information is as follows:

username  gbasedbt
sid       39
waiter
dbsname   mydb
tabname   tab1
rowidlk   0
keynum    0
type      IX
username  gbasedbt
sid       39
waiter
dbsname   mydb
tabname   tab1
rowidlk   259
keynum    0
type      X

Kill the session with the unreleased lock using onmode -z 39, or in a testing environment, ensure all open windows are committed before retrying.

4. Possibility of Lock Conflicts

In normal scenarios, an uncommitted transaction state will always result in locks being held. Common causes include:

• DML operations (INSERT | UPDATE | DELETE)
• DDL operations (CREATE TABLE | ALTER TABLE)
• TRUNCATE
• UPDATE STATISTICS
• CREATE INDEX

In short, most statements involving the creation, modification of database objects, and data manipulation will acquire write locks, which can lead to lock conflicts under the default CR isolation level.

5. Recommended Parameters

Instance-Level Adjustments

The default database isolation level is COMMIT READ. It is recommended to use COMMITTED READ LAST COMMITTED (last committed read).

Check the instance parameter:

onstat -c | grep USELASTCOMMITTED

Your evaluation license will expire on 2024-08-13 00:00:00
# USELASTCOMMITTED - Controls the committed read isolation level.
USELASTCOMMITTED "NONE"

Dynamically adjust the instance parameter to set the CR isolation level to LC:

onmode -wf USELASTCOMMITTED="COMMITTED READ"

Your evaluation license will expire on 2024-08-13 00:00:00
Value of USELASTCOMMITTED has been changed to COMMITTED READ.

Session-Level Adjustments

If unsure whether the LC isolation level meets business needs, you can set session parameters for debugging.

SET ISOLATION TO COMMITTED READ LAST COMMITTED;

For example, if a select statement throws a 244 error, you can manually set the LC isolation level and retry the query to check for continued errors.

6. Stored Procedure DEBUG TRACE 

To debug, you can toggle trace at the desired sections with the following statements:

SET DEBUG FILE TO '/data/lilin/test0817/foo.trace';
trace on;
...
trace off;

Add similar text to facilitate tracking:

trace "trace LC 'insert into tab1 select * from tab1;'";

Demonstration Example

create procedure p1 ()
    SET DEBUG FILE TO '/data/lilin/test0813/foo.trace';
    trace on;

    trace "trace LC 'insert into tab1 select * from tab1;'";
    SET ISOLATION TO COMMITTED READ LAST COMMITTED;
    -- Executable
    insert into tab1 select * from tab1;

    trace "trace CR 'insert into tab1 select * from tab1;'";
    SET ISOLATION TO COMMITTED READ;
    -- Throws error
    insert into tab1 select * from tab1;

    trace off;
end procedure;

The returned text will include markers for easy navigation:

trace on

trace expression :trace LC 'insert into tab1 select * from tab1;'

set isolation to ;

insert into tab1
select *
from tab1;
trace expression :trace CR 'insert into tab1 select * from tab1;'

set isolation to committed read;

insert into tab1
select *
from tab1;
exception : looking for handler

SQL error = -244 ISAM error = -107 error string = = "tab1"
exception : no appropriate handler

If subroutine call tracking is not needed, use trace procedure to only track calls and return values.

Conclusion

Although table locking issues are tricky, they can be effectively avoided and resolved with proper diagnosis and handling methods. The GBase database provides a wealth of tools and parameter adjustment options to help manage database locks better. Hopefully, this article provides practical guidance and assistance.