Finally I’ve understand, why one of my Interviews failed. Few months ago I was passing MSSQL interview and was asked about transactions and how should I handle it in MSSQL server.
I knew about transactions and how to handle it. I’ve told about different isolation levels (Read Committed \ Uncommitted. Serializable, Repeatable Read), but all of this was off the mark. I’ve told, that clients should hold the transaction by itself and no need to handle it from SQL side. Told about ACID and said, that all queries from the client should have Atomicity. Interviewers were really dissatisfied and I felt myself like a real newbie.
Todays I understand, what shit I was carrying out…
I’ve helped my friend (we work with freelance project) with stored procedure for Import products (he have a small system for automate restaurant’s business processes). In fact this SP gets some data as a parameter in the table Type, adds some additions and conversions with data and Updates or Inserts new rows for products. And It updates bad data in case of errors inside of SP. I’ve found, that if internal data conversions raise an error (like subquery returns more than 1 row as it was expected), SP don’t stop and continue to save data, even if some internal operation failed.
After several thoughts I’ve added blocks with TRY … CATCH … but didn’t understand why I had to do it. To tell the truth I’ve been working with FireBird DB for the last 4 years and forget, how to create transaction in SQL scripts. Firebird is not supporting internal transactions (it can only run another autonomous transaction from the current block). Each error inside of any SQL block (SP or simple update query) produces rollback.
Yep. Firebird SQL by default works like MSSQL with SET XACT_ABORT ON;
Shame for me….
Simple answer about transactions in FB
Wish you well