• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

correct this blockage?

and how I can correct this blockage?
0
enrique_aeo
Asked:
enrique_aeo
1 Solution
 
minganoCommented:
Which blockage?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you wanted refer to this previous question?
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26402112.html

however, I don't see a "blockage" ?...
please clarify
0
 
dwkorCommented:
Locking and blocking are always present in SQL. In most part of the cases it happens because of non-optimized queries and blocking due table/index scans. So in the long run the best option you have is analyze and optimize the queries.

Meanwhile, you can minimize blocking with either read uncommitted mode (or WITH (NOLOCK) hints in selects) - in such case readers are not obtaining shared locks or switching database to read committed snapshot mode (optimistic locking). Again, this is more or less temporary solution - in the long run you need to optimize the queries.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
enrique_aeoAuthor Commented:
how I can fix a database lock?
0
 
dwkorCommented:
What exactly do you mean by "database lock"?
0
 
enrique_aeoAuthor Commented:
I made following test
--first session: la actualizacion debe actualizar registros
begin tran
  update HumanResources.Employee
  set fechaRegistro = '01/01/2010'

--second session:
select * from HumanResources.Employee

run in query block to see (attached file)
What I want to know is how to kill the Process that is blocking the database
resultPROCEDIMIENTO.JPG
0
 
dwkorCommented:
I did the presentation on SQL Saturday #40 related with locking and blocking. You can download it from there: http://aboutsqlserver.com/sql-saturday-40-presentations/

It should give you some ideas why locking occurs and what could be done in order to minimize them.
0
 
dwkorCommented:
As for your latest question - commit or rollback original transaction.
0
 
enrique_aeoAuthor Commented:
What I try to do is a demonstration, so the code is fine, the next step is to kill the lock (this is my question)
0
 
dwkorCommented:
KILL <SPID>

or commit/rollback transaction in the session 1
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now