blocks|key|340561|text|找到一篇标题为"MYSQL+WITH+NOLOCK“的文章|type|unstyled|depth|inlineStyleRanges|entityRanges|data|340562|https://web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx|offset|length|340563|在MS+SQL+Server中，您将执行以下操作：|340564|SELECT+*+FROM+TABLE_NAME+WITH+(nolock)|code-block|syntax|javascript|340565|MYSQL的等价物是|340566|SET+SESSION+TRANSACTION+ISOLATION+LEVEL+READ+UNCOMMITTED+;
SELECT+*+FROM+TABLE_NAME+;
SET+SESSION+TRANSACTION+ISOLATION+LEVEL+REPEATABLE+READ+;|340567|编辑|style|BOLD|340568|Michael+Mior建议了以下内容(来自评论)|340569|SET+TRANSACTION+ISOLATION+LEVEL+READ+UNCOMMITTED+;
SELECT+*+FROM+TABLE_NAME+;
COMMIT+;|340570|entityMap|0|LINK|mutability|MUTABLE|url|1|https://stackoverflow.com/users/123695/michael-mior^0|0|0|2N|0|0|0|0|0|0|0|2|0|0|C|1|0|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|18|8|@]|9|@$D|19|E|1A|1|1B]]|A|$]]|$1|F|3|G|5|6|7|1C|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|1D|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|1E|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1F|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1G|8|@$D|1H|E|1I|S|T]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1J|8|@]|9|@$D|1K|E|1L|1|1M]]|A|$]]|$1|W|3|X|5|J|7|1N|8|@]|9|@]|A|$K|L]]|$1|Y|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|C]]|15|$5|11|12|13|A|$14|16]]]]

Found an article titled "MYSQL WITH NOLOCK"

<a href="https://web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx" rel="noreferrer">https://web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx</a>

in MS SQL Server you would do the following:

<pre><code>SELECT * FROM TABLE_NAME WITH (nolock)
</code></pre>

and the MYSQL equivalent is

<pre><code>SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
</code></pre>

EDIT

<a href="https://stackoverflow.com/users/123695/michael-mior">Michael Mior</a> suggested the following (from the comments)

<pre><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;
</code></pre>

blocks|key|3110844|text|如果表是InnoDB，请参见http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html+--对于SELECTs“如果设置了innodb_locks_unsafe_for_binlog选项并且事务的隔离级别未设置为SERIALIZABLE，则不指定for+UPDATE或LOCK+IN+SHARE模式的SELECTs”，请参见SERIALIZABLE--+it+uses+-+read+(无锁定模式)。因此，从所选表读取的行上不会设置任何锁。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3110845|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html^0|E|1U|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

If the table is InnoDB, see <a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html</a> -- it uses consistent-read (no-locking mode) for SELECTs "that do not specify FOR UPDATE or LOCK IN SHARE MODE if the innodb_locks_unsafe_for_binlog option is set and the isolation level of the transaction is not set to SERIALIZABLE. Thus, no locks are set on rows read from the selected table".

blocks|key|3110857|text|使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3110858|SET+TRANSACTION+ISOLATION+LEVEL+READ+UNCOMMITTED.|offset|length|style|CODE|3110859|5.0版文档为here。|3110860|版本5.1文档为here。|3110861|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html|1|http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html^0|0|0|1D|0|7|4|0|0|8|4|1|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@$D|10|E|11|1|12]]|A|$]]|$1|J|3|K|5|6|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]|$1|L|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]|T|$5|O|P|Q|A|$R|U]]]]

Use 

<code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.</code>

Version 5.0 Docs are <a href="http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html" rel="noreferrer">here</a>.

Version 5.1 Docs are <a href="http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html" rel="noreferrer">here</a>.

blocks|key|341579|text|您可能想要阅读MySQL手册的this+page。表的锁定方式取决于表的类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|341580|MyISAM使用表锁来实现非常高的读取速度，但是如果您有一条UPDATE语句在等待，那么将来的SELECTS将排在UPDATE语句之后。|341581|InnoDB表使用行级锁定，并且不会在更新后锁定整个表。与InnoDB相关的还有其他类型的锁定问题，但您可能会发现它符合您的需求。|341582|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/table-locking.html^0|F|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

You may want to read <a href="http://dev.mysql.com/doc/refman/5.0/en/table-locking.html" rel="noreferrer">this page</a> of the MySQL manual. How a table gets locked is dependent on what type of table it is.

MyISAM uses table locks to achieve a very high read speed, but if you have an UPDATE statement waiting, then future SELECTS will queue up behind the UPDATE.

