Avoid Dirty Reads

• Mauricio Morales S

  Ten Centuries

  Points: 1300

  More actions

  April 4, 2006 at 4:34 pm

  #116861

  I have a transaction:

  BEGIN

  TRAN

  SELECT

  * FROM dbo.PKSETUP WITH (SERIALIZABLE, XLOCK)--(XLOCK, ROWLOCK) WHERE FILE_NAME = RTRIM('MTREME01.DBF') AND FIELD_NAME = RTRIM('CRECENO')

  -- Add 5 sec sleep to widen the window for the deadlock

  WAITFOR

  DELAY '00:00:15'

  COMMIT

  TRANSACTION

   

  And I want avoid reads from other transaction:

  SELECT

  * FROM dbo.PKSETUP WHERE FILE_NAME = RTRIM('MTREME01.DBF') AND FIELD_NAME = RTRIM('CRECENO')

  But at the same time could be readed other row in this same table:

  SELECT

  * FROM dbo.PKSETUP WHERE FILE_NAME = RTRIM('ACARCASH.DBF') AND FIELD_NAME = RTRIM('CTRNNO')

  The Delay Time is just to evaluation purposes.

  The PK in this Table is FILE_NAME AND FIELD_NAME

  Thanks for your help

• noeld

  SSC Guru

  Points: 96590

  More actions

  April 4, 2006 at 5:20 pm

  #630875

  Make sure you have an index in

  FILE_NAME  and  FIELD_NAME

  

   

  
  * Noel