question about "Race condition on MySQL epic fail"

the following link is puzzling me.

http://computer.discuss.com.hk/v ... &extra=page%3D5

I capture one page from "Race condition on MySQL epic fail".
But I found the result is correct as "balance = -80",
Anyone can explain that to me?

附件 race.jpg (135.48 KB)

2014-6-24 10:00 PM

race.jpg (135.48 KB)

2014-6-24 10:00 PM

作者: form5 发布时间: 2014-07-21

>show create table user_balance;

'CREATE TABLE `user_balance` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `username` varchar(45) DEFAULT NULL,\n `balance` int(11) DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8'

作者: form5 发布时间: 2014-07-21

引用:原帖由 form5 於 2014-6-24 10:00 PM 发表
the following link is puzzling me.

http://computer.discuss.com.hk/viewthread.php?tid=23107538&extra=page%3D5

I capture one page from "Race condition on MySQL epic fail".
But I found the result ...

Related documentation:
http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html
Isolation level "REPEATABLE READ" is ok, but the select statement is wrong.
"SELECT ... FOR UPDATE" should be used instead of plain "SELECT".
The first "SELECT ... FOR UPDATE" will block others until the corresponding transaction ends.

作者: xianrenb 发布时间: 2014-07-21

I think it want to say that the checking for non-negative balance fail....

And I'm not really familiar with MySQL in production environment. But if this isolation level is set or by default, Oracle would just do the same...

Probably need to read the context for why:

1. Why was repeatable read isolation level being used? What is the default?
2. Why wasn't select....for update being used? Is that because it just want to demonstrate a long spanning transaction involved UI? Just not a perfect example or what?

I didn't read it thoroughly enough as I'm not familiar with MySQL that I won't be qualified to digest it....

引用:原帖由 form5 於 2014-6-24 22:00 发表
the following link is puzzling me.

http://computer.discuss.com.hk/viewthread.php?tid=23107538&extra=page%3D5

I capture one page from "Race condition on MySQL epic fail".
But I found the result ...

[ 本帖最后由 a8d7e8 於 2014-6-25 11:03 AM 编辑 ]

作者: a8d7e8 发布时间: 2014-07-21

you may want to put a "sleep" before the commit statement in "machine 1". say, 10 sec.

after having fired 1st set, then, within 10 sec fire 2nd set.

you will see the result.

作者: stevie1 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-25 10:54 AM 发表
I think it want to say that the checking for non-negative balance fail....

And I'm not really familiar with MySQL in production environment. But if this isolation level is set or by default, Oracle ...

The default isolation level should be "REPEATABLE READ", Oracle should be something like "READ COMMITTED".
I believe the only safe isolation level should be "SERIALIZABLE".
But with correct "SELECT ... FOR UPDATE" and "SELECT ... LOCK IN SHARE MODE" statements, "REPEATABLE READ" should act the same as in "SERIALIZABLE".

"SELECT ... FOR UPDATE" is just using write/exclusive lock for select.
"SELECT ... LOCK IN SHARE MODE" is just using read/shared lock for select.

When one wants to write/update records, he should use write lock, just like opening files in write mode.

作者: xianrenb 发布时间: 2014-07-21

Oh I didn't really try and rarely use repeatable read isolation level.

Perhaps repeatable read affects update as well?

such that when read get 100, update balance = balance - 90 would read as 100 too in Oracle.

Which isn't the case for MySQL so the presentation say MySQL fail......

引用:原帖由 a8d7e8 於 2014-6-25 10:54 发表
I think it want to say that the checking for non-negative balance fail....

And I'm not really familiar with MySQL in production environment. But if this isolation level is set or by default, Oracle ...

作者: a8d7e8 发布时间: 2014-07-21

Oh the presentation is to pro-PostgreSQL that it uses MVCC as the backbone for the isolation level that is highly efficient for high concurrency usage.

Shit I misread MVCC as application layer approach. That's why MVCC wasn't elaborated much as it's a feature of PostgreSQL.....

The MySQL epic fail is a major point to show how "bad" MySQL is comparing to PostgreSQL, even when performance is neglected.

作者: a8d7e8 发布时间: 2014-07-21

btw, did you use exactly the same version of MySQL as in the presentation?

引用:原帖由 form5 於 2014-6-24 22:00 发表
the following link is puzzling me.

http://computer.discuss.com.hk/viewthread.php?tid=23107538&extra=page%3D5

