DisCopy


Monday 27 June 2022

PostgreSQL VACUUM!

VACUUM

is the garbage-collector of PostgreSQL Database to discard obselete (updated) / deleted records of tables, materialized views and optionally analyze the statistics of a database/object.

If a table doesn’t get vacuumed, it will get bloated, which wastes disk space and also hampers the performance.

 

PostgreSQL's VACUUM command is a maintenance command needs to be run periodically to cater the following vital tasks:

1.      To recover or reuse disk space occupied by updated or deleted rows.

2.      To analyze/update data statistics used by the PostgreSQL query planner.

3.      To protect against loss of very old data due to transaction ID wraparound.

 

In normal PostgreSQL operation, tuples/records/rows that are deleted or obsoleted by an update are not physically removed from their table until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables as VACUUM reclaims storage occupied by dead tuples.

Normally we don’t need to take care of all that, because the autovacuum daemon does that with some limitations based on the configuration.

The frequency and scope of the VACUUM operations performed for each of the above reasons will vary depending on the needs of each database environment. Some tables with heavy updates and deletes need frequent vacuuming to discard obselete and deleted records. 

 

  • VACUUM processes every table and materialized view in the current database that the current user has permission to vacuum by default i.e. without a table_and_columns list specified, .With a list, VACUUM processes only those specified table(s).
  • VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected table. This is a handy combination form for routine maintenance scripts. This option Updates statistics used by the planner to determine the most efficient way to execute a query thus the Optimizer choose right plan including join and indexes.

VACUUM (without FULLsimply reclaims space and makes it available for re-use. This command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained on the table. However, extra space is not returned to the operating system immediately. it's just kept available for re-use within the same table. It also allows us to leverage multiple CPUs in order to process indexes. This feature is known as parallel vacuum. We can use PARALLEL option and specify parallel workers as zero to disable the parallelism.

  • VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an ACCESS EXCLUSIVE lock on each table while it is being processed. 

BTW, “full” vacuum, which can reclaim more space, takes much longer and exclusively locks impacts the performance and availability of the specific table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table like after a huge purge activity on the table.

 

  • VACUUM FREEZE selects aggressive “freezing” of tuples.

Specifying FREEZE with VACUUM is useful in transaction id wrapping and is equivalent to performing VACUUM with the vacuum_freeze_min_age and vacuum_freeze_table_age   parameters set to zero

 Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified.

 

Equivalent Commands in other RDBMS:

  • ·      REORG and UPDATE STATISTICS in Sybase
  • ·      REORG and GATHER STATS in Oracle

 

Sunday 26 June 2022

PostgreSQL Index types and use cases!

Indexes are database objects that can be created for a table to speed access to specific data rows by validating the existence of the data and pointing to the specific pages. Indexes store the values of the key(s) that were named when the index was created, and logical pointers to the data pages.

Although indexes speed data retrieval, they also can slow down data modifications (Each index creates extra work every time you insert, delete, or update a row) since most DML changes to the data also require updating the indexes.

Optimal indexing demands:

·       The workload on the table i.e. READs vs DMLs

·       An understanding of the behavior of queries that access unindexed heap tables, and tables with indexes

·       An understanding of the mix of queries that run on your server

·       An understanding of the PostgreSQL Query optimizer

Using indexes speeds optimizer access to data and reduces the amount of information read and processed from base tables. Whenever possible, the optimizer attempts index-seek-only retrieval to satisfy a query. With index-only retrieval, the database server uses only the data in the indexes to satisfy the query, and does not need to access rows in the table. The optimizer automatically chooses to use the indexes it determines will lead to the best performance.

Index Types

PostgreSQL provides several types of index: B-tree, Hash, GiST, SP-GiST, GIN and BRIN.

1. B-Tree

2. Hash

3. GiST

4. SP-GiST

5. GIN

6. BRIN

Each index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. The other index types are selected by the keyword USING followed by the index type name.

For example, to create an index:

CREATE INDEX name ON table USING [HASH|GIN](column);

 

Hash Indexes

Hash indexes store a 32-bit hash code derived from the value of the indexed column. Hence, such indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the equal operator:

=

To create a hash index, you use the CREATE INDEX statement with the HASH index type in the USING clause as follows:

Syntax:

CREATE INDEX index_name ON table_name USING HASH (indexed_column);

GIN indexes

GIN stands for Generalized Inverted Indexes. It is commonly referred to as GIN.

GIN indexes are most useful when you have multiple values stored in a single column, for example, hstorearray, jsonb, and range types.

BRIN Indexes

BRIN stands for Block Range Indexes. BRIN is much smaller and less costly to maintain in comparison with a B-tree index.

BRIN allows the use of an index on a very large table that would previously be impractical using B-tree without horizontal partitioning. BRIN is often used on a column that has a linear sort order, for example, the created date column of the sales order table.

GiST Indexes

GiST stands for Generalized Search Tree. GiST indexes allow a building of general tree structures. GiST indexes are useful in indexing geometric data types and full-text search.

SP-GiST Indexes

SP-GiST stands for space-partitioned GiST. SP-GiST supports partitioned search trees that facilitate the development of a wide range of different non-balanced data structures. SP-GiST indexes are most useful for data that has a natural clustering element to it and is also not an equally balanced tree, for example, GIS, multimedia, phone routing, and IP routing.

 

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

 

Thursday 23 June 2022

Interesting Behaviour of PostgreSQL character data types!

Every RDBMS supports multiple data types namely numeric, monetary, character, date(time), binary, Boolean and blobs etc. PostgreSQL supports special datatypes like Geometric, Network-Address, UUID, XML, JSON, Text Search types etc.

Out of these 70% of columns are typically character data types. Character data types are strings of ASCII characters. Upper and Lower case alphabetic characters are accepted literally. There are three kinds of character data types as cited:

1.       fixed-length, blank padded - char

2.       variable-length with limit - varchar                       and

3.       variable unlimited length  - text

The storage requirement for a character datatype for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1 byte. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values.

BTW, In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed for n in the data type declaration is less than that. It wouldn't be useful to change this because with multibyte character encodings the number of characters and bytes can be quite different. If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.)

Most importantly interesting behaviour in PostgreSQL is how it handles these character data types.

There is no performance difference among these three data types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

 

Source: postgresql.org

Sunday 18 April 2021

All about Databases and need of Migration - ReHost/RePlatform/ReFactoring/ReArchitecting of Databases!

Today’s emerging data world needs just the Optimal service with minimal manual intervention. Simply to achieve this, Data migration is a necessity. Actually new inventions in technology like Mobile Apps and friendly UIs demands Database Systems to be compatible with new interfaces and features, and to cater this, we need to migrate to new Database Engines. The world is producing and storing more data than ever before. Heterogeneous DBMS assessments and cross platform database migrations to help customers modernize their databases and applications to leverage this amazing capability at enterprise scale is a must now a days.

Necessity is mother of invention and Data Migration is Father of Necessity :)

