X Lock on table

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

X Lock on table

Post by nnani » Tue Aug 27, 2013 1:38 pm

Hello All,

I am trying to truncate a table in Vertica

The table has 308 records.

Code: Select all

truncate table testable ;
When I fire this query the query does not execute and does not give nay results, after a short a while of time I have to terminate the query by using Ctrl+C

When I do Ctrl+C

Code: Select all

Cancel request sent
ROLLBACK 2327:  Canceled: [Txn 0xe0000000020437] O lock table - canceled Canceled lock wait for Table:testable
I have checked with Locks table this is what it gives

Code: Select all

nnani=> select * from locks;
                                           node_names                                            |            object_name            |     object_id     |  transaction_id   |                    transaction_description                     | lock_mode | lock_scope  |      request_timestamp       |        grant_timestamp
-------------------------------------------------------------------------------------------------+-----------------------------------+-------------------+-------------------+----------------------------------------------------------------+-----------+-------------+------------------------------+-------------------------------
 node0001,node0002,node0003,node0004,node0005,node0006 | Table:testable | 45035996276482302 | 67553994410654689 | Txn: f0000000017be1 'delete from testable;' | X         | TRANSACTION | 2013-08-27 06:23:59.59236-05 | 2013-08-27 06:23:59.592371-05
(1 row)
I think this is because of delete query on testable.
Can anybody help me release this lock and let me know the cause of this.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: X Lock on table

Post by JimKnicely » Tue Aug 27, 2013 6:51 pm

Hi,

To release the lock the user issues the DELETE needs to commit or rollback their changes.

Using the transaction id you should be able to query the v_monitor.sessions table to get the user name and session id. Try contacting the user and let them know they need to finish their transaction. If that doesn't work, you can use the CLOSE_SESSION function which will rollback the transaction.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: X Lock on table

Post by nnani » Wed Aug 28, 2013 7:14 am

Thanks Jim,

We Closed the session using CLOSE_SESSION function.
nnani........
Long way to go

You can check out my blogs at vertica-howto

Post Reply

Return to “Vertica Database Development”