本文共 1320 字,大约阅读时间需要 4 分钟。
#建student表CREATE TABLE `student`( `id` bigint(5) UNIQUE NOT NULL COMMENT '主键', `name` varchar(25) DEFAULT NULL COMMENT '姓名', `birthday` datetime NOT NULL COMMENT '生日', `age` bigint(3) NOT NULL COMMENT '年龄', PRIMARY KEY(`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学生信息';SELECT * FROM student;SELECT * FROM bak_student;DELETE FROM bak_student WHERE id=2;#事务一:START TRANSACTION;UPDATE student SET name="Mary" WHERE id=1;UPDATE student SET name="Tome" WHERE id=2;COMMIT;#事务二:START TRANSACTION;UPDATE student SET name="Kevin" WHERE id=2;UPDATE student SET name="Colia" WHERE id=1;COMMIT;#上述两个事务,事务一将id=1的name改成Marry没问题,但是锁住了这条数据,事务二将id=2的name改成Kevin也没问题,但是锁住了这条数据,#这样,事务一修改id=2和事务二修改id=1由于相互锁住了数据,就会出现死锁的现象;#自动提交:SHOW VARIABLES LIKE "AUTOCOMMIT";SET AUTOCOMMIT=0; #0表示off,即为关闭自动提交SET AUTOCOMMIT=1; #1表示on,即为开启自动提交#mySQL的存储引擎:InnoDB和MyISAM,其中,InnoDB支持事务,MyISAM不支持事务和行级锁,且MyISAM的致命缺陷是崩溃后极大可能无法安全恢复;ALTER TABLE student ENGINE=InnoDB; #将student表的引擎修改为InnoDB;CREATE TABLE bak_student LIKE student; #创建一张新表bak_student,复制与student;ALTER TABLE bak_student ENGINE=MyISAM; #将bak_student的存储引擎改为MyISAM;INSERT INTO bak_student SELECT * FROM student; #将student表的数据复制到bak_student表中;#或者可以这样将student表中的数据插入到bak_student中:START TRANSACTION;INSERT INTO bak_student SELECT * FROM student WHERE id BETWEEN 3 AND 4;COMMIT;
转载地址:http://srtvb.baihongyu.com/