Most Customers planning data migration projects, perhaps modernizing the existing business processes, to save costs or become more competitive, requires retiring legacy applications and implementing new applications to support the new approach to business. May be they are integrating data from a merger or acquisition. Whatever the reason, data migration is the lynchpin of the larger initiative for the company, is investing strategy, budget, and precious time.

Why lot of databases? what to use?



Based on internet sources, Charles Bachman was the first person to develop the Integrated Data Store (IDS) which was based on network data model for which he was inaugurated with the Turing Award (The most prestigious award in the field of Computer Science the Turing Award, often referred to as the “Nobel Prize of Computing,” carries a $1 million prize, with financial support provided by Google, Inc. It is named for Alan M. Turing, the British mathematician who articulated the mathematical foundation and limits of computing.). The IDS was developed in early 1960’s.

In the late 1960’s, IBM developed the Integrated Management Systems which is the standard database system used till date in many places. It was developed based on the hierarchical database model. In 1970s, Edgar Codd developed the relational database model. Many of the database models we use till today are these model and was considered the standardized database model from then. After a decade (1980’s), IBM developed the Structured Query Language (SQL) as a part of "R project". It was declared as a standard language for the queries by ISO and ANSI. James Gray developed the Transaction Management Systems for processing transactions for which he also was felicitated the Turing Award.

For decades, since DBMS is initiated, the predominant data model that was used for Database Manageent was the relational data model used by all relational databases such as Oracle, Sybase, DB2, SQL Server, MySQL, and PostgreSQL. It wasn’t until the mid to late 2000s that other data models began to gain significant adoption and usage. To differentiate and categorize these new classes of databases and data models, based on the work load and transactions profile besides OLTP, OLAP the term “NoSQL” was coined which is used interchangeably with “nonrelational.”

 

