Post reply

Locking with an Index on VarBinary Field

  • March 25, 2005 at 9:45 am

    #65626

    I am running an application on SQL Server 2000 with a Transaction Isolation Level of Serializable (which cannot be changed).  I have a table that has a varbinary column, with an index on this column.  I hvae two simultaneous users within their own Serialized Transactions.  User 1 runs "select * from myTable where myVarBinaryColumn = @Value1" then runs an insert statement on this table "insert into myTable set myVarBindaryColumn = @Value1"

    User2 does the same thing but uses a different value for @Value1.  I get a locking error when I try this even though there is an index on the column. This query doesn't lock if the column is an int field or varchar.  Is there a problem with indexing varbinary fields?

  • March 27, 2005 at 4:25 pm

    #549074

    What is the error and what is the actual query that is experiencing the error?

    The only issue with indexing any variable length field (varchar, varbinary, etc.) is that it can't exceed 900 bytes.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • March 28, 2005 at 7:59 am

    #549114

    Have you tried setting up the granularity to the row?

    ex: "select * from myTable with (rowlock) where myVarBinaryColumn = @Value1

    This statement is invalid:

    insert into myTable set myVarBindaryColumn = @Value1

    If you were trying to perform an update then replace it with

    update myTable  with (rowlock)  set myVarBindaryColumn = @Value1

    HTH

     


    * Noel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply