Hi,

I currently use this class to specify nolocks on selects where we need it (found it in the forum somewhere):

 public class NoLockSqlServerCreator : SqlServerSpecificCreator
    {
        public override string CreateHintStatement(RdbmsHint hint, string targetName, params object[] values)
        {
            string toReturn = string.Empty;
            switch (hint)
            {
                case RdbmsHint.TableInFromClauseHint:
                    // enhancement tip: you can check for 'targetname' and values to limit emition of nolock even further. 
                    toReturn = "(nolock)";
                    break;
            }
            return toReturn;
        }   
    }

Is there a way to specify lock hints (specifically rowlock can be useful) for update/insert/delete statements (including adapter.UpdateEntitiesDirectly)?

Thanks,

Wiebe

Please check this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14111

Walaa wrote:

Please check this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14111

Hi Walaa,

I don't see how this answers the question, the thread you refer to refers to the exact thread I found the code above, or am I missing something?

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16508&StartAtMessage=0&#92356

I think I misunderstood you.

Why do you need to specify rowlock, while this is used by default when using a transaction.

Walaa wrote:

I think I misunderstood you.

Why do you need to specify rowlock, while this is used by default when using a transaction.

Is it? I thought the query optimizer chooses the granularity, and uses page lock most of the time as they are less resource intensive?

wtijsma wrote:

Walaa wrote:

I think I misunderstood you.

Why do you need to specify rowlock, while this is used by default when using a transaction.

Is it? I thought the query optimizer chooses the granularity, and uses page lock most of the time as they are less resource intensive?

Yes. Additionally you could define the IsolationLevel of the Transaction. If you really need some kind of special control in locks you could go to the same track you saw in NoLockSqlServerCreator code to include the hint in other areas of the code. We could help you on this if you really need it, although I think you don't really.

daelmo wrote:

Yes. Additionally you could define the IsolationLevel of the Transaction. If you really need some kind of special control in locks you could go to the same track you saw in NoLockSqlServerCreator code to include the hint in other areas of the code. We could help you on this if you really need it, although I think you don't really.

Oh yes I'm pretty sure too it can be solved in a different way, I'm just investigating all possibilities because I get these kind of questions from customers... Do you know what method in NoLockSqlServerCreator should be overridden to add these hints to the table name?

Edit: Basically I want to be able to experiment with lock hints like this: "WITH (updlock, readpast)", that are more specific than the transaction isolation level can specify, can you help me out with that?

wtijsma wrote:

Edit: Basically I want to be able to experiment with lock hints like this: "WITH (updlock, readpast)", that are more specific than the transaction isolation level can specify, can you help me out with that?

Ok. The way the hints work is that they are added to the "FROM [Table] [Hints]" if there is any hint to add. That means that you have to use the FROM clause in the query. So this query for example don't look for hints:

UPDATE [Table] SET Field = value WHERE...

However this query does add hints:

UPDATE [Table] SET Field = value FROM [Table INNER JOIN ...] WHERE...

I'm attaching an example implementation of UPDLOCK adapter, similar to the NoLock example. You have to put it in your DBSpecific project. Then you can use it this way:

// new value for the customer
CustomerEntity customer = new CustomerEntity();
customer.CompanyName = customer.CompanyName + "...";

// build the filter. In this case only the Customer "ALFKIi" will be updated
var filter = new RelationPredicateBucket(CustomerFields.CustomerId == "ALFKI");
// this is needed so the framework will look for hints. We will add any related entity
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);
            
// update
using (var updLockAdapter = new UpdLockAdapter())
{
    int rowsAffected = updLockAdapter.UpdateEntitiesDirectly(customer, filter);
}

It's the same for UpdateEntitiesDirectly.

daelmo wrote:

It's the same for UpdateEntitiesDirectly.

Owww yes of course you don't specify hints on the actual table you're modifying so the code actually works like this... Thanks a lot!