使用索引列和非索引列更新来自不同事务的数据有什么区别?
我之前使用交易时遇到了一些问题。希望有人能帮我弄清楚。我将不胜感激任何帮助。谢谢。
MySql表结构:
create table test (
id int not null,
someid int,
name varchar(50),
update_date datetime
);
primary key : id (auto-inc)
index1 : id (unique)
index2 : id, update_date (non-unique)
java方法:
// consider this method is Transaction 1
method1() {
A. set session transaction isolation level read commited;
B. select update_date from test where someid = 1;
C. insert into test values (some new data..);
D. select update_date from test where someid = 1;
}
// consider this method is Transaction 2
methodb() {
E. (start with default transaction isolation level - repeatable read)
F. update test set udpate_date = now() where someid = 1;
}
这就是我所做的:
- 时中断(在eclipse中设置断点
- 执行method1()并在D执行method2()并发
)请注意,“someid”不在索引中,但它完全存储相同的数据就像“id”一样。
然后,只要我不提交事务,我就只能等待,否则最终会导致事务超时。但是如果我将 where 子句更改为 F 的 id = 1,它将正常工作,无需任何等待。在这里我很困惑,因为我没有锁定该表或任何行。如果我这样做了,就不应该这样做,对吗?
谁能告诉我为什么会这样?谢谢你!
I got some issue when i was using transaction earlier. Hope someone can help me figure it out. I'll appreciate any help. Thank you.
MySql table structure:
create table test (
id int not null,
someid int,
name varchar(50),
update_date datetime
);
primary key : id (auto-inc)
index1 : id (unique)
index2 : id, update_date (non-unique)
java method :
// consider this method is Transaction 1
method1() {
A. set session transaction isolation level read commited;
B. select update_date from test where someid = 1;
C. insert into test values (some new data..);
D. select update_date from test where someid = 1;
}
// consider this method is Transaction 2
methodb() {
E. (start with default transaction isolation level - repeatable read)
F. update test set udpate_date = now() where someid = 1;
}
Here is what i did :
- execute method1() and break (set break point in eclipse) at D
- execute method2() concurrent
Note that "someid" is not in the index, but it store same data exactly as "id" does.
Then i got nothing but waiting as long as i don't commit the transaction1 or finally it will end up with transaction time-out. But if i changed where clause to id = 1 of F, it will work just fine without any waitting. Here i got confused, because i didn't lock that table or any rows. And if i did, it should not be done, right?
Can anybody tell me why this happed? Thank you!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
条件someId=1需要数据库系统扫描全表,因为没有索引。 insert 语句可以插入 someid=1 的行,从而影响第二个事务的结果。
当 id=1 时,数据库可以确定只有一行受到语句 F 的影响。insert 语句不会更改这一行。
我只是想知道,隔离级别是否会影响执行?
The condition someId=1 requires the database system to scan the full table because there is no index. The insert statement could insert a row with someid=1 and therefore influence the result of the second transaction.
With where id=1 the dbs can determine that only one row is affected by the statment F. The insert statment does not change this row.
I am just wondering, whether the isolation level affect the execution?
我不是 MySQL 并发控制方面的专家,但我猜您正在看到锁定的效果:在第一个事务提交之前,第二个事务无法知道第一个事务插入的行是否会被提交,因此必须停滞了。
只有在第一个事务完成后,第二个事务才能继续,并且:
问题是:为什么索引的 id 也没有发生这种情况?您确定两次交易使用的值与
someid
相同吗?您是否更改了两个事务的WHERE
子句?I'm no expert on MySQL concurrency control, but I'm guessing you are seeing the effects of locking: until the first transaction commits, the second one cannot know whether the row inserted by the first transaction will even be committed, so must be stalled.
Only after the first transaction finishes can the second transaction continue and either:
The question is: why didn't it happen for the indexed
id
as well? Are you sure you used the same value for both transaction as you did withsomeid
? Did you change theWHERE
clause for both transactions?