Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ERROR 1222: Lock request timeout

Posted on 2003-03-12
12
Medium Priority
?
1,614 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

578 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