Database lock

Tables in the database are always locked.

Cause: Because of the inherited features with Web architecture, the chances of the database locking are indeed increased compared to client/server architecture. For example, on the web: If there is too much time from the beginning of Data base operations to Commit, a transaction will consume database resources and increase the chances of the database locking.

Solution:

  1. Place the transactions in server NVOs or database procedures for execution.

  2. Break the transaction into smaller transactions. Commit each database operations so a transaction does not take too much to Commit.

    Refer to the following coding as an example:

    Window open();
    //Profile appeonsample
    SQLCA.DBMS = "ODBC"
    SQLCA.AutoCommit = "False"
    SQLCA.DBParm = "ConnectString = ??DSN=AppeonSample; UID=dba; PWD=sql"
    CONNECT;
    
    Command Button cb_1:
    String ls_emplid, ls_emplname
    Ls_emplid = sle_1.text
    DELETE employee WHERE s_emplid = :ls_emplid;
    COMMIT;
    
    DECLARE cur_empl CURSOR FOR SELECT s_emplid, s_emplname from employee; ;
    OPEN cur_empl;
    FETCH cur_empl INTO :ls_emplid, :ls_emplname;
    DO WHILE sqlca.sqlcode=0
                Ddlb_1.additem("["+ls_emplic+"]"+ls_emplname)
                FETCH cur_empl INTO :ls_emplid, :ls_emplname;
    
    LOOP
    CLOSE cur_empl;
    Ddlb_1.selectitem(1)
    Ddlb_1.triggerevent ("eventchanged")
    COMMIT;