SAP IQ and SQL Anywhere: Locking and blocking only for a maximum specified time

Usually in IQ, you will lock a table for a short time (dml operations) or longer when performing a data load, table/index maintenance or issuing a holdlock within your transaction. These locks can cause other processes to block or even abort with an error “SQL Anywhere Error – 210: User ‘another user’ has the row in (some table)”. It can become problematic if you have an application that doesn’t close the transaction in a reasonable timeframe.

Consider the following scenario: A process that retrieves and loads dozens of data files to load into a single table every few minutes.
Issue: Occasionally the “SQL Anywhere Error – 210: User ‘another user’ has the row in (some table)” error will occur causing the load to fail for that file(s).

What can be done? Several things really:

  1. Trap/ignore the error
  2. Retry loading the file. How many times do we attempt this before we collide with the next set of files to load?
  3. Issue holdlock (not sure if it works with load table – I haven’t tried it) causing write access to block. If the load table is stalled, it will hold the lock indefinitely
  4. Combine all the files and load that monolithic file. Potentially faster as setting up a load table connection is very expensive, but if the file fails, the entire set fails. Do we retry?
  5. Issue temporary locks, but block only for X time and only allow itself to be blocked for Y time before rolling back, erroring.

Using the temporary locks/blocking seems to be the best option for this particular scenario to me. It allows for the load tables to run in succession without me having to set up the queue and if it does error out, I can decide in the outside code whether or not to retry the load table. Basically it gives me more control over the locking/blocking.

set temporary option date_order = 'YMD';
set temporary option timestamp_format = 'yyyy/mm/dd hh:nn:ss.sss';

-- "If the blocking option is set to “On”, any transaction attempting to obtain a lock that conflicts with an existing lock held by another transaction waits until every conflicting lock is released or until the blocking_timeout is reached. If the lock is not released within blocking_timeout milliseconds, then an error is returned for the waiting transaction. If the blocking option is set to “Off”, the transaction that attempts to obtain a conflicting lock receives an error." - https://wiki.scn.sap.com/wiki/display/SQLANY/How+to+Debug+Blocking+in+SQL+Anywhere
SET TEMPORARY OPTION "blocking" = 'ON';

-- we will wait up to 10 seconds to obtain a lock before rolling back
SET TEMPORARY OPTION "blocking_timeout" = '10000';

-- allow blocking of other transactions to maximum 5 sec before rolling back
SET TEMPORARY OPTION blocking_others_timeout = '5000';

LOAD TABLE ${REPOSITORY_SCHEMA}.activity (
    ConnHandle,
    LSServer,
    Name,
    Userid
)
USING CLIENT FILE '${OUT_CSV_FILE}'
NOTIFY 1000
QUOTES ON ESCAPES OFF
FORMAT bcp
DELIMITED BY '|'
ROW DELIMITED BY '\n';

COMMIT;
Share Button

Linux & selinux: xauth timeout in locking authority file .Xauthority SOLVED

Error:

Last login: Tue Jan 20 14:17:19 2015 
/usr/bin/xauth:  timeout in locking authority file /home/jason/.Xauthority

Attempting to manually generate a new .Xauthority file results in the same error:

$ xauth generate :0 .trusted
xauth:  timeout in locking authority file /home/jason/.Xauthority

If the SELinux configuration is set to enforcing then we need to make sure the home directories are set in the correct context:

[root@localhost selinux]# egrep -e '^SELINUX=' /etc/selinux/config
SELINUX=enforcing

Taking a look at the SELinux settings for the home directories (use Z with the ls command):

[root@localhost ~]# ls -aslZ /home/
total 36
drwxr-xr-x. root    root    system_u:object_r:home_root_t:s0 .
drwxr-xr-x. root    root    system_u:object_r:root_t:s0      ..
drwx------. 55 unconfined_u:object_r:home_root_t:s0 jason users   4096 Jan 20 14:22 jason

The context for my home directory (jason) should be unconfined_u:object_r:user_home_dir_t:s0 and not unconfined_u:object_r:home_root_t:s0 as it is a home directory and not part of the root file system per se.

The easiest thing to do is just reset (restore) the context using restorecon as root

[root@localhost ~]# restorecon /home/jason

Verify that the context was changed:

[root@locahost ~]# ls -aslZ /home/
total 36
drwxr-xr-x. root    root    system_u:object_r:home_root_t:s0 .
drwxr-xr-x. root    root    system_u:object_r:root_t:s0      ..
drwx------. jason users   unconfined_u:object_r:user_home_dir_t:s0 jason

Verify fix:
Verify with a new ssh connection (with X11 Forwarding enabled):

Last login: Tue Jan 20 14:19:15 2015 
/usr/bin/xauth:  creating new authority file /home/jason/.Xauthority
$ xeyes

Capture

Share Button