mysql 共享锁 排他锁 防插入锁

 

 

试验1

事务1：

 

 

#!/usr/bin/python

import time

import MySQLdb;

conn = MySQLdb.connect(host="localhost",port=3306,user="root",passwd="asdf",db="test",unix_socket="/data/mysql_3306/mysql.sock")

cursor = conn.cursor()

cursor.execute("select * from test")

 

while str!="1":

    str = raw_input()

cursor.execute("update test id=id-1")

 

 

 

while str!="exit":

    str = raw_input()

cursor.close()

conn.close()

 

 

 

 

在mysql命令行中输入以下：

mysql> select * from test;

当事务1中等待输入1时，显示出select匹配的行

当事务1中输入1时，显示结果如下：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

结论：当select时会检测当前是否有事务会修改（比影响要确切一些）当前的记录时，才会被阻塞。

 

 

试验2

在事务1：

 

#!/usr/bin/python

import time

import MySQLdb;

conn = MySQLdb.connect(host="localhost",port=3306,user="root",passwd="asdf",db="test",unix_socket="/data/mysql_3306/mysql.sock")

cursor = conn.cursor()

cursor.execute("select * from test lock in share mode")

 

 

while str!="exit":

    str = raw_input()

cursor.close()

conn.close()

 

 

在mysql命令行中输入以下：

mysql> update test set id=id-1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

 

结论：当事务中包含select ...lock in share mode的时候，相关记录将会被锁住，不允许进行修改。

 

试验3

执行下面的python脚本两遍，并且同时输入1，2之后

#!/usr/bin/python

import time

import MySQLdb;

conn = MySQLdb.connect(host="localhost",port=3306,user="root",passwd="asdf",db="test",unix_socket="/data/mysql_3306/mysql.sock")

cursor = conn.cursor()

cursor.execute("select * from test lock in share mode")

str = ""

while str!="1":

    str = raw_input()

while str!="2":

    str = raw_input()

cursor.execute("update test set id=id-1")

while str!="exit":

    str = raw_input()

cursor.close()

conn.close()

 

[root@TJSJHL196-139 tmp]# python test_transaction.py 

1

2

Traceback (most recent call last):

  File "test_transaction.py", line 12, in <module>

    cursor.execute("update test set id=id-1")

  File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 173, in execute

  File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler

_mysql_exceptions.OperationalError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')

 

 

结论：当两个事务同时包含lock in share mode的时候，并且涉及当相同的记录时会产生死锁，mysql会自动检测并且将第二个直接干掉，这个就是select ... lock in share mode和select...for update的区别，lock in share mode允许不排斥lock in share mode，for update排斥一切

 

lock in share mode称为共享锁

for update称为排他锁

 

当事务中包含insert、update、delete命令的时候，会从命令开始一直到事务结束相关记录增加一个排他锁。

 

在默认情况下，InnoDB数据表驱动程序在遇到带有范围条件表达式（比如where id>100 或 where id between 100 and 200）的select ... lock in share mode、select ... for update 、update、delete命令还会给他们加上一把防插入锁(gap and next key lock)，将锁住符合条件的现在或者将来的记录。