← Back to team overview

maria-developers team mailing list archive

Re: doubt about read uncommited


----- Original Message -----
> hi Daniel!
> 2014-11-25 1:03 GMT-02:00 Daniel Black <daniel.black@xxxxxxxxxxxxx>:
> >
> >
> > ----- Original Message -----
> > > hi guys, i use read uncommited sometimes with innodb, that's nice and
> > work
> >
> > too scared to ask why. Its nice until the one day it shoots you.
> >
> no problem, i use with data that don't need fully consistency
> but this problem isn't my design, that's why i'm searching some solution
> just some points to avoid thinking that i'm a newbie trying to use myisam
> as innodb cause it have better count(*) speed than innodb or anything like
> it...

You define a solution with so man doubts and questions rather than describing a problem. This will always make you look like a newbie.

> i like the innodb and myisam when right used, and yes innodb can do this
> job with transactions, i know isolation level "read uncommited" is for some
> transactional engine and not all engine can use it, that's engine specific
> i don't remember what engine don't allow this, but i read something about it

SQL spec - DB implementations can escalate the isolation level if they so choose.

> myisam and aria don't execute as transactional engine, they have a non
> transacitonal model, ok i know this, i'm testing what it can really do
> i know that's something that maybe the solution don't exists today with
> standard mysql/mariadb engines
> maybe there's another engine "plug and play", i only don't know what engine
> and how complex is implement this if no one solve, yes i don't know how
> complex is change this applicatoin since i don't have the source code... i
> will execute query rewrite
> forgetting the "solution" of 'use innodb instead of myisam'... (that's
> valid solution)
> there's space to implement some new feature to aria/myisam? i think aria is
> better in this case cause it have jornalling, or maybe any other solution?

a solution to what? you haven't defined the problem.

> the table don't have many update/delete and writes are concurrent inserts,
> it's like a historical table
> i don't have access to source code of application sending the queries, i
> will implement something between client and mariadb to rewrite the queries,
> i know exactly what query must execute in this kind of "feature" since app
> send comments telling what 'module' is executing the query, something like
> "/* module=1242 */ SELECT ...."

So you want MariaDB devs to rewrite the database because you cant' improve the application?

> > > but now, i'm using a myisam table, and a aria table....
> > > does read uncommited work with this kind of engine?
> >
> > There is never any uncommitted as a table lock is held when writing
> > occurs. Reads wait until that finishes.
> >
> nice, confirmed with you too :)
> that was a doubt, since transactional level is realated to transactional
> engine

This of it as always transactional isolation level with no rollback or transactions.

>, i never tested if it could change something of non trasactional
> engines, a idiot doubt i know... but only testing to really know 100% if
> this change something, reading the docs or reading the myisam/aria source
> code, the easier method was testing... =]
> that's probably expected
> from what i know about myisam/aria, a table write lock block the table read
> lock (i can check this with meta data lock plugin), and read only execute
> after release of write lock, some writes can execute "without" "big" locks,
> like concurrent inserts, right?
> but instead of a read lock waiting write locks, i'm thinking about a one
> new "level of lock" inside myisam (or aria), i don't know how to name this
> "level of lock" using the myisam/aria words, with transactional engines
> that's a transactional isolation level
> no problem of slowing down the update query or the select query or the
> whole table read/write, and no problem about reading garbage (uncommited
> data) by select (that's expected to result "old" data)
> i don't know 100% how write works with myisam/aria, but at a high level i
> think the only problem is reading one row while write is being executed,
> probably this need a row level locking or a 'range level lock' or a jornal
> method (like aria), but i'm thinking if some kind of non transactional
> model could be used to read uncommited data, i will try to run 2 or more
> mysqld with same mysql table and check if any problem occur, probably yes...
> check that i don't care about transaction in this case, transaction solve
> the problem of course, but i'm thinking if there's a non transactional
> solution, and if exists how complex could be implement it, cause i didn't
> checked yet how complex is rewriting the whole queries from app to run with
> transactions, ideas and experiences with this are wellcome
> >
> > > i tested and table stay
> > > 'waiting table lock' while a looooong update occurs
> >
> > Right (+write) - as above.
> >
> yeap, the normal "problem" (solution) of write lock
> what i didn't tested before is the transactional level with non
> transactional tables... the "dumb doubt"
> >
> > > i don't know what's the internal diference but is possible to "easily"
> > > implement read uncommited to myisam/aria?
> >
> > It is possible to change to easily change to innodb
> i don't have the source code of app, probably i could execute a "proxy"
> between client and server to rewrite and implement begin/commit, and
> execute single statistics that myisam have (max/min/count) with triggers
> and statistics tables, rewrite some queries, etc...
> but this is a bit "complex", i don't know how complex it is yet, i didn't
> tested and didn't tried to convert a whole system without changing source
> code
> i know that i'm with a design problem, probably the 'easily' solution is
> contacting the developer

Given you can't describe the problem to us contacting the developer won't help either.

Apart from some vague notions of too much time waiting in locks and needing quicker counts I'm still clueless about what you need.

Be simple, concise and avoid elaboration on aspects untested/unknown.

> , an actually transactional storage engine, for the transaction features
> > you want to use?
> >
> well... i don't know if this need all transactional features of innodb, i
> didn't checked yet if the row length could break innodb engine, and i
> should partition this table and create a view or a vertical partition table
> with spider
> i was thinking about ACID:
> * Atomicity - i don't need and myisam/aria don't have (aria "partially
> atomicity")
> * Consistency - no need... only unique keys check
> * Isolation - that's my doubt
> today myisam serialize writes that's a lock method, not a isolat*ion method
> with read it do a 'uncommited' (execute reads with locks), it's a "lock
> oriented" engine
> what i'm thinking is write execute normally as myisam/aria do, only select
> with read uncommited could read while writing, others selects should wait
> table write lock as normal
> i don't know how complex, but maybe the simple solutions is a read that
> change writes to wait or something like this, i really don't know yet
> what's the solution, just trying to check if any idea could be possible
> this will increase some lock contention or anyother contention (or maybe
> reduce it)... anything better than long write lock is ok
> * Durability - execute as myisam/aria do today
> i think aria have better support to this since aria have jornal log and
> some kind of multi versioning

You're making assumptions. Stop it.

> aria write to jornal, and after write to file, the file have the
> 'uncommited data' and the jornal+file have the "commited" data
> what i'm thinking with aria:
> 1) when executing this selects (uncommited) we could ensure that jornal be
> write to file before starting select
> 2) writing from jornal to file is blocked
> 3) when select end, the write from jornal to file is released
> well i don't know if this is possible, just an idea...
> maybe no other engine do this in this way (non transactional), i didn't
> checked yet nosql storages/plugins solutions, any idea?
> sorry many doubts and questions, i'm thinking about possible solutions

Think about the problem more than the solutions.

Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.

Follow ups