I capture one page from "Race condition on MySQL epic fail".
But I found the result ...

作者: a8d7e8 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-25 02:40 PM 发表
Oh the presentation is to pro-PostgreSQL that it uses MVCC as the backbone for the isolation level that is highly efficient for high concurrency usage.

Shit I misread MVCC as application layer appr ...

Some related web pages:
http://en.wikipedia.org/wiki/Multiversion_concurrency_control
http://docs.oracle.com/cd/E11882_01/server.112/e26088/ap_locks001.htm#SQLRF55506
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF01702
http://www.postgresql.org/docs/9.1/static/explicit-locking.html
http://www.postgresql.org/docs/9.0/static/sql-select.html

Based on what I skimmed through above, I would say the presentation was quite misleading.
MySQL InnoDB is with MVCC, and the example should use SELECT ... FOR UPDATE for that kind of application.

作者: xianrenb 发布时间: 2014-07-21

引用:原帖由 stevie1 於 2014-6-25 11:48 AM 发表
you may want to put a "sleep" before the commit statement in "machine 1". say, 10 sec.

after having fired 1st set, then, within 10 sec fire 2nd set.

you will see the result.

复制内容到剪贴板代码:set transaction isolation level repeatable read;
start transaction;

select balance from user_balance where username = 'Person X';
update user_balance set balance = balance - 90 where username = 'Person X';

select sleep(10);
commit;

right ? same result
anyway, the order of steps are clearly stated in the given picture,
if Machine B runs first, the order of commands in two given sessions is illustrated in this picture. 1->2->3->4->5->6->7->8->9->10

the result is the same as (2->1->4->3->6->5->7-> 8->9->10).

did you try that out before come to any conclusion?

[ 本帖最后由 form5 於 2014-6-25 10:48 PM 编辑 ]

附件 race_1.jpg (136.89 KB)

2014-6-25 10:06 PM

race_1.jpg (136.89 KB)

2014-6-25 10:06 PM

作者: form5 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-25 10:54 AM 发表
I think it want to say that the checking for non-negative balance fail....

And I'm not really familiar with MySQL in production environment. But if this isolation level is set or by default, Oracle ...

yep, part of my question is why using this particular example to demonstrate race condition?

作者: form5 发布时间: 2014-07-21

[quote]原帖由 a8d7e8 於 2014-6-25 02:29 PM 发表
Oh I didn't really try and rarely use repeatable read isolation level.

Perhaps repeatable read affects update as well?

such that when read get 100, update balance = balance - 90 would read as ... [/quote

]

if one replace the isolation level "serializable" here, "dead lock" happens

作者: form5 发布时间: 2014-07-21

引用:原帖由 xianrenb 於 2014-6-25 12:12 PM 发表
"SELECT ... FOR UPDATE" is just using write/exclusive lock for select.
"SELECT ... LOCK IN SHARE MODE" is just using read/shared lock for select.

I think I would go for exclusive locking

作者: form5 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-25 02:41 PM 发表
btw, did you use exactly the same version of MySQL as in the presentation?

nope, hehe, because I think the same result are expected here, even in mysql 5.6 .

作者: form5 发布时间: 2014-07-21

引用:原帖由 form5 於 2014-6-25 10:17 PM 发表
[quote]原帖由 a8d7e8 於 2014-6-25 02:29 PM 发表

Oh I didn't really try and rarely use repeatable read isolation level.

Perhaps repeatable r ...

Yes, if plain SELECT as in the example and "SERIALIZABLE" isolation level is used, I believe deadlock could happen.
The plain SELECT statement would act as "SELECT ... LOCK IN SHARE MODE", meaning shared lock would be used, while the UPDATE statement uses exclusive lock.
Two machines could both go through the shared lock, but get deadlock at the UPDATE statement.

作者: xianrenb 发布时间: 2014-07-21

removed

引用:原帖由 form5 於 2014-6-25 22:11 发表

yep, part of my question is why using this particular example to demonstrate race condition?

[ 本帖最后由 a8d7e8 於 2014-6-26 02:30 PM 编辑 ]

作者: a8d7e8 发布时间: 2014-07-21

Ok lets focus on the "Today will cover" pts:

1. MVCC vs traditional READ-WRITE lock isolation.
2. How good is MVCC vs traditional RDBMS in different isolation level.
3. MySQL MVCC fails, whereas PostgreSQL is not.

For (1),

