Lock request time out period exceeded. Error: 1222

The First Solution
The first and the easy solution is to identify that locking (offending) process and then killing that process. But this will

cause you to loose any information if your offending query was a DDL statment. You must re-run that SQL statment again.

For finding the offending process execute the command

sp_who2
Running sp_who2 is easy, all that is required is to type sp_who2 and execute it.

sp_who2 shows all the sessions that are currently established in the database.

The sp_who2 internal procedure allows users to view current activity on the database. This command provides a view into several system tables (e.g., syslocks, sysprocesses, etc.). The sp_who command returns the following information:

Spid—The system process ID.
status—The status of the process (e.g., RUNNABLE, SLEEPING).
loginame—Login name of the user.
hostname—Machine name of the user.
blk—If the process is getting blocked, this value is the SPID of the blocking process.
dbname—Name of database the process is using.
Cmd—The command currently being executed (e.g., SELECT, INSERT)
CPUTime—Total CPU time the process has taken.
DiskIO—Total amount of disk reads for the process.
LastBatch—Last time a client called a procedure or executed a query.
ProgramName—Application that has initiated the connection (e.g., Visual Basic, MS SQL Query Analyzer)

Every session (or porcess) has its own unique SPID's, or Server process Id's.
Search for the column BlkBy (acutally Block by), this columns tell you which Process (SPID) is blocking this process. The column BlkBy has a recursive relationship with SPID.

Process that are being blocked by any other process are listed in this column. Search for the Process that is blocking other process. For e.g. you find '65′ SPID blocking many other process. After finding that process next step is to kill the offending process using the KILL command.

Simply write
Kill . For the above case write

Kill 65 .

Again perform sp_who2 you will see that the offending process has been killed. This will remove your Locking Error.

Another approach is to execute this Select

Select Distinct object_name(l.rsc_objid), l.req_spid, p.loginame
from master.dbo.syslockinfo l (nolock) join
master.dbo.sysprocesses p (nolock) on l.req_spid=p.spid
where object_name(l.rsc_objid) is not null

This script will show which table was locked, the process that locked the table and the login name used by the process. Once you find out which process is locking a table, you can issue a "kill" on that SPID. The offending process will be causing a lock on the syscolumns, syscomments, and sysobjects tables.

The second or alternative solution

Though sometime there is requirement that we can not terminate anything. If we know which transaction is locking up resources and database, we need to still run the same transaction.

Alternate Fix/WorkAround/Solution:
In this scenario following changes must be done in the offending transaction:
1) Modify the Transaction use query hints (use RECOMPILE,MAXDOPhints)
2) Run big Transaction in smaller transactions.
3) Upgrade Hardware if possible.