以下记录只针对数据库本身，不针对相关工具，因为我个人来说用的是DG(DATA GRIP)来统一操作的。
SQL SERVER
MySQL
Oracle
SQL Server
创建表、过程、函数等情况时，最好加一个判断是否已存在，是考虑DROP掉重新创建还是什么情况需要自己权衡。
-- 创建表
-- 1.物理表
IF OBJECTID('table_Name') IS NOT NULL
DROP TABLE tabne_Name;
GO
-- 2.临时表
IF OBJECT('tempdb..#TEMPORARYTABLENAME') IS NOT NULL
DROP TABLE #TEMPORARYTABNENAME
-- 3.创建表
CREATE TABLE table_Name()
GO创建查询死锁的存储过程，后续直接执行这个过程查询死锁情况
CREATE PROC sp_who_lock AS
/*
ProcName:T-SQL查找死锁进程
CreatedDate:2005/1/27
CreatedBy:ChanSheen
Version:1.0
ModifiedDate:2005/1/27
ModifiedContent:用于T-SQL查询死锁进程，建在master下
*/
BEGIN
DECLARE @SPID INT, @BL INT, @IntTransactionCountOnEntry INT, @IntRowcount INT, @IntCountProperties INT, @IntCounter INT;
CREATE TABLE #tmp_lock_who
(
ID INT IDENTITY (1, 1),
SPID SMALLINT,
BL SMALLINT
);
INSERT INTO #tmp_lock_who
(
SPID, BL
)
SELECT 0, BLocked
FROM (
SELECT *
FROM sysprocesses
WHERE BLocked > 0
) a
WHERE NOT EXISTS (
SELECT 1
FROM (
SELECT *
FROM sysprocesses
WHERE BLocked > 0
) b
WHERE a.BLocked = SPID
)
UNION
SELECT SPID, BLocked
FROM sysprocesses
WHERE BLocked > 0;
-- 找到临时表的记录数
SELECT @IntCountProperties = COUNT(1), @IntCounter = 1 FROM #Tmp_lock_who;
IF @IntCountProperties = 0 SELECT N'现在没有阻塞和死锁信息' AS message;
-- 循环开始
WHILE @IntCounter <= @IntCountProperties BEGIN
-- 取第一条记录
SELECT @SPID = SPID, @BL = BL FROM #tmp_lock_who WHERE ID = @IntCounter;
BEGIN
IF @SPID = 0
SELECT N'引起数据库死锁的是: ' + CAST(@BL AS VARCHAR(10)) + N'进程号,其执行的SQL语法如下';
ELSE
SELECT N'进程号SPID：' + CAST(@SPID AS VARCHAR(10)) + N'被' + N'进程号SPID：' + CAST(@BL AS VARCHAR(10)) +
N'阻塞,其当前进程执行的SQL语法如下';
DBCC INPUTBUFFER (@BL);
END;
-- 循环指针下移
SET @IntCounter = @IntCounter + 1;
END;
DROP TABLE #tmp_lock_who;
RETURN 0;
END;
GO查找某个字符串（通常是表）被哪些过程/函数/视图等使用
CREATE PROCEDURE SP_SEARCH(
@OBJNAME VARCHAR(100)
) AS
/*
ProcName:查询某个字符被用于哪些过程、函数等
CreatedDate:2010/01/01
CreatedBy:Justin
Version:1.0
ModifiedDate:2010/01/01
ModifiedContent:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/
BEGIN
DECLARE @STRING VARCHAR(1000)
SET @STRING = 'Select xtype,name,crdate From sysobjects Where ID In (Select ID From syscomments Where Text Like N''%' + @OBJNAME +
'%'') Order By crdate Desc'
EXEC (@STRING)
ENDMySQL
查询死锁
SELECT NOW(), CONCAT(p2.USER, '@', p2.HOST), t2.THREAD_OS_ID, r.trx_wait_started, TIMEDIFF(NOW(), r.trx_wait_started),
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()), CONCAT(rl.OBJECT_SCHEMA, rl.OBJECT_NAME), rl.INDEX_NAME, rl.lock_type, r.trx_id, r.trx_started,
TIMEDIFF(NOW(), r.trx_started), r.trx_rows_locked, r.trx_rows_modified, r.trx_mysql_thread_id, sys.format_statement(r.trx_query), rl.ENGINE_LOCK_ID,
rl.lock_mode, CONCAT(p.USER, '@', p.HOST), t.THREAD_OS_ID, b.trx_id, b.trx_mysql_thread_id, sys.format_statement(b.trx_query), bl.ENGINE_LOCK_ID,
bl.lock_mode, b.trx_started, TIMEDIFF(NOW(), b.trx_started), b.trx_rows_locked, b.trx_rows_modified, CONCAT('KILL QUERY ', b.trx_mysql_thread_id),
CONCAT('KILL ', b.trx_mysql_thread_id), IF(p.COMMAND = 'Sleep', CONCAT(p.TIME, ' 秒'), 0)
FROM performance_schema.data_lock_waits w
LEFT JOIN information_schema.INNODB_TRX r ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
LEFT JOIN information_schema.INNODB_TRX b ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id
LEFT JOIN performance_schema.data_locks rl ON w.REQUESTING_ENGINE_LOCK_ID = rl.ENGINE_LOCK_ID
LEFT JOIN performance_schema.data_locks bl ON w.BLOCKING_ENGINE_LOCK_ID = bl.ENGINE_LOCK_ID
LEFT JOIN information_schema.PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
LEFT JOIN information_schema.PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
LEFT JOIN performance_schema.threads t ON t.PROCESSLIST_ID = b.trx_mysql_thread_id
LEFT JOIN performance_schema.threads t2 ON t2.PROCESSLIST_ID = r.trx_mysql_thread_id
ORDER BY r.trx_wait_started;Oracle
查询死锁
SELECT object_name, machine, s.sid, s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id AND l.session_id = s.sid;
ALTER SYSTEM KILL SESSION '383, 55376'; --SID,SERIAL# 替换成上面查到的值
评论