Comparison matrix of different isolation level for traditional RDBMS vs MVCC:

Read committed: [Write lock] vs [Write lock]
Repeatable read: [read lock until end of TX] vs [(no read lock), (write lock or update exception)]
Serialiable: [lock all] vs [repeatable read + monitor, highly cpu intensive]

Isolation level facts:

i) MySQL defaults in repeatable read isolation level.
ii) Oracle defaults in read committed isolation level.
iii) Oracle has no repeatable read, whereas the author refer to serialiable instead.

Then, for (2), we have:

There is only obvious advantage in repeatable read since there is no read lock under MVCC approach.

And then (3),

MySQL will finish the race condition. balance -> -80
Oracle(in Serializable mode) will not permit the second update as the read in tx2 blocks until tx1 finish. balance -> 10 (one tx follow application logic to not update)
PostgreSQL, "In WRITE, if target rows have timestamp > start of current TX, exception will be raised.". balance -> 10 (one tx will finish with exception!)

In conclusion, MVCC(PostgreSQL) fails when conflict is found rather than using lock to prevent the conflict which is supreme in high concurrency environment.

ie. No need to use select....for update at all, just let the race condition fail and handle that from application. Well kinda like optimistic locking in ORM..

[ 本帖最后由 a8d7e8 於 2014-6-26 03:19 PM 编辑 ]

作者: a8d7e8 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-26 03:16 PM 发表
Ok lets focus on the "Today will cover" pts:

1. MVCC vs traditional READ-WRITE lock isolation.
2. How good is MVCC vs traditional RDBMS in different isolation level.
3. MySQL MVCC fails, whereas ...

I believe the default isolation level in MYSQL would have no lock for plain SELECT statement.
The race condition would have no deadlock as only the UPDATE statement uses exclusive lock.
In other words, the balance could go negative even if the application checks it before issuing the UPDATE statement.
To make it working correctly, "SELECT ... FOR UPDATE" should be used no matter what the isolation level is.
It would make sure that all other transactions that would update the balance end, before continuing the process (checking the balance amount).

I believe another method that does not require using "SELECT ... FOR UPDATE" is to list all the related/expected values in the WHERE clause of the UPDATE statement.
Such usage should be similar to SQL statements without transaction.
Update would only occur when there is no change between the SELECT and UPDATE statements

作者: xianrenb 发布时间: 2014-07-21

Oh I just found that I've PostgreSQL 9.3 installed.

Maybe I run the epic fail in pg to shows the spirit of the author:

-- 1. open 2 sessions

-- 2. Run this in any session
create table user_balance (
name text,
balance integer
);

insert into user_balance values ('test', 100);
---

-- 3. Run this in session 1
begin;
set transaction isolation level repeatable read;

select * from user_balance where name = 'test';
update user_balance set balance = balance - 90 where name = 'test';
--

-- 4. Run this in session 2
begin;
set transaction isolation level repeatable read;

select * from user_balance where name = 'test';
update user_balance set balance = balance - 90 where name = 'test';
-- session 2 blocks here

-- 5. Run this in session 1
commit;
--

-- 6. In session 2
-- ERROR: could not serialize access due to concurrent update
commit;
-- result in ROLLBACK

引用:原帖由 xianrenb 於 2014-6-26 17:34 发表

I believe the default isolation level in MYSQL would have no lock for plain SELECT statement.
The race condition would have no deadlock as only the UPDATE statement uses exclusive lock.
In other ...

作者: a8d7e8 发布时间: 2014-07-21

PS. PostgreSQL uses the same default isolation level, which is read committed, as Oracle. And in psql(the sql client of pg), by default it's auto-commit....need the begin; statement to start a transaction.

So the spirits are:

1. The author prefers repeatable read isolation level on PostgreSQL over all other RDBMS and isolation level combinations;
2. The MVCC implementation in PostgreSQL for repeatable read isolation level is a little bit different from that of MySQL that making the choice of MySQL a fail;
3. PostgreSQL is seemingly the best choice for supporting high concurrency application that needs ACID as well.

Perhaps there're more reasons to choose PostgreSQL among others but just this point is enough for an internet service based start-up whereas high concurrency is the most critical non-functional requirement.

作者: a8d7e8 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-26 06:22 PM 发表
Oh I just found that I've PostgreSQL 9.3 installed.

Maybe I run the epic fail in pg to shows the spirit of the author:
-- 1. open 2 sessions
-- 2. Run this in any session
create table user_b ...

