Uniface User Forum

 View Only
  • 1.  implicit transactions

    Posted 12-08-2022 09:39

    Hello,

     I've asked this question before, but the problem still persists, hence my question again:

    Does Uniface use implicitly transactions? And can it be turned off?

    Our DBA alerts us for the use of implicit transactions that degrade the performance of the application.

    Here's a piece of the sql logging:

     <blocked-process-report monitorLoop="112560">
    <blocked-process>  <process id="process299e41aa108" taskpriority="0" logused="0" waitresource="PAGE: 40:3:991877 " waittime="5502" ownerId="519654068" transactionname="implicit_transaction" lasttranstarted="2022-11-30T09:13:04.563" XDES="0x299e0f6e400" lockMode="S" schedulerid="3" kpid="11772" status="suspended" spid="401" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2022-11-30T09:13:13.143" lastbatchcompleted="2022-11-30T09:13:13.140" lastattention="1900-01-01T00:00:00.140" clientapp="D:\Programs\DiSys\Uniface\common\bin\udnd.exe -srvid=301 -dnp=TCP:+13003||94169455-4248-4A1F-9812-06A69F734702| -drv=MSS -ust=dn" hostname="SW025V1944" hostpid="9188" loginname="dn_online" isolationlevel="read committed (2)" xactid="519654068" currentdb="40" currentdbname="dn22" lockTimeout="10000" clientoption1="671088672" clientoption2="119858">   <executionStack>    <frame line="1" stmtstart="162" stmtend="454" sqlhandle="0x02000000527165011666cfccf11afaccc5232f77e1c68da40000000000000000000000000000000000000000" />    <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" />   </executionStack>   <inputbuf>
    (@P1 numeric(4,0),@P2 numeric(4,0),@P3 numeric(2,0),@P4 numeric(1,0),@P5 char(3))select max([rgl_num_inz])
    from [ros_rgl_v01]
    where ((((([grp_num]=@P1 and [ros_jar]=@P2) and [ros_wek]=@P3) and [ros_vrs]=@P4) and [ros_cde]=@P5))

    </inputbuf>  </process>
    </blocked-process>

     

    In the source where this select statement occurs, there is no a commit or rollback.
    selectdb (max(rgl_num_inz)) %\
    from "ROS_RGL_V01" %\
    u_where (grp_num.ROS_RGL_V01 = grp_num.ROS_V01 &amp; %\
    ros_jar.ROS_RGL_V01 = ros_jar.ROS_V01 &amp; %\
    ros_wek.ROS_RGL_V01 = ros_wek.ROS_V01 &amp; %\
    ros_vrs.ROS_RGL_V01 = 1 &amp; %\
    ros_cde.ROS_RGL_V01 = ros_cde.ROS_V01) %\
    to ($max_rgl_num_inz$)

    Is it necessary to do commit/rollback after select?

    Best regards,
    Erhan



    ------------------------------
    Erhan Gunbulut
    Sogeti Nederland B.V.
    ------------------------------


  • 2.  RE: implicit transactions

    ROCKETEER
    Posted 12-08-2022 10:00
    Hello Erhan,

    As I have already replied in the other post (Implicit transactions) the mentioned behavior seems to be caused by the Microsoft ODBC driver. Here is, once more, the question that one of my colleagues has raised on the SQL Server Forum (back in 2017):

    My colleague has replicated this behavior also outside of Uniface.

    I had a another look at the old case that is related to the above mentioned post and my colleague suggested the following workarounds to the customer that has originally reported this behavior:

    1. Use the (old) SQL ODBC driver instead of the (newer) SQL Server Native Client ODBC driver or the ODBC Driver for SQL Server, or
    2. Explicitly close the MSS path as a workaround.
    In both cases my colleague could not see any implicit transactions anymore.

    I hope this helps.

    Kind regards

    ------------------------------
    Daniel Iseli
    Principal Technical Support Engineer
    Uniface Services
    Rocket Software, Switzerland
    ------------------------------