Kinds of DBMS:

OLTP ->          Regular RDBMS strictly comply ACID (atomicity, consistency, isolation, and durability) properties.

Most popular DBMS since 1970 till 2000s. At the time, storage was extremely expensive, so database engineers normalized their databases in order to reduce data duplication (Normalization is a process of avoid Redundancy). As most of us aware of these Databases like Oracle, Sybase, MSSQL, MySQL, PostgreSQL and DB2, simply OLTP is many small transactions in parallel. A large number of short on-line DML transactions (INSERT, UPDATE, DELETE) besides thousands of SELECTs. The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. Most DBMS during until early 2000s were RDBMS and especially OLTP databases only.

 

OLAP ->         All statistical and Analytical Systems especially with ColumnStore.

Redundancy and scale of data needed a different strategy and regular RDBMS is unable to provide the required throughput/response time and Columnar and Key Databases are emerged since 2010 but to the peaks in mid 2010s.

Analytics/Statistical queries seldom touch most columns of the table, but typically a single or few essential columns, but probably all or most of the Rows/Records. Keeping all the values of a particular column together, the Query processing becomes much more efficient than when the data stored in rowstore. When we need a subset of columns, we use indexes for better performance in Rowstore RDBMS but still it needs to fetch data from many rows, whereas all required entities stored together in a columnar database, results 100s of times faster results.

-        Amazon RedShift, Sybase IQ and SAP HANA are most popular column store RDBMS.

 

Open source RDBMS

Open source databases allows users to create a DBMS based on their unique requirements and business needs besides get rid of huge licensing costs of regular RDBMS. It is free and can also be shared. The source code can be modified to match any user preference. Open source databases address the need to analyze data from a growing number of new applications at lower cost.

-        MySQL remains on top as the #1 free and open source database, representing over 30% of open source database use.  

-        PostgreSQL came in 2nd place with 13.4% representation from open source database users, closely followed by MongoDB at 12.2% in 3rd place.

 

NoSQL

NoSQL databases (aka "not only SQL") are non tabular, and store data differently than relational tables. NoSQL databases come in a variety of types based on their data model. The main types are document, key-value, wide-column, and graph. NoSQL databases allow database developers to store huge amounts of unstructured data, giving them a lot of flexibility, as storage costs rapidly decreased and software engineers cost increased, the amount of data to store and query increased to a great extent besides data entities/elements came in all shapes and sizes i.e. structured, semi-structured, and polymorphic plus defining the schema in advance became nearly impossible, NoSQL became is the optimal solution.


Type of Database
Popular Engines
Key–value cache Apache Ignite, Couchbase, Coherence, eXtreme Scale, Hazelcast, Infinispan, Memcached, Redis, Velocity
Key–value store Azure Cosmos DB, ArangoDB, Aerospike, Couchbase, Redis
Key–value store (eventually consistent) Azure Cosmos DB, Oracle NoSQL Database, Dynamo, Riak, Voldemort
Key–value store (ordered) FoundationDB, InfinityDB, LMDB, MemcacheDB
Tuple store Apache River, GigaSpaces
Object database Objectivity/DB, Perst, ZopeDB
Document store Azure Cosmos DB, ArangoDB, BaseX, Clusterpoint, Couchbase, CouchDB, DocumentDB, eXist-db, IBM Domino, MarkLogic, MongoDB, Qizx, RethinkDB, Elasticsearch
Wide Column Store Azure Cosmos DB, Amazon DynamoDB, Bigtable, Cassandra, Google Cloud Datastore, HBase, Hypertable, Scylla
Native multi-model database ArangoDB, Azure Cosmos DB, OrientDB, MarkLogic

 

  • Document databases store data in documents similar to JSON (JavaScript Object Notation) objects. Each document contains pairs of fields and values. According to DB-engines rankings/statistics, Relational Databases Oracle, MySQL, MS SQL and PostgreSQL tops the charts and MongoDB is consistently ranked in the 5th place as the world’s most popular NoSQL.
  • Key-value databases are a simpler type of database where each item contains keys and values. Redis and DynanoDB are popular key-value databases. Redis is the only key-value database in top 10 consistently.
  • Wide-column stores store data in tables, rows, and dynamic columns. Wide-column stores provide a lot of flexibility over relational databases because each row is not required to have the same columns. Wide-column stores are commonly used for storing Internet of Things data and user profile data. Cassandra and HBase are two of the most popular wide-column stores.
  • Graph databases store data in nodes and edges. Nodes typically store information about people, places, and things while edges store information about the relationships between the nodes. Graph databases excel in use cases where you need to traverse relationships to look for patterns such as social networks, fraud detection, and recommendation engines. Neo4j and JanusGraph are examples of graph databases.

 