you know what, shit happens again with "select * for update" under "repeatable read" in mysql

--------------set up--------------
create table `user_balance` (
`name` text,
`balance` int(11) DEFAULT NULL
) engine = InnoDB;

insert into user_balance values ('test', 100);

SELECT * FROM user_balance;

step 1.
------------- session 1-----------------
set transaction isolation level repeatable read;

start transaction;

select balance from user_balance where name = 'test' for update;
update user_balance set balance = balance - 90 where name = 'test';

step 2.
----------------session 2-----------------------------
set transaction isolation level repeatable read;

start transaction;

select balance from user_balance where name = 'test' for update;
update user_balance set balance = balance - 90 where name = 'test';

step 3
---------------session 1-----------------
commit;

step 4
---------------session 2-----------------
commit;

result => balance = -80, "select ... for update" still fails.

作者: form5 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-26 06:22 PM 发表
-- 3. Run this in session 1
begin;
set transaction isolation level repeatable read;

select * from user_balance where name = 'test';
update user_balance set balance = balance - 90 where name = 'test';
--

-- 4. Run this in session 2
begin;
set transaction isolation level repeatable read;

select * from user_balance where name = 'test';
update user_balance set balance = balance - 90 where name = 'test';
-- session 2 blocks here

-- 5. Run this in session 1
commit;
--

Should step 5: commit the tx1 move to step 3? As the example show that the tx1 is committed before tx2 begin to update

作者: ceap2003 发布时间: 2014-07-21

I believe some of us may have missed the point that "return 100 and thus pass the checking".
The application has to check the balance value before continuing.
I guess it is to check whether the value is greater than 90.
Correct design should have no deadlock.
One could design a correct locking sequence for a transaction.
I believe one would consider only using exclusive lock(s) or only using shared lock(s) (hence no UPDATE) within a single transaction as a best practice.

[ 本帖最后由 xianrenb 於 2014-6-27 09:50 AM 编辑 ]

作者: xianrenb 发布时间: 2014-07-21

Sorry my mistake but the result is the same except that tx2 didn't block and fail instantly.

From this I can feel the powerful "feature" of PostgreSQL's MVCC backed repeatable read isolation level that the PL/pgSQL developer need no special concurrent mindset but just exception handling during update.

引用:原帖由 ceap2003 於 2014-6-27 00:46 发表
Should step 5: commit the tx1 move to step 3? As the example show that the tx1 is committed before tx2 begin to update

http://n2.hk/d/attachments/day_140624/20140624_b3973f7fb163d6e311abibTrV ...

作者: a8d7e8 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-27 11:20 AM 发表
Sorry my mistake but the result is the same except that tx2 didn't block and fail instantly.

From this I can feel the powerful "feature" of PostgreSQL's MVCC backed repeatable read isolation level ...

I believe no matter whether one uses MySQL (InnoDB), PostgreSQL or Oracle, all of them are with MVCC, and "SELECT ... FOR UPDATE" or similar statement should be used in order to use exclusive lock.
Without proper locking, the transaction may not process in correct sequence.

作者: xianrenb 发布时间: 2014-07-21

引用:原帖由 form5 於 2014-6-25 10:05 PM 发表
set transaction isolation level repeatable read;
start transaction;

select balance from user_balance where username = 'Person X';
update user_balance set balance = balance - 90 where username = ...

"SELECT ... FOR UPDATE" (exclusive lock) should be used instead of plain SELECT.
By doing so, the sequence must be 6->7->8->5->9->10(or 5->9->10->6->7->8), because 5 must wait 8 to finish.
And "SELECT ... FOR UPDATE" in 5 must report the correct balance value after the first transaction, and hence the operation must be correct (actually the application should decide not to run the second UPDATE) and with no deadlock.

[ 本帖最后由 xianrenb 於 2014-6-27 11:48 AM 编辑 ]

作者: xianrenb 发布时间: 2014-07-21

Yes all are with MVCC but among MySQL (InnoDB), PostgreSQL and Oracle, only the repeatable read isolation level of PostgreSQL allows plain select(in contra to select for update), which is lock-free, whilst ensuring no race condition could happen.

So it means MySQL (InnoDB) and Oracle uses MVCC for the snapshot feature only whilst PostgreSQL uses MVCC for concurrent write checking as well under repeatable read isolation level.

The keys are:

