Row-level locknig discussion

Conversation started Jul 25, 2012 at 14:34.

ObsessiveFOSS

Jul 25, 2012 14:34

Mind if I ask a stupid question about MySQL?

On MySQL 5.5, if I perform a DELETE LOW_PRIORITY query, are the deleted rows visible in future SELECT statements while the query is waiting for all reads to stop, or are they made invisible to clients making SELECTS on the same table?

Jack Douglas

@ObsessiveFOSS presumably your storage engine does table level locking, right?

ObsessiveFOSS

@JackDouglas Yes. MyISAM since I need fulltext indices.

Jack Douglas

@ObsessiveFOSS I can't answer from experience, but it seems to be the point of LOW_PRIORITY that the rows are visible until no-one is reading them any more and the delete kicks in

ObsessiveFOSS

@JackDouglas so the point isn't delaying resource use(as it does take time to delete a row), but delaying removal of visibility?

Jack Douglas

@ObsessiveFOSS that's how I read it

but you can test it out to be sure?

ObsessiveFOSS

Jul 25, 2012 14:44

@JackDouglas I'm developing a PHP script so I don't have a large database or enough load to test. Sorry.

DTest

Sounds like a good question on the stack :)

Jack Douglas

@ObsessiveFOSS I'd asked on the main site then - you ok to do that? Including this bit:

4 mins ago, by ObsessiveFOSS

@JackDouglas so the point isn't delaying resource use(as it does take time to delete a row), but delaying removal of visibility?

DTest

Never used it personally, but I'm fairly sure it just delays the acquisition of the table lock. Once it has the lock it runs the delete as normal.

ObsessiveFOSS

Sure...

Sorry, a bit of confusion: @JackDouglas are you asking or shall I write the question?

jokerdino

Where is the hypnotic mullet again??!

Jack Douglas

Jul 25, 2012 14:47

@ObsessiveFOSS I think it's a good question for the main site - ask away...

gbn

@jokerdino ta da

jokerdino

@gbn heya!

gbn

@jokerdino quickmeme.com/meme/35v8in

jokerdino

As you command, your Highness.

gbn

Credit to @ConcernedOfTunbridgeWells for that, many moons ago

jokerdino

Jul 25, 2012 14:51

It was funny. I was hoping mullet.se gets redirected to dba.se :p

ConcernedOfTunbridgeWells

@jokerdino Suggested here.

Q: Can we get mullet.stackexchange.com aliased to dba.stackexchange.com?

In line with @gbn's status as an unofficial dba.se mascot, would it be possible to get mullet.stackexchange.com aliased to dba.stackexchange.com?

feature-request

gbn

@jokerdino as we noted, this means we could have goat.se as an alias for someting which simply wouldn't do...

ObsessiveFOSS

@JackDouglas Added.

jokerdino

@ConcernedOfTunbridgeWells well yes. I ran into that request last night. :-)

ObsessiveFOSS

Q: DELETE LOW_PRIORITY row visibility

I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and actually delete the rows from disk when no clients are accessing the table, or is the point delay...

mysql-5.5 locking delete

jokerdino

Jul 25, 2012 14:55

@gbn I did get the joke.. It was a nice time pass for most of us.

Jack Douglas

Jul 25, 2012 15:11

@ObsessiveFOSS is my answer sufficient proof?

ObsessiveFOSS

@JackDouglas Yes, it helps. Accepted.

DTest

@JackDouglas They're only visibile because the delete hasn't acquired the lock yet so nothing has been issued.

ObsessiveFOSS

@JackDouglas Is there a way to remove visibility right away but then physically delete when the disk/CPU/etc. is idle?

Jack Douglas

@ObsessiveFOSS you have a performance problem with deletes?

jokerdino

He has a psychological problem with being obsessed.

Jack Douglas

Jul 25, 2012 15:18

@DTest exactly. always good to check with MySQL though :-)

jokerdino

(just jk)

(don't boot me out)

Jack Douglas

@jokerdino you know each other from AskUbuntu?

ObsessiveFOSS

@JackDouglas Yes, @jokerdino is right, but the application will be under heavy load, and the deletes are from fulltext indices.

jokerdino

@JackDouglas Quite a bit.

DTest

@ObsessiveFOSS don't release until innodb gets Fulltext search! (5.6)

jokerdino

Jul 25, 2012 15:20

okay. Mysql is beyond my area. I'll leave before I faint. :-)

ObsessiveFOSS

@DTest I, for some odd reason, can't get MySQL5.6 to even start cleanly, even after a typical installation. I have gone as far as reinstalling Windows. I guess I'll have to wait for a stable version. 5.5 works really well, though.

DTest

@ObsessiveFOSS oh lord. I'm stepping away from windows installs :) but yeah, 5.6 isn't GA yet

but without using another solution like Sphinx for fulltext searches, you're stuck with MyISAM in 5.5...and that comes with table locks at some point.

Ben Brocka

weee waiting for a 15GB database to restore

(dumping new data for test, I didn't break anything)

DTest

there's not a way to flag rows to delete and remove those rows later when no one is using them (row-locking) in a table-locking engine.

ObsessiveFOSS

@DTest Does InnoDB need any weird config to get row-level locking, and will it flag and make invisible now, delete later?

Jack Douglas

Jul 25, 2012 15:26

@ObsessiveFOSS what it doesn't do is lock the whole table - you don't usually need to know what goes on under the bonnet

ObsessiveFOSS

@JackDouglas So it's by default? And it'll hide rows now, physically delete when idle?

DTest

@ObsessiveFOSS InnoDB is row-level locking, no config needed. You can configure it to get 'dirty reads' (ie, be able to read rows that are locked to be deleted before the delete has finished).

but no FTS on innodb in 5.5

ObsessiveFOSS

OK, thanks!

Conversation ended Jul 25, 2012 at 15:27.

View all rooms

Jul '1225

General on- and off-site discussion for dba.stackexchange.com....

context of the conversation
join this room
about this room

Participants

ObsessiveFOSS 28%
Jack Douglas 21%
jokerdino 19%
DTest 16%
gbn 7%
ConcernedOfTunbridgeWells 3%
Ben Brocka 3%

all times are UTC

Row-level locknig discussion

The Heap™ – Consultancy ©®

Participants