Why Database Migrations?

 

As a DBA and guardians of data, we are constantly faced with the challenge of moving data from one platform to another for a multitude of reasons.  

For most Customers and businesses data is become a gold mine for their critical analytics and processes and the aggressive scale up in size and usage became a crux to manage/maintain without compromising the performance. As the type of data stores, requirements, workloads and volume of the data continue to increase, an innovative solution is necessitated.

As a result many Customers are moving their databases into CLOUD and are implementing DBaaS model to rapidly deploy their databases into the cloud.

A few vital advantages of databases on cloud are:

  1. Optimised Costs. As cloud computing is now more cost effective it means that your IT overheads will be reduced significantly, optimising costs as it is more of a pay as you go cost structure; maintenance, software, licensing etc cost are all picked up by the cloud provider meaning your data will be in good hand and at a reduced rate.
  2. For clients who have data that is regularly accessed, the cloud allows you to scale the database up or down in high usage periods to deal with the extra demand.
  3. Faster time to market. Thanks to the Cloud you’ll be able to swiftly move your new/ existing applications straight to market; whereas offline this operation could take days or even weeks.
  4. Due to datacentres now being available within the Regions, organisations that must abide by certain legislations and store their data in the country of origin now have the availability to do so. Higher availability coupled with constant database back-up and recovery allows for geo-regional, secure data storage.
  5. Most cloud providers offer several 9s of consistency and reliability when it comes to data in the cloud. Meaning that their SLA’s are met almost every time and customers can access their data quickly and with confidence that it will be available when needed.

 

BTW, Along with migration of Data to a better, faster and optimal system, the ability to validate and clean existing data can be an important feature of a system. This is independent of migration. There are often mechanisms to modify data which are outside the control of the system. This can cause data to become invalid. Other data problems result from bugs in the application. Running the validation routines periodically can help identify the problem and allow the data to be cleaned before it is time for migration. As has been noted cleaning the data early can make the migration easier.

 

Let’s discuss various Database Migration tools/utilities and Replication technologies in the upcoming blogpost, till then, Have a safe, pleasant and productive Days ahead!

 

Thanks much for many authors and sites for the information I have read and used here.

Tuesday 7 July 2020

Locks!! Table Level Locks!! How to Analyze and Optimize ??

Sybase/SAP ASE Locking

Most of Sybase databases are designed for either OLTP or mixed workloads and especially for multi-user, multi-transactions at any point of time. Adaptive Server protects the tables, data pages, or data rows currently used by active transactions by locking them for integrity and consistency of Data. Locking is a concurrency control mechanism: it ensures the consistency of data within and across transactions. Locking is needed in a multiuser environment, since several users may be working with the same data at the same time.

Locking affects performance when one process holds locks that prevent another process from accessing needed data. The process that is blocked by the lock sleeps until the lock is released. 

This is called lock contention. BTW, Locking is handled automatically by Adaptive Server, with options that can be set at the session and query level by the user. 

Based on the data and rows/pages being processed the locking granularity changes. By locking at higher levels of granularity, the amount of work required to obtain and manage locks is reduced. If a query needs to read or update many rows in a table:
  • It can acquire just one table-level lock
  • It can acquire a lock for each page that contained one of the required rows
  • It can acquire a lock on each row

Less overall work is required to use a table-level lock, but large-scale locks can degrade performance and availability of data to other concurrent processes, by making other users wait until locks are released. Decreasing the lock size makes more of the data accessible to other users. However, finer granularity locks can also degrade performance, since more work is necessary to maintain and coordinate the increased number of locks. To achieve optimum performance, a locking scheme must balance the needs of concurrency and overhead, entirely depends on the work-load or transactions type.

Adaptive Server provides these locking schemes:
  • Allpages locking, which locks both datapages and index pages
  • Datapages locking, which locks only the data pages
  • Datarows locking, which locks only the data rows
For each locking scheme, Adaptive Server can choose to lock the entire table for queries that acquire many page or row locks, or can lock only the affected pages or rows.

Adaptive Server has two levels of locking:
·       For tables that use allpages locking or datapages locking, either page locks or table locks.
·       For tables that use datarows locking, either row locks or table locks

Page or row locks are less restrictive (or smaller) than table locks. A page lock locks all the rows on data page or an index page; a table lock locks an entire table. A row lock locks only a single row on a page. Adaptive Server uses page or row locks whenever possible to reduce contention and to improve concurrency.

Every row or page in a table is also protected with a lock. These locks only come in two flavours:
·       share
·       exclusive

Many transactions can hold a share lock concurrently, but only one transaction can hold an exclusive lock.

Adaptive Server uses a table lock to provide more efficient locking when an entire table or a large number of pages or rows will be accessed by a statement. Locking strategy is directly tied to the query plan, so the query plan can be as important for its locking strategies as for its I/O implications.
The following describes the types of table locks.

·       Intent lock
An intent lock indicates that page-level or row-level locks are currently held on a table. Adaptive Server applies an intent table lock with each shared or exclusive page or row lock, so an intent lock can be either an exclusive lock or a shared lock. Setting an intent lock prevents other transactions from subsequently acquiring conflicting table-level locks on the table that contains that locked page. An intent lock is held as long as page or row locks are in effect for the transaction.

·       Shared lock
This lock is similar to a shared page or lock, except that it affects the entire table. For example, Adaptive Server applies a shared table lock for a select command with a holdlock clause if the command does not use an index. A create nonclustered index command also acquires a shared table lock.

·       Exclusive lock
This lock is similar to an exclusive page or row lock, except it affects the entire table. For example, Adaptive Server applies an exclusive table lock during a create clustered index command. update and delete statements require exclusive table locks if their search arguments do not reference indexed columns of the object.

How to Analyze and Optimize?

Use sp_object_stats or sp_sysmon to determine the overall server level lock contention, and then use it to tune to reduce lock contention of the system. We also need to monitor these reports whenever resources changed or locking configuration changed for the Server.

Viewing current locks on the Server

To get a report on the locks currently being held on Adaptive Server, use sp_lock:
sp_lock

fid spid loid locktype         table_id   page  row dbname   context
--- ---- ---- ---------------- ---------- ----- --- -------- ----------------
  0  15   30  Ex_intent         208003772     0   0 sales    Fam dur
  0  15   30  Ex_page           208003772  2400   0 sales    Fam dur, Ind pg
  0  15   30  Ex_page           208003772  2404   0 sales    Fam dur, Ind pg
  0  15   30  Ex_page-blk       208003772   946   0 sales    Fam dur
  0  30   60  Ex_intent         208003772     0   0 sales    Fam dur
  0  30   60  Ex_page           208003772   997   0 sales    Fam dur
  0  30   60  Ex_page           208003772  2405   0 sales    Fam dur, Ind pg
  0  30   60  Ex_page           208003772  2406   0 sales    Fam dur, Ind pg
  0  35   70  Sh_intent          16003088     0   0 sales    Fam dur
  0  35   70  Sh_page            16003088  1096   0 sales    Fam dur, Inf key
  0  35   70  Sh_page            16003088  3102   0 sales    Fam dur, Range
  0  35   70  Sh_page            16003088  3604   0 sales    Fam dur, Range
  0  49   98  Sh_intent         464004684     0   0 master   Fam dur
  0  50  100  Ex_intent         176003658     0   0 stock    Fam dur
  0  50  100  Ex_row            176003658 36773   8 stock    Fam dur
  0  50  100  Ex_intent         208003772     0   0 stock    Fam dur
  0  50  100  Ex_row            208003772 70483   1 stock    Fam dur
  0  50  100  Ex_row            208003772 70483   2 stock    Fam dur
  0  50  100  Ex_row            208003772 70483   9 stock    Fam dur
32  13   64  Sh_page           240003886 17264   0 stock
32  16   64  Sh_page           240003886  4376   0 stock
32  19   64  Sh_page           240003886 22367   0 stock
32  32   64  Sh_intent          16003088     0   0 stock    Fam dur
32  32   64  Sh_intent          48003202     0   0 stock    Fam dur
32  32   64  Sh_intent         240003886     0   0 stock    Fam dur


This example shows the lock status of serial processes and two parallel processes:

