學習心得 : 查詢Oracle當前交易處理時間

 問題描述

查詢Oracle當前交易處理時間

解決方式

1. 正執行交易指令

SELECT T1.SID,T1.SQL_ID, T1.USERNAME, T2.XIDUSN, T2.USED_UREC, T2.USED_UBLK,T2.START_TIME,SQL_TEXT 

FROM V$SESSION  T1, V$TRANSACTION T2 , V$SQL T3

WHERE T1.SADDR = T2.SES_ADDR   AND T1.SQL_HASH_VALUE=T3.HASH_VALUE;

2. 交易語法執行完成但尚未COMMIT或ROLLBACK

SELECT T1.SID,T1.USERNAME, T2.XIDUSN, T2.USED_UREC, T2.USED_UBLK,T2.START_TIME

FROM V$SESSION  T1, V$TRANSACTION T2

WHERE T1.SADDR = T2.SES_ADDR;

此外SQL DEVELOPER的Real-Time SQL Monitoring報表可查詢執行時間超過5秒的SQL指令，不論是否有交易，執行完成或與執行中都會紀錄於V$SQL_MONITOR，並保留一段時間。

筆記_鎖定類型

一、    鎖定類型Lock types

1.   Shared lock (S)：執行select指令時發生，相容於Shared與Update等lock。

2.   Exclusive lock (X)：執行insert、update與delete時發生，不會相容於任何一個lock。

3.   Update lock (U)：執行類似updtae tablea set col1=’A’ where ID=2的時令時，sql server會先搜尋符合條件的資料，此時就會產生Update lock，而當找到並更新內容時會轉換成Exclusive lock。

4.   Intent lock (IS, IX, IU, etc)：當row有Exclusive lock時，在page、table與database層級就會產生IX lock，用來增加檢查lock的相容性效率。

二、    Update lock與Intent lock說明

1.   Update lock的功能：預防死結，當有兩個session語法要更新同一筆資料時，例如：updtae tablea set col1=’A’ where ID=2 ，

l   若沒有Update lock的情況如下：

(1) 第一個session執行SQL時，因為要搜尋資料，故會放置Shared lock。

(2) 接著第二個session執行SQL也會放置Shared lock，因為Shared lock是相容的。

(3) 當第一個session要更新資料時，會將Shared locks轉成Exclusive lock，但因為第二個session有Shared locks，故會等待第二個釋放lock。

(4) 當第二個session要更新資料時，也會將Shared lock轉成Exclusive lock，但因為第一個session有Shared lock，故會等待第一個釋放lock。

(5) 兩個session會無限的等待，就會產生死結(deadlock)。

l   若有Update lock的情況如下：

(1) 第一個session執行SQL時，因為要搜尋資料，故會放置Update lock。

(2) 接著第二個session執行SQL也會放置Update lock，但因為Update lock是不相容的，故第二個session會被blocking，無法放置lock。

(3) 第一個session要更新資料將Shared lock成功轉成Exclusive lock

(4) 第一個session完成交易，釋放Exclusive lock。

(5) 第二個session將Shared lock成功轉成Exclusive lock並也完成交易。

(6) 皆大歡喜。

2.   Intent lock的功能：增加檢查lock相容性的效率。

(1) 當session1刪除一筆資料，對row data放置Exclusive lock。

(2) 此時session1也會對page、table與database放置Intent Exclusive lock。

(3) 若有session2要對table放置Shared lock時，session2藉由Intent Exclusive lock知道table內已有Exclusive lock，而不用逐筆檢查每一筆row。

三、    參考：

1.   http://www.madeiradata.com/role-update-lock-sql-server/

2.   https://www.sqlpassion.at/archive/2016/05/16/why-do-we-need-intent-locks-in-sql-server/

3.   https://aboutsqlserver.com/2011/04/14/locking-in-microsoft-sql-server-part-1-lock-types/

SQL SERVER欄位資料類型為Timestamp的使用範例

Timestamp說明：

Timestamp為SQL SERVER資料庫內自動產生的唯一二進位數字的資料類型，Timestamp通常用來作為版本戳記資料表資料列的機制，簡單來說就是ROW的版本控管，大小為8位元組。只要資料有更新或新增Timestamp的內容就會遞增。

Timestamp的應用情境：

若有兩個人同時在應用程式編輯同一筆資料，使用Timestamp可以防止更新被覆蓋。

Timestamp使用範例：

(1)新增資料:

Timestamp是SQL SERVER會自動產生，不用特別匯入。

USE [TestDataTypeDB]
GO
--建立測試timestamp的資料表
CREATE TABLE [dbo].[TestTable](
 [nid] [int] NOT NULL,
 [timeid] [timestamp] NOT NULL,
 datetimes DATETIME2 not NULL
) ON [PRIMARY]

GO
--插入兩筆資料(timestamp是SQL SERVER會自動產生，不用特別匯入)
INSERT INTO [dbo].[TestTable](nid,datetimes)
VALUES(1,GETDATE()),(2,GETDATE())
GO
--檢視結果
SELECT * FROM [dbo].[TestTable]
GO

--更新一筆資料，資料版本會改變
UPDATE [dbo].[TestTable]
SET datetimes=GETDATE()+1
WHERE nid = 1
GO
--檢視結果
SELECT * FROM [dbo].[TestTable]
GO

--插入一筆新資料，新資料版本會是最新的版本
INSERT INTO [dbo].[TestTable](nid,datetimes)
VALUES(3,GETDATE())
GO
SELECT * FROM [dbo].[TestTable]
GO

(2)資料匯入：

在使用SSIS或是匯入匯出精靈轉入資料時，如果沒有略過資料類型為Timestamp的欄位會產生資料轉檔錯誤。

我們將資料類型為Timestamp的資料行忽略，此時就可以成功匯入資料內容到目的端了。

搞定收工

如何在SQL Server Management Studio(SSMS)上使用sqlcmd執行dos command

朋友問我在不使用XP_CMDSHELL和SQLCLR的情況下如何在SSMS上執行DOS Command，當下想到就是SQLCMD。

以下為執行步驟：

1.  先確認把XP_CMDSHELL是DISABLE。

2.選擇SQLCMD模式

3.在C:\建立一個批次檔b.at。內容為DIR > C:\TEST2.txt

4.在SSMS上面執行!! C:\b.bat，到C:\觀看執行結果。

5.搞定收工。

附註！在SSMS上SQLCMD的指令是灰色背景的。