Open Source RDBMS - Seamless, Scalable, Stable and Free

English | Login |Register

CUBRID Click Counter

The scope of this tutorial is to show you how to use one of the most special features implemented in CUBRID – Click Counter.

Problem Statement

For instance, assume you have a bulletin-board system, or a blogging system, or a forum web site, or, simply put, a web application which has hundreds of thousands of articles and tens of thousands of visitors, i.e. a high traffic web site. And what you want to do is to keep track of how many times a certain article on the web site was viewed by users; this is obviously a common and most frequent request in such systems.

In this case, once a user clicks on an article title, the system has to display the contents of this article as well as increment the table “counter” field, let's say “read_count”, by 1 (registering that one more user has viewed the current article). In order to perform these operations, on the back-end of the system, the client application usually makes two separate requests to the database server.

Here is the pseudo-code of these two requests:

1. SELECT article FROM article_table WHERE article_id = 130,987
2. UPDATE article_table SET read_count = read_count + 1 WHERE article_id = 130,987

Now what is the problem in these requests…?

The problem is “hidden” in the second UPDATE statement. The UPDATE query does generate a long-lasting and expensive lock of the working record. It usually takes about 5 to 10 ms to update the record. And what if the current record is a “hot-spot”? Then it will generate a considerable delay if thousands of concurrent visitors click the same article at the same time.

CUBRID’s Solution

So how does CUBRID resolve this issue…?

In version R2.1 CUBRID introduced a unique feature called Click Counter. In order to perform the above mentioned task, CUBRID does not require the application client to make two separate requests. Instead, CUBRID has a build-in query-functions INCR() and DECR() - which almost do not lock the records:

1. SELECT article, INCR(read_count) FROM  article_table WHERE article_id = 130,987

Once the above SELECT query is executed, CUBRID increments the mentioned table field by 1 “on the fly”.

INCR & DECR functions

Let’s take a closer look now at the CUBRID’s INCR() and DECR() functions, which are the core of the Click Counter feature.

The INCR function increments the column's value given as a parameter for a SELECT statement by 1. And the DECR function decrements the value of the column by 1.

Note: The INCR/DECR functions increments the column value specified as an argument.

Constraint: Only integer type numbers can be used as arguments.

INCR function behavior rules:

  • If the value of the column is NULL, the INCR function returns NULL. That is, a value must be valid in order to be incremented by the INCR function.
  • If an INCR function is specified in the SELECT statement, the COUNTER value is incremented by 1 and the query result is displayed with the values before the increment.
  • Furthermore, the INCR function does not increment the value of the tuple affected by the query process but rather the one affected by the final result.

The DECR function behaves in the same way as INCR, with the only difference that it decrements instead of incrementing.

Function syntax:

SELECT [ qualifier ] select_expression
[ { TO | INTO } variable [ {, variable }...; ] ]
...;

select_expression:
	*
	table_name. *
	[expression | counter_expression] [ {, expression |counter_expression}...]

counter_expression:
	INCR(path_expression)

DECR() has the same syntax.

WITH INCREMENT FOR

One less known thing is that the same result can be obtained using instead of INCR the option WITH INCREMENT FOR.

For example, the following two SELECT are equivalent:

SELECT article, INCR(read_count)
FROM article_table
WHERE article_id = 130,987;

SELECT article, read_count
FROM article_table
WHERE article_id = 130,987
WITH INCREMENT FOR read_count;

Same goes for DECR/DECREMENT – the following two SELECTs are also equivalent:

SELECT article, DECR(read_count)
FROM article_table
WHERE article_id = 130,987;

SELECT article, read_count
FROM article_table
WHERE article_id = 130,987
WITH DECREMENT FOR read_count;

Transactions impact

The INCR/DECR functions execute independent of user-defined transactions and are applied automatically to the database by the top operation internally used in the system, apart from the transaction's COMMIT/ROLLBACK.

Things to know:

  • When multiple INCR/DECR functions are specified in a single SELECT statement, the failure of any of the INCR/DECR functions leads to the failure of all of them.
  • The INCR/DECR functions apply only to top-level SELECT statements. Sub-SELECT statements such as INSERT ... SELECT ... statement and UPDATE table SET col = SELECT ... statement are not supported.

The following is an example where the INCR function is not allowed, because if the SELECT statement with INCR/DECR function(s) returns more than one row as a result, it is treated as an error. The final result must have only one row to be considered valid:

SELECT b.content, INCR(b.read_count) FROM (SELECT * FROM board WHERE id = 1) AS b

Remember: When the INCR function is called, the value to be returned will be the current value, while the value to be stored will be the current value + 1.

Execute the following statement to retrieve the value that will be stored as the result of calling INCR:

SELECT content, INCR(read_count) + 1 FROM board WHERE id = 1;

If the defined maximum value of the domain is exceeded, the INCR function initializes the column value to 0. Likewise, the column value is also initialized to 0 when the DECR function applies to the minimum value.

Note: Data inconsistency can occur because the INCR/DECR functions are executed regardless of UPDATE trigger. The example below shows the database inconsistency in that situation:

CREATE TRIGGER event_tr BEFORE UPDATE ON event EXECUTE REJECT;
SELECT INCR(players) FROM event WHERE gender='M';

Example:

Suppose that the following three records were inserted into the 'board' table:

CREATE TABLE board (
id  INT,
title  VARCHAR(100), 
content  VARCHAR(4000),
read_count  INT
);
INSERT INTO board VALUES (1, 'aaa', 'text...', 0);
INSERT INTO board VALUES (2, 'bbb', 'text...', 0);
INSERT INTO board VALUES (3, 'ccc', 'text...', 0);

The following is an example of incrementing the value of the 'read_count' column in a data whose 'id' value is 1:

SELECT content, INCR(read_count) FROM board WHERE id = 1;
=== <Result of SELECT Command in Line 1> ===
content					read_count
===================================
'text...'       0

In the example, the column value becomes read_count + 1 as a result of the INCR function in the SELECT statement. You can check the result using the following SELECT statement:

SELECT content, read_count FROM board WHERE id = 1;
=== <Result of SELECT Command in Line 1> ===
content                read_count
===================================
'text...'      1

More examples

First of all, please make sure AUTOCOMMIT is turned OFF.

And let’s create a table and a record:

CREATE TABLE cc (
id INT
);
INSERT INTO cc VALUES(1);
Current id value in the table cc: 1
SQL executed: SELECT INCR(i) FROM cc;
Returned value from SQL: 1
id value after executing the SQL: 2
Current id value in the table cc: 2
SQL executed: SELECT DECR(i) FROM cc;
Returned value from SQL: 2
id value after executing the SQL: 1
Current id value in the table cc: 1
SQL executed: SELECT INCR(id) FROM cc;

ROLLBACK;

SELECT id FROM cc;

Returned value from last SELECT: 1
id value after executing the SQLs: 2
Current id value in the table cc: 2
SQL executed: SELECT DECR(id) FROM cc;

ROLLBACK;

SELECT id FROM cc;

Returned value from last SELECT: 2
id value after executing the SQLs: 1
Current id value in the table cc: 1
SQL executed: SELECT INCR(id), DECR(id) FROM cc;

ROLLBACK;

SELECT id FROM cc;

Returned value from last SELECT: 1
id value after executing the SQLs: 1

Links & Resources

This concludes the CUBRID Click Counter startup tutorial. Please let us know your feedback and remember to periodically check the CUBRID web site for other tutorials and resources.

See also

CUBRID Query Tuning Techniques

This article has been written by one of the CUBRID core developers to help users improve their application performance by understanding h...

Obtaining Database Information in CUBRID

This guide explains how to retrieve database information from CUBRID. This includes: table, index, and column names, if certain columns are indexes ...

How to Concatenate Column Values from Different Rows

Sometimes you want to concatenate all column values from differenct rows returned by your SELECT statement in one value. For example, consider the c...




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: