4

How DO I set the transaction isolation level at proc level in Mysql. Do I have to use session or any other parameter. Right now we have to write at the Select query level. Sql server provides that you can write it once at the procedure level and it will be applicable for all the statements.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
abksharma
  • 576
  • 7
  • 26
  • See [14.3.6 SET TRANSACTION Syntax::Scope of Transaction Characteristics](http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html). – wchiquito Dec 23 '16 at 17:44

1 Answers1

1

I do not believe you can do this in MySQL. You can do it in SQL Server for certain flags like Transaction Isolation Level but not for things like ANSI_NULLS - IIRC ANSI_NULLS must be set at the time of procedure creation. In MySQL I believe you have to set a connection, set the TIL on the connection and then execute the procedure on that connection.

Dan Collins
  • 26
  • 2
  • 1
    I was wrong about this. I was able to set the "Session" Transaction Isolation Level for a stored procedure but only after I had completed all my variable declarations. – Dan Collins Feb 19 '17 at 14:46
  • How did you figure out that you had to complete variable declarations first @dan-collins? I was stick on this forever and this one comment saved me. – Morgan Oct 25 '20 at 14:18