1. No need to use select for update in PostgreSQL under repeatable read isolation level.
2. Need to use select for update in MySQL (InnoDB) and Oracle as you suggest.

引用:原帖由 xianrenb 於 2014-6-27 11:36 发表

I believe no matter whether one uses MySQL (InnoDB), PostgreSQL or Oracle, all of them are with MVCC, and "SELECT ... FOR UPDATE" or similar statement should be used in order to use exclusive lock ...

作者: a8d7e8 发布时间: 2014-07-21

引用:原帖由 a8d7e8 於 2014-6-27 11:20 AM 发表
Sorry my mistake but the result is the same except that tx2 didn't block and fail instantly.

From this I can feel the powerful "feature" of PostgreSQL's MVCC backed repeatable read isolation level ...

But if the tx2 didn't block and fail instantly, then the update statement of tx2 executed successfully? and the result will be same as MySQL (i.e. balance = -80) ?

Edit :　I have tried a8d7e8 example on postgreSQL 9.3.4, whether commit of tx1 in step 3 or step 5, the results are the same, that is as a8d7e8 said that the postgreSQL successfully keep the balance (=10 and tx2 raise error when try to execute the update statement) to be correct, without issue any lock statements explicitly

[ 本帖最后由 ceap2003 於 2014-6-27 02:01 PM 编辑 ]

作者: ceap2003 发布时间: 2014-07-21

Heh my bad English.

- It didn't block.
- It failed instantly.

I'm glad that you spot and reported my mistakes.

引用:原帖由 ceap2003 於 2014-6-27 12:53 发表

But if the tx2 didn't block and fail instantly, then the update statement of tx2 executed successfully? and the result will be same as MySQL (i.e. balance = -80) ?

Edit :　I have tried a8d7e8 ex ...

作者: a8d7e8 发布时间: 2014-07-21

Oh nearly missed this interesting test!!! Very informative, thanks!

It's real shit, as long as "repeatable read" is the default isolation level for mysql inno!!!!

I must be trapped then if I'm to use the same mindset as using Oracle, oh!EDIT: Oh again I type faster than think, yes there should be checking after select....for update......as 闲人 said. So it is the expected effect, even in read committed isolation level, for Oracle.

引用:原帖由 form5 於 2014-6-26 22:33 发表

you know what, shit happens again with "select * for update" under "repeatable read" in mysql