InnoDB tables use row-level locking, and you won't have the whole table lock up behind an UPDATE. There are other kind of locking issues associated with InnoDB, but you might find it fits your needs.

blocks|key|3323872|text|根据您的表类型，锁定的执行方式会有所不同，但SELECT计数也会有所不同。对于MyISAM表，一个简单的SELECT+count(*)+FROM+table不应该锁定表，因为它访问元数据来提取记录计数。Innodb将花费更长的时间，因为它必须抓取快照中的表来计算记录，但它不应该导致锁定。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3323873|您至少应该将concurrent_insert设置为1(默认值)。然后，如果数据文件中没有要填充的表的“间隙”，插入将被附加到文件中，并且MyISAM表可以同时执行SELECT和INSERT。请注意，删除一条记录会在数据文件中留下一个“间隙”，该间隙将尝试用将来的插入和更新来填充。|3323874|如果您很少删除记录，则可以将concurrent_insert设置为等于2，并且始终会将插入内容添加到数据文件的末尾。然后，selects和inserts可以同时发生，但无论您删除多少记录(所有记录除外)，您的数据文件永远不会变小。|3323875|底线是，如果你在一个表上有大量的更新、插入和选择，你应该让它成为InnoDB。不过，您可以在一个系统中自由地混用表格类型。|3323876|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Depending on your table type, locking will perform differently, but so will a SELECT count. For MyISAM tables a simple SELECT count(*) FROM table should not lock the table since it accesses meta data to pull the record count. Innodb will take longer since it has to grab the table in a snapshot to count the records, but it shouldn't cause locking.

You should at least have concurrent_insert set to 1 (default). Then, if there are no "gaps" in the data file for the table to fill, inserts will be appended to the file and SELECT and INSERTs can happen simultaneously with MyISAM tables. Note that deleting a record puts a "gap" in the data file which will attempt to be filled with future inserts and updates.

If you rarely delete records, then you can set concurrent_insert equal to 2, and inserts will always be added to the end of the data file. Then selects and inserts can happen simultaneously, but your data file will never get smaller, no matter how many records you delete (except all records).

The bottom line, if you have a lot of updates, inserts and selects on a table, you should make it InnoDB. You can freely mix table types in a system though.

blocks|key|341664|text|在mysql中启用脏读的另一种方法是add+hint:+LOCK+in+SHARE模式|type|unstyled|depth|inlineStyleRanges|entityRanges|data|341665|SELECT+*+FROM+TABLE_NAME+LOCK+IN+SHARE+MODE;+|code-block|syntax|javascript|341666|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

another way to enable dirty read in mysql is add hint: LOCK IN SHARE MODE

<pre><code>SELECT * FROM TABLE_NAME LOCK IN SHARE MODE; 
</code></pre>

blocks|key|343980|text|来自this参考：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|343981|343982|如果使用锁表显式获取表锁，则可以请求读本地锁而不是读锁，以使其他会话能够在您锁定表时执行并发插入。|blockquote|343983|343984|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html^0|2|4|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|V|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|W|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

From <a href="http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html" rel="nofollow noreferrer">this</a> reference:

<blockquote>
 If you acquire a table lock explicitly
 with LOCK TABLES, you can request a
 READ LOCAL lock rather than a READ
 lock to enable other sessions to
 perform concurrent inserts while you
 have the table locked.
</blockquote>

blocks|key|344064|text|SELECTs通常不会对InnoDB表执行您所关心的任何锁定。默认的事务隔离级别意味着选择不锁定内容。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|344065|当然，争用仍然会发生。|344066|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

SELECTs do not normally do any locking that you care about on InnoDB tables. The default transaction isolation level means that selects don't lock stuff.

Of course contention still happens.

blocks|key|3110985|text|这里有一个替代的编程解决方案，如果(重要的)您不关心查询过程中是否发生了更新，那么它可能适用于其他使用MyISAM的人。正如我们所知，MyISAM可能会导致表级锁，特别是当您有一个更新挂起并将被锁定时，然后此更新背后的其他select查询也会被锁定。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3110986|所以这种方法不会阻止锁，但它会制造很多微小的锁，这样就不会挂起一个网站，例如需要在非常短的时间内响应的网站。|3110987|这里的想法是，我们基于一个快速的索引获取一个范围，然后我们只从该查询中进行匹配，所以它是以较小的批次进行的。然后我们将列表向下移动到下一个范围，并检查它们是否匹配。|3110988|示例是用Perl编写的，其中包含一些伪代码，并从高到低遍历。|3110989|#+object_id+must+be+an+index+so+it's+fast
#+First+get+the+range+of+object_id,+as+it+may+not+start+from+0+to+reduce+empty+queries+later+on.

