MyISAM 仅选择锁定过程内的插入

发布于 2024-10-04 22:59:37 字数 1810 浏览 4 评论 0原文

我们有一个大型 MyISAM 表，其中的行仅插入到表的底部。

在进行一些基准测试时，我意识到选择并不（总是）将其他插入锁定到同一个表。但是，当插入来自存储过程/函数时，它们将被选择锁定。

这是为什么？

要演示此行为：

CREATE TABLE Foo (
   ID INT NOT NULL AUTO_INCREMENT,
   Bar VARCHAR(200),
   PRIMARY KEY(ID)) ENGINE=MyISAM;

--INSERT into Foo 10M rows


DELIMITER $$

DROP PROCEDURE IF EXISTS InsertProc$$

CREATE PROCEDURE InsertProc(IN vBar VARCHAR(255))
BEGIN
    INSERT Foo(Bar) VALUES (vBar);
END$$

DELIMITER ;

运行以下查询：

SELECT Count(*) FROM Foo WHERE INSTR(Bar, 'abcdefg') > 0;

当该 Select 运行时，打开一个新连接并运行以下插入查询：

INSERT Foo(Bar) VALUES ('xyz1234');

该插入将立即运行并返回，但是如果我运行以下查询：

CALL InsertProc('xyz1234');

现在查询将锁定并等待以便选择完成。

MySql 版本：5.0.51 在 Window Server 2K3 上运行

谢谢。

- 更新以下是配置文件输出：

直接插入：

(initialization)     0.0000432
checking permissions 0.0000074
Opening tables       0.0000077
System lock          0.0000032
Table lock           0.0000025
init                 0.000021
update               0.0002365
end                  0.0000382
query end            0.000002
freeing items        0.0000057
closing tables       0.0000022
logging slow query   0.0000005

通过过程插入：

(initialization) 0.0000285
Opening tables   0.0004325
System lock      0.0000022
Table lock       0.0002957
checking permissions 0.0000047
Opening tables   0.000004
System lock      0.0000017
Table lock       3.2365122
init             0.0000422
update           0.000251
end              0.0000025
query end        0.000003
closing tables   0.00004
query end        0.0000074
freeing items    0.0000074
logging slow query 0.000001
cleaning up      0.5790915

为什么过程会打开并“表锁定”两次？

原文

We have a large MyISAM table to which rows get inserted to the bottom of the table only.

While doing some benchmarks, i realized that selects do not (always) lock other inserts to that same table. However, when the inserts are coming from a stored procedure/function they will by locked by the select.

Why is that?

To demonstrate this behavior:

CREATE TABLE Foo (
   ID INT NOT NULL AUTO_INCREMENT,
   Bar VARCHAR(200),
   PRIMARY KEY(ID)) ENGINE=MyISAM;

--INSERT into Foo 10M rows


DELIMITER $

DROP PROCEDURE IF EXISTS InsertProc$

CREATE PROCEDURE InsertProc(IN vBar VARCHAR(255))
BEGIN
    INSERT Foo(Bar) VALUES (vBar);
END$

DELIMITER ;

Run the following query:

SELECT Count(*) FROM Foo WHERE INSTR(Bar, 'abcdefg') > 0;

While that Select is running, open a new connection and run the following insert query:

INSERT Foo(Bar) VALUES ('xyz1234');

That Insert will run and return right away, However if i run the following query:

CALL InsertProc('xyz1234');

Now the query locks and waits for the select to complete.

MySql Version: 5.0.51 running on Window Server 2K3

Thank you.

-- UPDATE
Here is the profile output:

Insert Direct:

(initialization)     0.0000432
checking permissions 0.0000074
Opening tables       0.0000077
System lock          0.0000032
Table lock           0.0000025
init                 0.000021
update               0.0002365
end                  0.0000382
query end            0.000002
freeing items        0.0000057
closing tables       0.0000022
logging slow query   0.0000005

Insert via Procedure:

(initialization) 0.0000285
Opening tables   0.0004325
System lock      0.0000022
Table lock       0.0002957
checking permissions 0.0000047
Opening tables   0.000004
System lock      0.0000017
Table lock       3.2365122
init             0.0000422
update           0.000251
end              0.0000025
query end        0.000003
closing tables   0.00004
query end        0.0000074
freeing items    0.0000074
logging slow query 0.000001
cleaning up      0.5790915

Why does the procedure open and "Table lock" twice?

分享到QQ

分享到微博