·       spid 15 hold an exclusive intent lock on a table, one data page lock, and two index page locks. A “blk” suffix indicates that this process is blocking another process that needs to acquire a lock; spid 15 is blocking another process. As soon as the blocking process completes, the other processes move forward.

·       spid 30 holds an exclusive intent lock on a table, one lock on a data page, and two locks on index pages.

·       spid 35 is performing a range query at isolation level 3. It holds range locks on several pages and an infinity key lock.

·       spid 49 is the task that ran sp_lock; it holds a shared intent lock on the spt_values table in master while it runs.

·       spid 50 holds intent locks on two tables, and several row locks.

·       fid 32 shows several spids holding locks: the parent process (spid 32) holds shared intent locks on 7 tables, while the worker processes hold shared page locks on one of the tables.

The lock type column indicates not only whether the lock is a shared lock (“Sh” prefix), an exclusive lock (“Ex” prefix), or an “Update” lock, but also whether it is held on a table (“table” or “intent”) or on a “page” or “row.”

A “demand” suffix indicates that the process will acquire an exclusive lock as soon as all current shared locks are released. 

sp_familylock displays the locks held by a family. This examples shows that the coordinating process (fid 51, spid 51) holds a shared intent lock on each of four tables and a worker process holds a shared page lock:

sp_familylock 51

fid spid loid locktype         table_id   page  row dbname   context
--- ---- ---- ---------------- ---------- ----- --- -------- ----------------
51  23  102  Sh_page           208003772   945   0 sales
51  51  102  Sh_intent          16003088     0   0 sales    Fam dur
51  51  102  Sh_intent          48003202     0   0 sales    Fam dur
51  51  102  Sh_intent         176003658     0   0 sales    Fam dur
51  51  102  Sh_intent         208003772     0   0 sales    Fam dur

You can also specify two IDs for sp_familylock. ( sp_familylock 23 also gives the above set)


Getting information about blocked processes

sp_who reports on system processes. If a user’s command is being blocked by locks held by another task or worker process, the status column shows “lock sleep” to indicate that this task or worker process is waiting for an existing lock to be released.
The blk_spid or block_xloid column shows the process ID of the task or transaction holding the lock or locks.
You can add a user name parameter to get sp_who information about a particular Adaptive Server user. If you do not provide a user name, sp_who reports on all processes in Adaptive Server. 


Some options to improve locks performance: 

These locking guidelines can help reduce lock contention and speed performance:

·       Use the lowest level of locking required by each application. Use isolation level 2 or 3 only when necessary.
Updates by other transactions may be delayed until a transaction using isolation level 3 releases any of its shared locks at the end of the transaction.
Use isolation level 3 only when nonrepeatable reads or phantoms may interfere with your desired results.
If only a few queries require level 3, use the holdlock keyword or at isolation serializing clause in those queries instead of using set transaction isolation level 3 for the entire transaction.
If most queries in the transaction require level 3, use set transaction isolation level 3, but use noholdlock or at isolation read committed in the remaining queries that can execute at isolation level 1.
·       If you need to perform mass inserts, updates, or deletes on active tables, you can reduce blocking by performing the operation inside a stored procedure using a cursor, with frequent commits.
·       If your application needs to return a row, provide for user interaction, and then update the row, consider using timestamps and the tsequal function rather than holdlock.
·       If you are using third-party software, check the locking model in applications carefully for concurrency problems.

Also, other tuning efforts can help reduce lock contention. For example, if a process holds locks on a page, and must perform a physical I/O to read an additional page, it holds the lock much longer than it would have if the additional page had already been in cache.

Better cache utilization or using large I/O can reduce lock contention in this case. Other tuning efforts that can pay off in reduced lock contention are improved indexing and good distribution of physical I/O across disks.

To help reduce lock contention between update and select queries:

·       Use datarows or datapages locking for tables with lock contention due to updates and selects.
·       If tables have more than 32 columns, make the first 32 columns the columns that are most frequently used as search arguments and in other query clauses.
·       Select only needed columns. Avoid using select * when all columns are not needed by the application.
·       Use any available predicates for select queries. When a table uses datapages locking, the information about updated columns is kept for the entire page, so that if a transaction updates some columns in one row, and other columns in another row on the same page, any select query that needs to access that page must avoid using any of the updated columns.

Finally, an Index scan/seek reduces table accessing time or locking period so always check for table scans in Query plans especially for Queries with joins.