--------------set up--------------
create table `user_balance` (
`name` text,
`balance` ...

[ 本帖最后由 a8d7e8 於 2014-6-27 02:23 PM 编辑 ]

作者: a8d7e8 发布时间: 2014-07-21

for oracle, as xianrend said that, using select ... for update can keep correct balance as expected, but it must check the balance after select statement in tx2.
There is another way to do that is append "nowait" keyword at the end of select statement, i.e. select ... for update nowait,

-- section 1 (tx1)
select * from user_balance where name = 'test' for update nowait;

-- section 2 (tx2)
select * from user_balance where name = 'test' for update nowait; // fail immediately

作者: ceap2003 发布时间: 2014-07-21

引用:原帖由 xianrenb 於 2014-6-27 11:41 AM 发表
"SELECT ... FOR UPDATE" (exclusive lock) should be used instead of plain SELECT.
By doing so, the sequence must be 6->7->8->5->9->10(or 5->9->10->6->7->8), because 5 must wait 8 to finish.
And " ...

To be more specifically in both session 1 and session 2, I rewrite the steps in following table, this time use proper "select * for update".
- assume that isolation is repeatable read in mysql;
- initial balance is 100

time session1 session2 remarks t+1 start transaction; t+2 start transaction; t+3 SELECT balance FROM user_balance where username = 'Person X' for update; block other select balance ... for update t+5 SELECT balance FROM user_balance where username = 'Person X' for update;session 2, select is blocked t+6 update user_balance set balance = balance - 90 where username = 'Person X'; session 2, select is still blocked t+7 session 2, select is still blocked t+9 commit; session 2, select will run after "session 1, commit"
balance = 10 in session 2 t+10 ****-------check if balance is negative-------*** t+11 update user_balance set balance = balance - 90 where username = 'Person X';
t+12 commit; balance = -80

yes, exit condition should be checked after "select"( t+10), either in sql statements or in application, the original demonstration use "select *" without requesting X lock may not block others, "race condition" seems inevitable.

[ 本帖最后由 form5 於 2014-6-28 02:06 AM 编辑 ]

作者: form5 发布时间: 2014-07-21

我是Slide的作者。

有好多人会说为何不用select for update，我只想说：这epic fail是用来证明MySQL的MVCC本身是有bug的，违反了ISO SQL的定义

根据ISO SQL对repeatable read的定义

在transaction begin后read过的record，在整个transaction中都应该维持原本的value

所以User B他过了validation的select statement, 即使不用select for update这种lock promotion，都应该不会concurrency被user A改到还commit 成功的
PS: Oracle / PostgreSQL在User B commit时会出error. cannot preform serialiable transaction（大约吧，我忘了exact的message）

作者: TritonHo 发布时间: 2014-07-21

另外．．．．．．

http://opensource.hk/monthly-talks-2014-07
今次会玩RESTful API

作者: TritonHo 发布时间: 2014-07-21

引用:原帖由 TritonHo 於 2014-7-2 02:57 PM 发表
Oracle / PostgreSQL在User B commit时会出error. cannot preform serialiable transaction（大约吧，我忘了exact的message）

for oralce, 当 transaction isolation level set to serializable 才会出 error? oracle 没有 repeatable read isolation level, 只有 read only mode, read only mode 是 repeatable read 的 (default read committed 则不是)，但 read only mode 下不可执行 insert, update, delete 等动作，不能执行 slide 中的例子，所以用 oracle 做比较好似并唔系咁适合。要比较就大家同是 repeatable read 或 serializable isolation level下比较才有意义。

作者: ceap2003 发布时间: 2014-07-21

Oracle的serializable tx是挡唔住phantom read的

Oracle claim自己挡phantom read只是让自己tx睇唔到phantom rows,不等於phantom rows不能被加入tables.

所以会出write skew这种race condition:
http://en.wikipedia.org/wiki/Sna ... _Snapshot_Isolation

所以oracle claim的serializable只是真正身份只是repeatable read．
（ＰＳ：PostgreSQL的repeatable read正正等於oracle的serializable，而postgreSQL的serializable是在repeatable read之上再开predicate monitering去挡phantom rows）

作者: TritonHo 发布时间: 2014-07-21

学野了

引用:原帖由 TritonHo 於 2014-7-2 16:04 发表
Oracle的serializable tx是挡唔住phantom read的

Oracle claim自己挡phantom read只是让自己tx睇唔到phantom rows,不等於phantom rows不能被加入tables.

所以会出write skew这种race condition:
http://en.wi ...

作者: a8d7e8 发布时间: 2014-07-21

Can u share the slide as before as I'm afraid I cannot attend the talk....regretfully

引用:原帖由 TritonHo 於 2014-7-2 14:59 发表
另外．．．．．．

http://opensource.hk/monthly-talks-2014-07
今次会玩RESTful API

[ 本帖最后由 a8d7e8 於 2014-7-2 08:39 PM 编辑 ]

作者: a8d7e8 发布时间: 2014-07-21

引用:原帖由 TritonHo 於 2014-7-2 02:57 PM 发表
我是Slide的作者。

有好多人会说为何不用select for update，我只想说：这epic fail是用来证明MySQL的MVCC本身是有bug的，违反了ISO SQL的定义

根据ISO SQL对repeatable read的定义

在transaction begin后r ...

Just try and taste postgresql, it raises error instantly, I like the way it is

复制内容到剪贴板代码:ERROR: could not serialize access due to concurrent update********** Error **********
ERROR: could not serialize access due to concurrent updateSQL state: 40001

作者: form5 发布时间: 2014-07-21

引用:原帖由 TritonHo 於 2014-7-2 04:04 PM 发表
Oracle的serializable tx是挡唔住phantom read的

Oracle claim自己挡phantom read只是让自己tx睇唔到phantom rows,不等於phantom rows不能被加入tables.

所以会出write skew这种race condition:
http://en.wi ...

你的 slide 中 epic fail 是 write skew 其中一个例子吧? oracle in serialization isolation level, user 2 commit 时不是出 error 防止 write skew 发生吗?

for MySQL, the below pages show that InnoDB will detect deadlook and abort the involved transactions 防止 write skew:http://www.evanjones.ca/db-isolation-semantics.html

for verify, 我自己也试了一次，结果如以上pages 所述

附件 mysql_test_user1.png (15.06 KB)

2014-7-2 10:09 PM

mysql_test_user2.png (18.28 KB)

2014-7-2 10:09 PM

mysql_test_user1.png (15.06 KB)

2014-7-2 10:09 PM

mysql_test_user2.png (18.28 KB)

2014-7-2 10:09 PM

作者: ceap2003 发布时间: 2014-07-21

引用:原帖由 TritonHo 於 2014-7-2 02:57 PM 发表
我是Slide的作者。

有好多人会说为何不用select for update，我只想说：这epic fail是用来证明MySQL的MVCC本身是有bug的，违反了ISO SQL的定义

根据ISO SQL对repeatable read的定义

在transaction begin后r ...

我认为 MVCC 只是一种 implementation 方法，大概只是 database 内部可以同时有多个 version 的 (record) data 而已。
其实应该与 locking 、 isolation level 无一定关系。
MySQL 、 PosgresQL 与 Oracle 都是 with MVCC 的。
#41 列出过的 http://www.evanjones.ca/db-isolation-semantics.html 及 http://www.postgresql.org/docs/9.1/static/transaction-iso.html 都指出 PosgresQL 的 default isolation level 是 Read Committed 。
Oracle 亦是类似。
如果真要比较，应该拿相同的 isolation level 出来。

而以我的理解，原 slide 中的 example 是 plain SELECT + UPDATE statements ，亦即是 non-locking read + locking write 。
以 PosgresQL 及 Oracle 的 isolation level 来看，应该是 no deadlock/error message 才对？

p.s.
或者按 documentation ， PosgresQL 与 Oracle 出现 deadlock/error message 亦是正常的。
http://www.postgresql.org/docs/9.1/static/explicit-locking.html
我估计 PosgesQL 中， plain SELECT 会取 ACCESS SHARE lock ， UPDATE 会取 ROW EXCLUSIVE lock 。
所以就不相容。
http://docs.oracle.com/cd/E11882_01/server.112/e26088/ap_locks001.htm#SQLRF55505
我估计 Oracle 中， plain SELECT 有取 RS lock ， UPDATE 有取 RX lock 。
所以就不相容。

[ 本帖最后由 xianrenb 於 2014-7-3 09:17 AM 编辑 ]

作者: xianrenb 发布时间: 2014-07-21

引用:原帖由 xianrenb 於 2014-7-3 09:01 AM 发表

我认为 MVCC 只是一种 implementation 方法，大概只是 database 内部可以同时有多个 version 的 (record) data 而已。
其实应该与 locking 、 isolation level 无一定关系。
MySQL 、 PosgresQL 与 Oracle 都是 ...

大概公道点说， slide 中的 example 是指出了 MySQL 与 PosgreQL 及 Oracle 不同的地方，而不是 MySQL fail 。
MySQL 之所以不同，以我看是因为它在 default isolation level 中有真正的 non-locking read 。
所以 example 中会完全没有 deadlock/error message 。
但其实以上述 example 的情况而言，正确／小心使用的话，在上述三款常见 database 中都应使用 SELECT ... FOR UPDATE 来选定适当的 locking 方法。
因为 UPDATE 是 write ，应用 exclusive 的 lock 。
相应的 SELECT (read) ，亦应选用 exclusive 的 lock ，而不是 shared 的。

作者: xianrenb 发布时间: 2014-07-21

引用:原帖由 xianrenb 於 2014-7-3 09:01 AM 发表
而以我的理解，原 slide 中的 example 是 plain SELECT + UPDATE statements ，亦即是 non-locking read + locking write 。
以 PosgresQL 及 Oracle 的 isolation level 来看，应该是 no deadlock/error message 才对？

oracle, mysql 在 read committed 和 repeatable read isolation level 都不会有 error message. 要在 serializable 时才有

PostgreSql 在 repeatable read 和 serializable isolation level 会有 error message。

有 error message 是合理的，因为假设 application 会在 select statement 之后会对取出来的 value 进行处理。但在处理的过程中，另一个 transaction 已经把资料更改了。所以 tx1 所做的处理可能是无意义的，故 error

作者: ceap2003 发布时间: 2014-07-21

引用:原帖由 xianrenb 於 2014-7-3 09:34 AM 发表

但其实以上述 example 的情况而言，正确／小心使用的话，在上述三款常见 database 中都应使用 SELECT ... FOR UPDATE 来选定适当的 locking 方法。

ANSI/SQL 定义 isolation level 的目的，大概是希望设定适当的 isolation level 之后可以使 programmer 减少对 explicit locking 的依赖吧。

作者: ceap2003 发布时间: 2014-07-21