Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

correct this blockage?

and how I can correct this blockage?
Avatar of mingano
mingano

Which blockage?
Avatar of Guy Hengel [angelIII / a3]
I presume you wanted refer to this previous question?
https://www.experts-exchange.com/questions/26402112/View-lock-information-using-Activity-Monitor.html

however, I don't see a "blockage" ?...
please clarify
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.
Avatar of enrique_aeo

ASKER

how I can fix a database lock?
What exactly do you mean by "database lock"?
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
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.
As for your latest question - commit or rollback original transaction.
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)
ASKER CERTIFIED SOLUTION
Avatar of dwkor
dwkor
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial