Teradata: Select Table With No Lock

lockingteradata

As I understand, when selecting from a table in Teradata the table is locked by default.
I would like to do a simple select * from table without locking the table. Meaning, i want to do a select and allow other users to select from the same table without having to wait for my select to be over.

I know that i can downgrade the select lock using LOCKING modifier as follows:

locking table my_table for access select * from my_table

But this will allow my operation not to wait for locks. To my understanding it will still lock the table and so other operations will have to wait for the lock.

Best Answer

If those other users only need to Select from that table neither your nor the other session must add a LOCKING modifier.

A Select applies a READ lock by default which doesn't prevent other Selects, only INSERT/UPDATD/DELETE & DDL is blocked.

If you want other sessions to be able to modify rows you should use LOCK ROW ACCESS which allows dirty reads, i.e. a 2nd session might modify data while you're reading.

Teradata supports two out of the four Standard SQL Transaction isolation levels, the least and the most restrictive:

Read Uncommited -> ACCESS LOCK
Read Commited
Repeatable Read
Serializable -> READ LOCK

Teradata 15.10 implements a new feature named Load Isolation which adds a kind of Read Commited.

Related Solutions

Mysql – How should I interpret the “lock time” within thesql slow query log

The lock_time in the slow query log is actually the amount of time the query spent waiting to acquire the lock it needs to run. For example, UPDATES queries need a write lock.

The locking also depends on the storage engine you are using in the table. When writing, InnoDB will use row-level locking and only lock the rows that are being changed. MyISAM will lock the entire table until the update/insert/delete is complete.

The Locking of the entire table for MyISAM is a big reason SELECT queries will have a lock_time in your slow query log.

Mysql – Does MySQL guarantee atomic upgrade from read lock to write lock

Indeed, the part of the manual you quoted does just what it says:

-- Connection #1
LOCK TABLE mytable READ; -- lock is acquired

-- Connection #2
LOCK TABLE mytable WRITE; -- execution is suspended, lock is NOT acquired

-- Connection #1
LOCK TABLE mytable WRITE; -- execution is suspended
-- READ lock was released, allowing Connection #2 to acquire the WRITE lock
-- Connection #2 resumes

Solution: do switch to InnoDB and use transactions. Performance of the InnoDB engine improved a lot in the recent versions.

-- Connection #1
BEGIN;
SELECT * FROM mytable LOCK IN SHARE MODE; -- lock is acquired

-- Connection #2
BEGIN;
SELECT * FROM mytable FOR UPDATE; -- execution is suspended, lock is NOT acquired

-- Connection #1
SELECT * FROM mytable FOR UPDATE;
-- deadlock is detected, Connection #2 is rolled back

Best Answer

Related Solutions

Mysql – How should I interpret the “lock time” within thesql slow query log

Mysql – Does MySQL guarantee atomic upgrade from read lock to write lock

Related Question