Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved How do we Implement NoLock in SQLServer ODBC

  1. How-to
0
Votes
Undo
  1. Glenn Barber
    Glenn Barber
  2. PowerBuilder
  3. Friday, 2 February 2024 00:03 AM UTC

We have some background server processing in PB 17 that read from a static SQL Server Database - there appears to be some locking contention and it has been suggested that we have our SQL Queries implement NoLock.

Is that done in the ODBC connection and what parameter do we use?  otherwise how is this implemented?

 

 

Error opening dll library pbCrypt.dll
Faulting module name- msoshext.dll while export a ...
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 2 February 2024 00:15 AM UTC
  2. PowerBuilder
  3. # 1

Hi Glenn;

  That would be a "dirty read" or also known as "Read Uncommitted". That would set by the following ....

SQLCA.Lock = "RU"

HTH

Regards .... Chris 

Comment
There are no comments made yet.
Glenn Barber
Glenn Barber Accepted Answer Pending Moderation
  1. Friday, 2 February 2024 00:40 AM UTC
  2. PowerBuilder
  3. # 2

THis is then a SQLCA  parameter rather than something we can set in the ODBC string - correct?

If we were reading and writing from the same SQLCA connection - would this have any impact on the writes?

 

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 4 February 2024 15:45 PM UTC
Yes, your DBA can turn off logging at the "server" and/or for a specific DB instance.

I used to turn off DB logging, perform a DB full back - then we would perform the intensive table inserts. This was all done overnight. Then the DB Server's logging was restarted for online DB use. Newer SS's though have to have some sort of transaction logging active but there are alternatives...
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 4 February 2024 15:46 PM UTC
Option #1 ....

You CAN set your recovery model to SIMPLE on your dev machines - that will prevent transaction log bloating when tran log backups aren't done.



ALTER DATABASE MyDB SET RECOVERY SIMPLE;
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 4 February 2024 15:47 PM UTC
Option #2...

There is a third recovery mode not mentioned above. The recovery mode ultimately determines how large the LDF files become and how ofter they are written to. In cases where you are going to be doing any type of bulk inserts, you should set the DB to be in "BULK/LOGGED". This makes bulk inserts move speedily along and can be changed on the fly.



To do so,



USE master ;

ALTER DATABASE model SET RECOVERY BULK_LOGGED ;

To change it back:



USE master ;

ALTER DATABASE model SET RECOVERY FULL ;



HTH
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 2 February 2024 05:19 AM UTC
  2. PowerBuilder
  3. # 3

you may also want to look into the snapshot isolation / READ_COMMITTED_SNAPSHOT settings in sql server 2022 (prior versions have some similar options too).  they allow for row versioning to eliminate locks for reads while allowing update/delete/insert transactions to process.  

if the database is completely static, then just do what chris suggested.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.