0

I have a simple stored procedure and for testing purposes I have commented out COMMIT statement. 

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK; 
    RESIGNAL;  
END;

    START TRANSACTION; 
        SELECT * FROM Kimmel_tree FOR UPDATE;
        INSERT INTO Kimmel_tree (name, guid) VALUES('abc', 'abcd');         
    #COMMIT;
END

I wanted to check what happens if internet cable is unplugged while transaction is not yet commited. The result is that transaction is rolled back but the lock on whole table is not released. As a result I can't do anything to the table. Sometimes it takes up to twenty minutes for the lock to get released just for no reason. How can I make sure that the used resources are unlocked on ROLLBACK?

niks
  • 579
  • 1
  • 3
  • 14
  • Are you sure that the transaction is rolled back? If you unplug the cable, then the transaction is likely to stay there until some kind of a timeout expires and that's when the locks are released. – Shadow May 03 '19 at 12:49
  • 1
    Ah, found the exact timeout settings. – Shadow May 03 '19 at 12:52
  • @Shadow Yes, when table becomes available again, I can see that the INSERT statement has been rolled back. Could you share what timeout settings have you found? – niks May 03 '19 at 12:57
  • @Shadow Oh, didn't notice a duplicate! Thank you for pointing it out! – niks May 03 '19 at 13:00

0 Answers0