Locks

How locks work

Kognitio uses locks on objects to ensure consistency. Locking an object ensures that no other query can perform a conflicting operation at the same time.

Typically there are two sorts of lock granularity - TABLE or ROW locks. There are also two lock types - EXCLUSIVE or SHARED.

Every time an object (e.g. a table or view) is involved in a transaction, a lock is applied to that object and released at the end of the transaction. Some types of lock allow other transactions to use the same object in certain ways, other types of lock are more restrictive. If a transaction is unable to use an object due to a pre-existing lock from another query, the query will be in a pending state until the lock is released.

Note that locks are held for the duration of a transaction, so the completion of a command is not sufficient - the transaction must be rolled back/committed to free any locks held. Check in ipe_transaction to see if the relevant transaction has two entries - if it has only one it is still open and still holding locks.

What locks conflict with each other?

As you would expect, ROW locks on different rows in a table do not conflict with each other, and SHARED locks do not conflict with other SHARED locks. Conflicts occur when an EXCLUSIVE lock request and any other lock request conflict in one of the following ways:

  • they are both row locks for the same row in the same table

  • they are both table locks for the same table

  • one is a row lock within a table, and the other is a table lock for that same table

Lock timeouts

Kognitio has a lock timeout mechanism to prevent lock requests being outstanding for too long - by default the timeout is 24 hours, but this can be reduced via the lkti parameter. However, there are normally better ways to deal with long-standing lock requests, so this parameter is not usually changed on production systems.

Deadlocks

Kognitio has a deadlock detection method. Imagine transaction A gets an EXCLUSIVE lock on table T1, then transaction B gets an EXCLUSIVE lock on table T2. Now if A tries to get a SHARED lock on T2 it will block, as B already holds a conflicting lock. Now if B tries to get a SHARED lock on T1, it will also block. Kognitio detects the deadlock condition, and will error one of the transactions, allowing the other one to proceed.

Monitoring locks

Monitoring granted/pending locks

The virtual table view, IPE_ALLLOCKS, allow users to see relevant locks which are currently held, or pending. There is also information on e.g. which session and transaction requested each lock, so it is possible to tie these back to entries in IPE_CURSESSIONS if the queries are still running, or IPE_COMMAND if an historic issue is being investigated. To find information on the contents of IPE_ALLLOCKS, run explain ipe_alllocks;.

Another way to see similar information is in Console, under view > reports > locks report.

Console also has a “blocking locks report”, under view > reports > blocking locks report. This shows queries which hold locks that block the currently logged in user’s queries.

lock timeouts and deadlocks

In the case of a lock timeout, an entry will appear in SYS.IPE_ERRORLOG looking something like this:

Lock Manager: session 213669 transaction 452609 requested EX TAB on table 12039. Conflicts with session 213668 transaction 452608 SH TAB on table 12039

Now it is possible to look in ipe_command to see which transactions were being run for the transactions listed above, and abort one of the sessions if appropriate, or wait for one session to complete then resubmit the query. IPE_ERRORLOG also logs entries when a deadlock is detected, for example:

Lock Manager: session 63506 transaction 291265 requested SH TAB on table 3727. Deadlock detected: dependency cycle of sessions 63506, 62908.

In this case, look in ipe_command for commands run by transactions from the relevant sessions to see which queries caused the problems, and resolve in a similar way to timeouts.

From the command line, it is possible to review historical log files related to locking issues. These files can be found by running ls \`wxlogd smd\`/locking*, and contain historical evidence of when a query waited on a pending lock for more than da_dump_lkti seconds (default 600), which results in output like this

02-06_17:57:23_BST: DA: DUMPING LOCKS: Locks held for longer than da_dump_lkti
02-06_17:57:23_BST: DA: Locks granted:
02-06_17:57:23_BST: DA: Session 26589 TNo  67494 type SH ROW table      1 row hash 1173333716
02-06_17:57:23_BST: DA: Session 26589 TNo  67494 type SH ROW table      1 row hash -1277331839
02-06_17:57:23_BST: DA: Session 26589 TNo  67494 type SH ROW table      1 row hash 670452605
...
02-06_17:57:23_BST: DA: Session 26597 TNo  67489 type EX ROW table      1 row hash -789570782
...
02-06_17:57:23_BST: DA: Session 26597 TNo  67489 type SH ROW table      0 row hash 1385381117
02-06_17:57:23_BST: DA: Locks pending:
02-06_17:57:23_BST: DA: Session 26589 TNo  67494 type SH TAB table     12 row hash -2147483648
02-06_17:57:23_BST: DA: Session 26589 TNo  67494 type SH TAB table      0 row hash -2147483648
02-06_17:57:23_BST: DA: Session 26589 TNo  67494 type SH TAB table      1 row hash -2147483648
02-06_17:57:23_BST: DA: Session 26589 TNo  67494 type SH TAB table     27 row hash -2147483648

In the case above you an see that the EX ROW (exclusive row) lock held on table 1 by tno 67489 is blocking the pending lock requests from tno 67494. The \`wxlogd smd\`/locking* file will also log entries when a deadlock is detected.

Manual table lock

Most of the time, locks on objects will be applied automatically when a query is run. However, it is possible to manually lock objects with SQL. This is useful if you need to perform a complex transaction containing several UPDATE or DELETE queries, and want to make sure that all the objects being manipulated are locked as early as possible within the transaction. Use the LOCK TABLE statement to set up a lock that lasts for the duration of the current transaction, (that is, up to the next COMMIT or ROLLBACK). To hold the lock for any length of time, you have to be running in Transaction Mode. The synax is

LOCK TABLE table IN {SHARE | EXCLUSIVE} MODE

The following example illustrates how this works:

Session 1

Session 2

SET MODE TRANSACTION;

LOCK TABLE mjbt1 IN EXCLUSIVE MODE;

SELECT * FROM mjbt1;

Result: LOCK TIMEOUT

COMMIT

SELECT * FROM mjbt1

Result: OK

LOCK TABLE mjbt1 IN SHARE MODE

SELECT * FROM mjbt1

Result: OK

DELETE FROM mjbt1

Result: LOCK TIMEOUT

COMMIT

DELETE FROM mjbt1

Result: OK

Lock system

Some operations on Kognitio require a global session, where only one user session has access to the Kognitio system. A LOCK SYSTEM is required for reclaiming space from the disks using a Global Session Reclaim. Changes to many system tables can only be made with a global lock. A global lock may simply be used to prevent users from logging on to the system while some administrative function is performed.

Obtaining a global session is achieved using the LOCK SYSTEM command. Normally, this is reserved for the exclusive use of the System Administrator, but it is possible to give other users the LOCK SYSTEM privilege. If there are any other sessions running, SYS may need to terminate them in order to obtain the system lock. All other sessions will be aborted by running the following command:

UPDATE ipe_cursessions SET abort = 2 WHERE session <> CURRENT_SESSION

Obviously, care should be taken when aborting user sessions. A session may take some time to abort if a large INSERT, UPDATE or DELETE operation has to be rolled back. Once there are no other sessions connected to Kognitio, SYS can give the LOCK SYSTEM command:

LOCK SYSTEM;

and then perform the task requiring exclusive use.

After a user issues a LOCK SYSTEM command and obtains a global session, any attempt to access Kognitio by another session receives the error message:

Sessions inhibited

The global session lasts until the user disconnects, either explicitly, by issuing a DISCONNECT, or by issuing another CONNECT command.