MySQL Forums
Forum List  »  InnoDB

Re: HOW: SELECT ...FOR...UPDATE SKIP LOCKED but allow INSERT
Posted by: Kristijan Marin
Date: January 29, 2019 08:40AM

Hi Peter,

I went and changed the isolation level according to your description, but then came across this post :

In READ COMMITED, you see the information that had been committed, regardless if you are in a transaction or not, thus it´s not guaranteed integration in the information because it can change multiple times. Instead of this is REPEATABLE READ that forbids you in an occurrence of a transaction the modification(UPDATE) of information(integrity), but you can add information(INSERT ...)

What does "it´s not guaranteed integration in the information because it can change multiple times." mean?
OK he's not saying anything about locking .... cause I've changed the isolation level to Read Committed
and did a test in two sessions ... to see what happens if I lock it in one session and then try to
update it, and you can't ,but still can insert , which is what I want,

I'm just thinking out loud .... Why would I ever use REPEATED READ (in my situation) as being the default iso level in MySql....

Cause If I need to JUST read some records, without locking them, I would actually expect them to be changed on "second","third" read, cause I always want to get the last committed data. My workflow expects
to always get committed data.

But If this is some sort of a working queue table, and I need to do some stuff based on my records in
this queue or just to change some USER's fullname, then I will lock them (to prevent other CRONs to access it ), change some flag on them to
signal other CRON jobs that this one was already processed, or at the end delete them ... so what I'm trying
to say is,
if I lock some record, no one should be able to change or delete it except me, or the one that is holding the
lock, until I'm done and the lock is released with commit/rollback,

READ COMMITTED is just that, am I right?

Peter, I owe you big time.
Thank you so much,
Kris

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: HOW: SELECT ...FOR...UPDATE SKIP LOCKED but allow INSERT
44
January 29, 2019 08:40AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.