page311
399
Blocked Transactions
Blocked transactions are those DML operations waiting for an object lock before
completing. I previously discussed how multiple concurrent processes logging into a
single table will serialize processing as each process must acquire a table lock before
completing their DML operation. The Account Usage Store
query_history
table
provides information on blocked transactions, as shown next:
SELECT query_type,
query_id,
query_text,
total_elapsed_time,
transaction_blocked_time
FROM TABLE ( information_schema.query_history())
WHERE transaction_blocked_time > 0
ORDER BY transaction_blocked_time DESC;
Blocked transactions may time out waiting for a lock, and you may set a session
variable called
LOCK_TIMEOUT
to adjust the default from 12 hours to a more suitable
value, in this example, an hour.
ALTER SESSION SET LOCK_TIMEOUT = 3600;
You can find more information on setting session values at
https://docs.
snowflake.com/en/sql-reference/sql/alter-session
. I also found this article useful
where
LOCK_TIMEOUT
is not honored by transactions:
https://community.snowflake.
com/s/article/LOCK-TIMEOUT-not-honoured-by-transactions
.
You can find more information on blocked transactions at
https://docs.
snowflake.com/en/sql-reference/transactions#label-analyzing-blocked-
transactions
.
Join Explosion
Identifying join explosions is a two-step process. Here I present the two SQL statements
required.
ChapTer 10
OpTimizing perfOrmanCe