Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> rowlock on insert/update/delete
 

Pages: 1
LLBLGen Pro Runtime Framework
rowlock on insert/update/delete
Page:1/1 

  Print all messages in this thread  
Poster Message
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 25-Jul-2011 15:09:44.  
Hi,

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

Code:
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
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# Posted on: 25-Jul-2011 16:45:27.  
Please check this thread:
http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14111


  Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 25-Jul-2011 16:56:56.  
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
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# Posted on: 25-Jul-2011 17:02:59.  
I think I misunderstood you.

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


  Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 25-Jul-2011 17:22:49.  
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?
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8108 posts
# Posted on: 26-Jul-2011 04:13:34.  
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.


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 26-Jul-2011 14:39:06.  
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?
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8108 posts
# Posted on: 26-Jul-2011 21:02:25. Goto attachments  
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:
Code:
UPDATE [Table] SET Field = value WHERE...

However this query does add hints:
Code:
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:
Code:
// 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.


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 27-Jul-2011 14:01:58.  
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!
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.