Re: HOW: SELECT ...FOR...UPDATE SKIP LOCKED but allow INSERT
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