Whats all this MySQL stuff about row locking etc?

Anon

Hi, i was just reading on slashdot about "row level locking" planned for MySQL? http://slashdot.org/article.pl?sid=00/10/30/1553209&mode=thread

Anyway, it talks about mysql will be getting this row locking thing.. my questions are:

What is this "row level locking", what does it do better for me? what is there currently, is there any other special syntax i will have to use to use it?

also, what are "transactions"?.. someone on the thread was going on about how mysql doesnt have transactions??

what is all this stuff?

thanx

David

Anon

If you look at a few more articles, you'll get a better idea. Basically, MySQL uses table level locking. When it does a write, it locks the table so nothing else can write to it. Row level locking would lock one row only, other processes can still write to the other rows. If you do a lot of writing to your database, then row level locking will help you, otherwise you probably won't notice a difference.

Transactions: From what I understand, you start a transaction, then execute a number of inserts or updates, etc... then end the transaction. The value is that if along the line, one of the inserts fails, you can cancel the transaction and none of them will take effect.

That's what I understand, at least. I could be completely wrong. 🙂 There are a lot of articles out there explaining it in more detail.

It depends on how large your site is, how much data you have, and how much traffic you get on whether either of these will help or hurt you.

---John Holmes...

Anon

Getting quite off-tropic here, but it's interesting info.
If I make a mistake in my explenation I'm sure the helpfull experts will correct me.

Row level locking is locking at row level :-)

It means you can set access restrictions per row of a table.
This means that if you have for example a huge query that takes several minutes to complete, the records that you are using can be locked so that nobody can change them while you are running your query.

This is an alternative for table-level-locking, where the whole table gets locked during your query.

Transactions are linked to "rollback" and "commit" (and rollforward but that is not really relevant here I think)
Rollback is basically an UNDO function for sql queries.
(Rollforward is a REDO)
Commit is answering YES to "are you sure?"

If you want to be able to UNDO, you need to define how much you want to undo, how far back you want to go.

This is done using transactions.
You start a transaction with the SQL statement BEGIN WORK.
This marks the point to where you can rollback ("undo").
Next you run your queries.
If you decide you did the wrong query you can give a "rollback" command.
This ends your transaction and "undoes" all the queries you execute after the BEGIN WORK.

If you are happy with your queries and do not want to rollback, you give a COMMIT.
This also ends your transaction. After a commit you cannot rollback!