eddeane
asked on
ERROR 1222: Lock request timeout
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
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
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
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
1. It is a deadlock of system tables2.
2. Solution
select * from master.dbo.sysprocesses
exec sp_lock
then
KILL <put spid here>
2. Solution
select * from master.dbo.sysprocesses
exec sp_lock
then
KILL <put spid here>
...and remove the ";" at the end of the line that reads:
"SELECT @ErrorCode = @@Error;"
"SELECT @ErrorCode = @@Error;"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No problem with semicolons, they are standard SQL command delimiters :)
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!
You can add "no_output" option to prevent waiting for completition of task, but it cannot help.
Good luck!
ASKER
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?
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.
Thanks. Can you post your corrected code ?
ASKER
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
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
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.
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.
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.