Setting Isolation level in a database / table

Question

I understand the concept of isolation w.r.t transactions. I want to know whether I can set the default isolation level for the entire database / a particular table in a database.

I want to do this because i understand that increasing the isolation level can impact performance so i want to restrict the change to a few tables in the entire DB.

Assuming you could set the default isolation level per table, and you did a query that joined two tables with different defaults, which isolation level do you suppose it would use? — Bill Karwin
– Bill Karwin, Commented Apr 20, 2022 at 16:17
If you are doing thousands of transactions per second, isolation levels may need tuning. Otherwise, your question comes under the category of "premature optimization". That is, don't worry about isolation level until you have a specific use case. Even then, there may be a workaround to avoid the performance issue without touching "isolation". — Rick James
– Rick James, Commented Apr 20, 2022 at 21:41

Mike Sherrill 'Cat Recall' · Accepted Answer · 2022-04-20 16:06:19Z

4

You can set the transaction isolation level for a session. Or for the server. But not for one table.

As a workaround, you can set the desired isolation level immediately before you operate on that single table. Just stay aware that you've set the isolation level for your session.

Docs

i understand that increasing the isolation level can impact performance

Have you measured the impact?

answered Apr 20, 2022 at 16:06

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Karwin Over a year ago

It's worth noting that if one changes the tx isolation, do it before starting the transaction. I've been surprised how many times I've seen code that changes the tx isolation after starting the transaction, and the developer was confused why it had no effect.

gopher · Accepted Answer · 2022-04-21 09:13:05Z

1

If you are using a database eg. Mysql that support multiple engine, then you can create your special table with a special engine eg. memory.

Then your query can fetch rows from most tables with the isolation level of current session, and fetch rows from the special table with the only isolation level for the special engine.

It is not a general solution for your problem, but it may help in some cases.

answered Apr 21, 2022 at 9:13

gopher

613 bronze badges

Collectives™ on Stack Overflow

Setting Isolation level in a database / table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related