my+(+$first_id,+$last_id+)+=+$db->db_query_array(
++sql+=>+q{+SELECT+MIN(object_id),+MAX(object_id)+FROM+mytable+}
);

my+$keep_running+=+1;
my+$step_size++++=+1000;
my+$next_id++++++=+$last_id;

while(+$keep_running+)+{

++++my+$sql+=+q{+
SELECT+object_id,+created,+status+FROM+
++++(+SELECT+object_id,+created,+status+FROM+mytable+AS+is1+WHERE+is1.object_id+<=+?+ORDER+BY+is1.object_id+DESC+LIMIT+?+)+AS+is2
WHERE+status='live'+ORDER+BY+object_id+DESC+
};++

++++my+$sth+=+$db->db_query(+sql+=>+$sql,+args+=>+[+$step_size,+$next_id+]+);

++++while(+my+($object_id,+$created,+$status+)+=+$sth->fetchrow_array()+)+{

++++++++$last_id+=+$object_id;
++++++++
++++++++##+do+your+stuff

++++}

++++if(+!$last_id+)+{
++++++++$next_id+-=+$step_size;+#+There+weren't+any+matched+in+the+range+we+grabbed
++++}+else+{
++++++++$next_id+=+$last_id+-+1;+#+There+were+some,+so+we'll+start+from+that.
++++}

++++$keep_running+=+0+if+$next_id+<+1+%7C%7C+$next_id+<+$first_id;
++++
}|code-block|syntax|javascript|3110990|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Here is an alternative programming solution that may work for others who use MyISAM IF (important) you don't care if an update has happened during the middle of the queries. As we know MyISAM can cause table level locks, especially if you have an update pending which will get locked, and then other select queries behind this update get locked too.
So this method won't prevent a lock, but it will make a lot of tiny locks, so as not to hang a website for example which needs a response within a very short frame of time.
The idea here is we grab a range based on an index which is quick, then we do our match from that query only, so it's in smaller batches. Then we move down the list onto the next range and check them for our match.
Example is in Perl with a bit of pseudo code, and traverses high to low.
<pre><code>
# object_id must be an index so it's fast
# First get the range of object_id, as it may not start from 0 to reduce empty queries later on.

my ( $first_id, $last_id ) = $db-&gt;db_query_array(
 sql =&gt; q{ SELECT MIN(object_id), MAX(object_id) FROM mytable }
);

my $keep_running = 1;
my $step_size = 1000;
my $next_id = $last_id;

while( $keep_running ) {

 my $sql = q{ 
SELECT object_id, created, status FROM 
 ( SELECT object_id, created, status FROM mytable AS is1 WHERE is1.object_id &lt;= ? ORDER BY is1.object_id DESC LIMIT ? ) AS is2
WHERE status='live' ORDER BY object_id DESC 
}; 

 my $sth = $db-&gt;db_query( sql =&gt; $sql, args =&gt; [ $step_size, $next_id ] );

 while( my ($object_id, $created, $status ) = $sth-&gt;fetchrow_array() ) {

 $last_id = $object_id;
 
 ## do your stuff

 }

 if( !$last_id ) {
 $next_id -= $step_size; # There weren't any matched in the range we grabbed
 } else {
 $next_id = $last_id - 1; # There were some, so we'll start from that.
 }

 $keep_running = 0 if $next_id &lt; 1 || $next_id &lt; $first_id;
 
}



</code></pre>

Query:

<pre><code>SELECT COUNT(online.account_id) cnt from online;
</code></pre>

But online table is also modified by an event, so frequently I can see lock by running <code>show processlist</code>.

Is there any grammar in MySQL that can make select statement not causing locks?

And I've forgotten to mention above that it's on a MySQL slave database.

After I added into <code>my.cnf:transaction-isolation = READ-UNCOMMITTED</code>
the slave will meet with error:

<blockquote>
 Error 'Binary logging not possible. Message: Transaction level 'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'' on query
</blockquote>

So, is there a compatible way to do this?

Any way to select without causing locking in MySQL?

查询：SELECT COUNT(online.account_id) cnt from online;但是online表也会被事件修改，所以我经常可以通过运行show processlist看到锁。在MySQL中有什么语法可以让select语句不引起锁吗？我忘了在上面提到它是在一个MySQL从数据库上。在我添加到my.cnf:transaction-isolation = READ-UNCOMMI

问有什么方法可以选择而不会在MySQL中导致锁定？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有什么方法可以选择而不会在MySQL中导致锁定？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有什么方法可以选择而不会在MySQL中导致锁定？
EN