How can I be getting a deadlock trying to create a stored proc?

If you’re getting an error similar to the following…

SQL0911N The current transaction has been rolled back because of a deadlock 
or timeout. Reason code "68". LINE NUMBER=171. SQLSTATE=40001

… and you know your tables are all just fine, it may be that someone (likely a DBA) has the SYSCAT.ROUTINES table locked up. Try running this…

SELECT RTRIM(R.ROUTINESCHEMA),RTRIM(R.SPECIFICNAME),RTRIM(R.ROUTINENAME) 
FROM SYSCAT.ROUTINES R 
WHERE R.ROUTINETYPE='P'  
ORDER BY RTRIM(R.ROUTINESCHEMA), RTRIM(R.SPECIFICNAME), RTRIM(R.ROUTINENAME)

…and if that errors, try running “WITH UR”. Assuming the 1st fails and the 2nd does not, have your neighborhood DBA kill any connections that may be causing the lock.

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Leave a Reply