SQL Server Performance

NetCOBOL with SQL Server 2000

Discussion in 'General Developer Questions' started by merlin, May 14, 2003.

  1. merlin New Member

    Hi Everybody!<br /><br />I need understand the LOCK of SQL. I´m development a program with instructions of SQL Syntax. Imagine, Client A and Client B, for the routine below, i hope that you understand (because is a COBOL Language), but is a SQL Syntax.<br /><br /> MOVE POW-NUMERIC OF FCODIGO TO WS-CODIGO-SQL (moving the DATA of FORM to VARIABLE)<br /> EXEC SQL <br /> DECLARE EDT CURSOR FOR<br /> SELECT CODIGO, DESCRICAO FROM NATUREZAS WHERE CODIGO = :WS-CODIGO-SQL<br /> END-EXEC<br /> EXEC SQL<br /> WHENEVER SQLERROR GO TO<img src='/community/emoticons/emotion-4.gif' alt=':p' />-ERROR<br /> END-EXEC.<br /> P-START.<br /> EXEC SQL<br /> OPEN EDT<br /> END-EXEC<br /> EXEC SQL<br /> FETCH EDT INTO :TNAT-CODIGO, :TNAT-DESCRICAO<br /> END-EXEC<br /> EXEC SQL<br /> CLOSE EDT<br /> END-EXEC<br /> CALL "MOVE-DATA"<br /> CALL "ENABLE-DATA"<br /> GOBACK.<br /> <br /> P-ERROR.<br /> DISPLAY SQLSTATE SQLCODE<br /> GOBACK<br /><br />Well, when Client A, get the data, for exemple, CODIGO = 5, and Client B, execute the same program with the CODIGO = 5, the LOCK occurs at SQLServer. This is correct, because the Client B, will not be able alter this data while the Client A liberate the data. Occurs that, when the program execute the instruction EXEC SQL FETCH EDT INTO :TNAT-CODIGO, :TNAT-DESCRICAO END-EXEC, my application for Client B, stop and i don´t have more controller above my program. I obtain to controller again, when the Client A desists of data, with ROLLBACK or COMMIT instructions. I question: Exists any configuration for execute in the SQL Server?!<br /><br />Thanx for cooperation!<br /><br />Nicola Losacco<br />Analyst of System<br />From Brazil
  2. trifunk New Member

    Are you expecting the lock on the data, is this what you want? You may want to DEALLOCATE the cursor after you close it.
    What re the MOVE-DATA and ENABLE-DATA functions doing with the data?

    Cheers
    Shaun

    World Domination Through Superior Software
  3. Chappy New Member

    I believe the question you are asking is: Is there any way to prevent a client from blocking, if the data it is trying to access is locked? The simple answer is no, and there is no way to see if the data is blocked until you access it.

    You have a couple of choices:

    Using UPDLOCK hint, you can specify that a record is locked by current user, so no other processes can modify it. Using this, only the current user can modify it, but all other users are able to read it without blocking.

    Alternatively, you can implement your own locking mechanism, using a locks table. Using this method, you can query the locks table to see if the data you are intending to retrieve is in use by another user (ie, you previously inserted a record into locks table to signify this), and prevent the user from trying to access that data. Using this method is not perfect, you must think very carefully about concurrency and race conditions.

    Hope this helps.
  4. merlin New Member

    Ok Chappy, perfect!<br /><br />The first option that i want: <i>"Using UPDLOCK hint, you can specify that a record is locked by current user, so no other processes can modify it. Using this, only the current user can modify it, but all other users are able to read it without blocking. "</i><br /><br />But, how to use this hint? Which instruction SQL i need use. I stow searching and i saw other options: SET CONCURRENCY OPTCC and SET CONCURRENCY LOCKCC.<br /><br />Thanx,<br /><br />I continue trying ! [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />Nicola
  5. gaurav_bindlish New Member

    Use syntax like this...
    Select ColA, ColB from tblXXX(with UPDLOCK)
    where SerachCond1 = @Param1
    and SerachCond1 = @Param1
    HTH.

    Gaurav
  6. merlin New Member

    Gaurav,

    i can use with the syntax Declare Cursor?

    EXEC SQL
    DECLARE EDT CURSOR FOR
    SELECT CODIGO, DESCRICAO FROM NATUREZAS WITH UPDLOCK WHERE CODIGO = :WS-CODIGO-SQL
    END-EXEC


    Thanx!

    Nicola [?]
  7. gaurav_bindlish New Member

    Yes use the same while declaring cursor as
    Declare CursName cursor for Select ColA, ColB from tblXXX(with UPDLOCK)
    where SerachCond1 = @Param1
    and SerachCond1 = @Param1
    HTH.

    Gaurav

Share This Page