Solved

correct this blockage?

Posted on 2010-08-15
10
289 Views
Last Modified: 2012-05-10
and how I can correct this blockage?
0
Comment
Question by:enrique_aeo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 4

Expert Comment

by:mingano
ID: 33443415
Which blockage?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33443493
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
 
LVL 13

Expert Comment

by:dwkor
ID: 33445477
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:enrique_aeo
ID: 33447078
how I can fix a database lock?
0
 
LVL 13

Expert Comment

by:dwkor
ID: 33447171
What exactly do you mean by "database lock"?
0
 

Author Comment

by:enrique_aeo
ID: 33447237
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
 
LVL 13

Expert Comment

by:dwkor
ID: 33447242
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
 
LVL 13

Expert Comment

by:dwkor
ID: 33447261
As for your latest question - commit or rollback original transaction.
0
 

Author Comment

by:enrique_aeo
ID: 33447308
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
 
LVL 13

Accepted Solution

by:
dwkor earned 125 total points
ID: 33447337
KILL <SPID>

or commit/rollback transaction in the session 1
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question