CODEWITHSUNDEEP
mysqldatabaseisolation-levelrowlocking
Pınar Seçkin
Pınar Seçkin

Reputation: 47

Serializable and Row-based locking in MySQL

I'm trying to convert a database from MsSQL to MySQL.

How exactly should the transaction configuration be done in MySQL for the Serializable isolation level that exists in MsSQL? Because MsSQL keyword didn't work.

Example;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION;
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition GO
COMMIT TRANSACTION;
GO

Except this; Is there a row-based locking feature when transacting on a row of the table between transaction begin and commit?

Example;

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition

// row-based locking for only condition

If it is disabled by default, what should I do to enable it?

Thank you.

Upvotes: 1

Views: 587

Answers (1)

Bart
Bart

Reputation: 124

did you start a transaction after set isolation level? it will set transaction level only for the next transaction. you could set it for all session via "SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;". if you want to do it during create connection to the database server you can add "?sessionVariables=transaction_isolation='SERIALIZABLE'" at the end of the connection string. or you can execute once "SET SESSION TRA...SERIALIZABLE" as soon as you connect.

Upvotes: 1

Related Questions