![]() In order to have blocking we need another process trying to access the same resource. SELECT TOP (1) * FROM Table1 WITH (TABLOCKX) ![]() and since I'm using WITH (TABLOCKX) I'm going to get an Top 1 because I don't want to get much back (or do much work) Since we aren’t trying to optimize anything here we can just use a query hint to force an exclusive table lock and an explicit transaction to force the lock to be held until we release it. All you have to do is lock a resource that your process is going to want. Blockīlocking occurs when a process requests a lock on a resource and has to wait because that resource is already being locked by another process.Ĭreating a block is pretty simple. This does not mean you should use it to avoid blocking! For all of these examples I’m going to assume the default transaction isolation level READ COMMITTED. The famous (infamous) query hint NOLOCK for example forces a READ UNCOMMITTED transaction isolation level so it doesn’t take or honor certain types of locks, making it much less likely to be blocked (or deadlocked). ![]() ![]() Because of this the transaction isolation level is going to have a major effect on blocking and deadlocking. But sometimes you need to test error handling or prove a point and then you need to do the unusual.īlocking and Deadlocking are based on lock requests and locking is handled differently depending on the TRANSACTION ISOLATION LEVEL. Blocking and deadlocking are not things you typically want to do deliberately.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |