Row of database is unreacheble

Posted on

Question :

Here is the problem: I have a table called “flags” with 54 columns:

PVKEY   PEOPLEKEY   ADDRESSKEY  FLAGTYPE    FLG1    FLG2    FLG3    FLG4    FLG5    FLG6    FLG7    FLG8    FLG9    FLG10   FLG11   FLG12   FLG13   FLG14   FLG15   FLG16   FLG17   FLG18   FLG19   FLG20   FLG21   FLG22   FLG23   FLG24   FLG25   FLG26   FLG27   FLG28   FLG29   FLG30   FLG31   FLG32   FLG33   FLG34   FLG35   FLG36   FLG37   FLG38   FLG39   FLG40   FLG41   FLG42   FLG43   FLG44   FLG45   FLG46   FLG47   FLG48   FLG49   FLG50

If I do this:

select * from flags where PVKEY = 902180 

it takes forever. If I do for any other PVKEY, the query runs in less then 1 second.

If I do:

select * from flags

It runs fast until reachs that PVKEY.

I’ve try to UPDATE or DELETE, with no sucess, it never ends the execution.

The only query that works for this row is:

select PVKEY from flags where PVKEY = 902180 

It returns me: 902180

I’m running SQL Server 2012.

Any ideas, please?

Answer :

To see open transactions which may be locking rows, execute:

SELECT * FROM sys.sysprocesses WHERE open_tran = 1

Issuing a COMMIT or ROLLBACK for those transactions would address your issue.

If you don’t mind dirty reads (depends on your business rule for your particular situation) you can

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

And this would allow you to read the table without the wait. It;s important to understand that the data would be inaccurate if the pending transaction was rolled back.

Leave a Reply

Your email address will not be published.