?
Solved

ERROR 1222: Lock request timeout

Posted on 2003-03-12
12
Medium Priority
?
1,483 Views
Last Modified: 2011-10-03
Hi All

Hopefully someone can help me with this one. (new to TSQL, know a bit of PL/SQL).

Am trying to get an extract file created through a stored procedure as follows:


CREATE PROCEDURE PROC1
AS

BEGIN

DECLARE @v_accounts1      numeric(10,0);
DECLARE @v_accounts2     numeric(10,0);
DECLARE @ErrorCode          int;

BEGIN TRANSACTION

select a.ID, z.agreement1
into EXTRACTOUT
FROM TABLE1

EXECUTE master..xp_cmdshell 'BCP DB_NAME..EXTRACTOUT OUT C...\test3.txt -S SERVERNAME -U SA -P -t "," -c'

SELECT  @ErrorCode = @@Error;

If @ErrorCode <> 0
     BEGIN
              GOTO ERR_HANDLER  
     END

COMMIT TRANSACTION

DROP TABLE EXTRACTOUT
Return(0)
END

ERR_HANDLER:
   print 'Procedure PROC1 terminated - transaction rolledback'
   print 'Error Code: '
   print  @ErrorCode
   DROP TABLE EXTRACTOUT
   Rollback transaction

GO

But on executing this I manage to lock everything up (executing query batch runs endlessly), am unable to cancel this activity, and when i try to view the current activity for locks, i get Error 1222: Lock request time out period exceeded

It's now not possible to access this database in anyway!

Can someone shed a bit of light on this problem for me? Have I locked things at database level!? What is the correct procedure to resolve this (ideally without restarting the db)?

Many thanks in advance
Ed
0
Comment
Question by:eddeane
[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
  • 6
  • 4
  • 2
12 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 8118250
Can you not get into query analyser?
If you can...
You can use the sp_who system stored procedure (EXECUTE sp_who) to return a list of the currently running processes. Find your process that you started, and note the spid value. Then you can try issuing a KILL <spid_value> command which will attempt to kill off that process. Note that this may take some time.

If not, you may need to restart the server.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8118273
Also, I think the syntax in your procedure is wrong.
Try these steps:

1) Remove the "BEGIN" from near the top of the procedure
2) Remove the "END" from the line after "Return(0)"

Cheers
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8118274
1. It is a deadlock of system tables2.
2. Solution

select * from master.dbo.sysprocesses
exec sp_lock
then
KILL <put spid here>
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 23

Expert Comment

by:adathelad
ID: 8118279
...and remove the ";" at the end of the line that reads:
"SELECT  @ErrorCode = @@Error;"
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 1000 total points
ID: 8118285
The problem is that you start a transaction before creating table, move BEGIN TRAN after SELECT INTO command.
No problem with RETURN.

Good luck!
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8118300
No problem with semicolons, they are standard SQL command delimiters :)
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8118341
EXECUTE master..xp_cmdshell starts new thread, outside the transaction.
You can add "no_output" option to prevent waiting for completition of task, but it cannot help.

Good luck!
0
 

Author Comment

by:eddeane
ID: 8128329
Is difficult to say that one of the comments offered were the correct ones. The answer was a combination of all the comments offered. Is there a way of distributing the points across more than one anwer?
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8128351
Only to create a new question saying "Points for <whoever>" and accepting the user's comment when they post a reply. Glad you got it fixed.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8128834
Thanks. Can you post your corrected code ?
0
 

Author Comment

by:eddeane
ID: 8129291
Many thanks ispaleny and adethelad for all your help, am on a fairly steep learn curve at the moment and your feedback was really useful.
It does seem a fairly odd way to write to a file though, ie going through the command line, am used to using the UTL_FILE package of PL/SQL and presumed there would be something similar for TSQL.
Also, in order to write a header/trailer to an extract, it seems the best thing to do is create an extra file for each of these and just merge the files using xp_cmdshell again. Do you think this is the best way?

Ed

Corrected code as requested:

CREATE PROCEDURE PROC1
AS

DECLARE @v_accounts1      numeric(10,0);
DECLARE @v_accounts2     numeric(10,0);
DECLARE @ErrorCode          int;

select a.ID
into EXTRACTOUT
FROM TABLE1 a

BEGIN TRANSACTION

EXECUTE master..xp_cmdshell 'BCP DB_NAME..EXTRACTOUT OUT C...\test3.txt -S SERVERNAME -U SA -P -t "," -c'

SELECT  @ErrorCode = @@Error;

If @ErrorCode <> 0
    BEGIN
             GOTO ERR_HANDLER  
    END

COMMIT TRANSACTION

DROP TABLE EXTRACTOUT
Return(0)

ERR_HANDLER:
  print 'Procedure PROC1 terminated - transaction rolledback'
  print 'Error Code: ' + @ErrorCode
 
  DROP TABLE EXTRACTOUT
  Rollback transaction

GO
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8131036
1.
DROP TABLE EXTRACTOUT
Rollback transaction

If an error occurs, you drop table and then you rollback drop table. Your SP will not work anymore.

Use

Rollback transaction
DROP TABLE EXTRACTOUT

2. To your question about ORACLE
I am not too familiar with PL/SQL and ORACLE administration.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 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