【例11.1】限定stu_info表中最多只能插入10条学生记录,如果表中插入人数大于10人,插入失败,操作过程如下。
USE test;
GO
BEGIN TRANSACTION
INSERT INTO stu_info VALUES(22,'路飞',80,'男',18);
INSERT INTO stu_info VALUES(23,'张露',85,'女',18);
INSERT INTO stu_info VALUES(24,'魏波',70,'男',19);
INSERT INTO stu_info VALUES(25,'李婷',74,'女',18);
DECLARE @studentCount INT
SELECT @studentCount=(SELECT COUNT(*) FROM stu_info)
IF @studentCount > 10
BEGIN
ROLLBACK TRANSACTION
PRINT '插入人数太多,插入失败!'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT '插入成功!'
END
【例11.2】锁定stu_info表中s_id=2的学生记录,输入语句如下。
USE test;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM stu_info ROWLOCK WHERE s_i2;
【例11.3】锁定stu_info表中记录,输入语句如下。
USE test;
GO
SELECT s_age FROM stu_info(TABLELOCKX) WHERE s_age=18;
【例11.4】创建名称为transaction1和transaction2的事务,在transaction1事务上面添加排他锁,事务1执行10秒钟之后才能执行transaction2事务,输入语句如下。
USE test;
GO
BEGIN TRAN transaction1
UPDATE stu_info SET s_score=88 WHERE s_name='许三' ;
WAITFOR DELAY '00:00:10';
COMMIT TRAN
BEGIN TRAN transaction2
SELECT * FROM stu_info WHERE s_name='许三';
COMMIT TRAN
【例11.5】创建名称为transaction1和transaction2的事务,在transaction1事务上面添加共享锁,允许两个事务同时执行查询操作,如果第二个事务要执行更新操作,必须等待10秒钟,输入语句如下。
USE test;
GO
BEGIN TRAN transaction1
SELECT s_score,s_sex,s_age FROM stu_info WITH(HOLDLOCK) WHERE s_name='许三';
WAITFOR DELAY '00:00:10';
COMMIT TRAN
BEGIN TRAN transaction2
SELECT * FROM stu_info WHERE s_name='许三';
--UPDATE stu_info SET s_score=90 WHERE s_name='许三' ;
COMMIT TRAN
USE test;
GO
BEGIN TRANSACTION
INSERT INTO stu_info VALUES(22,'路飞',80,'男',18);
INSERT INTO stu_info VALUES(23,'张露',85,'女',18);
INSERT INTO stu_info VALUES(24,'魏波',70,'男',19);
INSERT INTO stu_info VALUES(25,'李婷',74,'女',18);
DECLARE @studentCount INT
SELECT @studentCount=(SELECT COUNT(*) FROM stu_info)
IF @studentCount > 10
BEGIN
ROLLBACK TRANSACTION
PRINT '插入人数太多,插入失败!'
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT '插入成功!'
END
【例11.2】锁定stu_info表中s_id=2的学生记录,输入语句如下。
USE test;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM stu_info ROWLOCK WHERE s_i2;
【例11.3】锁定stu_info表中记录,输入语句如下。
USE test;
GO
SELECT s_age FROM stu_info(TABLELOCKX) WHERE s_age=18;
【例11.4】创建名称为transaction1和transaction2的事务,在transaction1事务上面添加排他锁,事务1执行10秒钟之后才能执行transaction2事务,输入语句如下。
USE test;
GO
BEGIN TRAN transaction1
UPDATE stu_info SET s_score=88 WHERE s_name='许三' ;
WAITFOR DELAY '00:00:10';
COMMIT TRAN
BEGIN TRAN transaction2
SELECT * FROM stu_info WHERE s_name='许三';
COMMIT TRAN
【例11.5】创建名称为transaction1和transaction2的事务,在transaction1事务上面添加共享锁,允许两个事务同时执行查询操作,如果第二个事务要执行更新操作,必须等待10秒钟,输入语句如下。
USE test;
GO
BEGIN TRAN transaction1
SELECT s_score,s_sex,s_age FROM stu_info WITH(HOLDLOCK) WHERE s_name='许三';
WAITFOR DELAY '00:00:10';
COMMIT TRAN
BEGIN TRAN transaction2
SELECT * FROM stu_info WHERE s_name='许三';
--UPDATE stu_info SET s_score=90 WHERE s_name='许三' ;
COMMIT TRAN
--课后练习
--create database index_test
--use index_test
--create table writers
--(
-- w_id int primarwritersy key not null,
-- w_name varchar(255) not null,
-- w_address varchar(255),
-- w_age char(2) not null,
-- w_note varchar(255)
--);
--为w_id创建索引
--use index_test
--create unique clustered index UniqIdx
--on writers(w_id desc)
--use index_test
--create unique nonclustered index NAIdx
--on writers(w_name,w_address)
----重命名
--use index_test
--go
--exec sp_rename 'writers.NAIdx','muti_index','index'
--查看muti_index索引的统计信息
--dbcc show_statistics('index_test.dbo.writers',muti_index)
--删除索引
--use index_test
--drop index writers.muti_index