Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):

• sp_who
• sp_lock

Also, in SSMS, you can view locks and processes in different ways:

Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.

For getting straight to "who is blocked/blocking" I combined/abbreviated sp_who and sp_lock into a single query which gives a nice overview of who has what object locked to what level.

--Create Procedure WhoLock
--AS
set nocount on
if object_id('tempdb..#locksummary') is not null Drop table #locksummary
if object_id('tempdb..#lock') is not null Drop table #lock
create table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6))
Insert into #lock exec sp_lock
if object_id('tempdb..#who') is not null Drop table #who
create table #who (     spid int, ecid int, status char(30),
            loginame char(128), hostname char(128),
            blk char(5), dbname char(128), cmd char(16)
            --
            , request_id INT --Needed for SQL 2008 onwards
            --
         )
Insert into #who exec sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'
Print '-----------------------------------------' + Char(10)
Select     left(loginame, 28) as loginame, 
    left(db_name(dbid),128) as DB,
    left(object_name(objID),30) as object,
    max(mode) as [ToLevel],
    Count(*) as [How Many],
    Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
    l.spid, hostname
into #LockSummary
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and l.status='GRANT'
group by dbID, objID, l.spid, hostname, loginame

Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object

Print '--------'
Print 'Who is blocking:'
Print '--------' + char(10)
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Print '--------'
Print 'Details:'
Print '--------' + char(10)
Select     left(loginame, 30) as loginame,  l.spid,
    left(db_name(dbid),15) as DB,
    left(object_name(objID),40) as object,
    mode ,
    blk,
    l.status
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and blk <>0
Order by mode desc, blk, loginame, dbID, objID, l.status

(For what the lock level abbreviations mean, see e.g. https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx )

Copied from: sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock...

NB the [Xclusive lock for command] column can be misleading -- it shows the current command for that spid; but the X lock could have been triggered by an earlier command in the transaction.

exec sp_lock

This query should give you existing locks.

exec sp_who SPID -- will give you some info

Having spids, you could check activity monitor(processes tab) to find out what processes are locking the tables ("details" for more info and "kill process" to kill it).

I have a stored procedure that I have put together, that deals not only with locks and blocking, but also to see what is running in a server. I have put it in master. I will share it with you, the code is below:

USE [master]
go

CREATE PROCEDURE [dbo].[sp_radhe] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- the current_processes
-- marcelo miorelli 
-- CCHQ 
-- 04 MAR 2013 Wednesday

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, '') AS login_name
,COALESCE(es.host_name,'') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
CASE es.transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
END
,COALESCE(es.cpu_time,0) 
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
    + COALESCE(es.writes,0) 
    + COALESCE(er.reads,0) 
+ COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
where es.is_user_process = 1 
  and es.session_id <> @@spid
  and es.status = 'running'
ORDER BY es.session_id

end 

GO

this procedure has done very good for me in the last couple of years. to run it just type sp_radhe

Regarding putting sp_radhe in the master database

I use the following code and make it a system stored procedure

exec sys.sp_MS_marksystemobject 'sp_radhe'

as you can see on the link below

Creating Your Own SQL Server System Stored Procedures

Regarding the transaction isolation level

Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask

Jonathan Kehayias
Once you change the transaction isolation level it only changes when the scope exits at the end of the procedure or a return call, or if you change it explicitly again using SET TRANSACTION ISOLATION LEVEL.

In addition the TRANSACTION ISOLATION LEVEL is only scoped to the stored procedure, so you can have multiple nested stored procedures that execute at their own specific isolation levels.

This should give you all the details of the existing locks.

DECLARE @tblVariable TABLE(SPID INT, Status VARCHAR(200), [Login] VARCHAR(200), HostName VARCHAR(200), 
    BlkBy VARCHAR(200), DBName VARCHAR(200), Command VARCHAR(200), CPUTime INT, 
    DiskIO INT, LastBatch VARCHAR(200), ProgramName VARCHAR(200), _SPID INT, 
    RequestID INT)

INSERT INTO @tblVariable
EXEC Master.dbo.sp_who2

SELECT v.*, t.TEXT 
FROM @tblVariable v
INNER JOIN sys.sysprocesses sp ON sp.spid = v.SPID
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS t
ORDER BY BlkBy DESC, CPUTime DESC

You can then kill, with caution, the SPID that blocks your table.

kill 104 -- Your SPID

You can also use sp_who2 which gives more information

Here is some info http://dbadiaries.com/using-sp_who2-to-help-with-sql-server-troubleshooting

A colleague and I have created a tool just for this. It's a visual representation of all the locks that your sessions produce. Give it a try ( http://www.sqllockfinder.com ), it's open source ( https://github.com/LucBos/SqlLockFinder )

As per the official docs the sp_lock is mark as deprecated:
This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

and it is recommended to use sys.dm_tran_locks instead. This dynamic management object returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

It generally returns more details in more user friendly syntax then sp_lock does.

The whoisactive routine written by Adam Machanic is very good to check the current activity in your environment and see what types of waits/locks are slowing your queries. You can very easily find what is blocking your queries and tons of other handy information.

For example, let's say we have the following queries running in the default SQL Server Isolation Level - Read Committed. Each query is executing in separate query window:

-- creating sample data
CREATE TABLE [dbo].[DataSource]
(
    [RowID] INT PRIMARY KEY
   ,[RowValue] VARCHAR(12)
);

INSERT INTO [dbo].[DataSource]([RowID], [RowValue])
VALUES (1,  'samle data');

-- query window 1
BEGIN TRANSACTION;

    UPDATE [dbo].[DataSource]
    SET [RowValue] = 'new data'
    WHERE [RowID] = 1;

--COMMIT TRANSACTION;

-- query window 2
SELECT *
FROM [dbo].[DataSource];

Then execute the sp_whoisactive (only part of the columns are displayed):

You can easily seen the session which is blocking the SELECT statement and even its T-SQL code. The routine has a lot of parameters, so you can check the docs for more details.

If we query the sys.dm_tran_locks view we can see that one of the session is waiting for a share lock of a resource, that has exclusive lock by other session:

Plot twist!

You can have orphaned distributed transactions holding exclusive locks and you will not see them if your script assumes there is a session associated with the transaction (there isn't!). Run the script below to identify these transactions:

;WITH ORPHANED_TRAN AS (
SELECT
    dat.name,
    dat.transaction_uow,
    ddt.database_transaction_begin_time,
    ddt.database_transaction_log_bytes_reserved,
    ddt.database_transaction_log_bytes_used
FROM
    sys.dm_tran_database_transactions ddt,
    sys.dm_tran_active_transactions dat,
    sys.dm_tran_locks dtl
WHERE
    ddt.transaction_id = dat.transaction_id AND
    dat.transaction_id = dtl.request_owner_id AND
    dtl.request_session_id = -2 AND
    dtl.request_mode = 'X'
)
SELECT DISTINCT * FROM ORPHANED_TRAN

Once you have identified the transaction, use the transaction_uow column to find it in MSDTC and decide whether to abort or commit it. If the transaction is marked as In Doubt (with a question mark next to it) you will probably want to abort it.

You can also kill the Unit Of Work (UOW) by specifying the transaction_uow in the KILL command:

KILL '<transaction_uow>'

References:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017#arguments

https://www.mssqltips.com/sqlservertip/4142/how-to-kill-a-blocking-negative-spid-in-sql-server/