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 la