DEV Community

Scale
Scale

Posted on

🔄 Understanding Transactions and Logging in GBase Database

#gbase #database

When you execute SQL in a database, things don’t just “happen” instantly behind the scenes.

Every operation—whether it’s:

  • INSERT
  • UPDATE
  • DELETE

…is carefully tracked and recorded.

In GBase database, this is handled through transactions and logging mechanisms, which ensure:

✅ Data consistency
✅ Crash recovery
✅ Reliable execution

🧠 What is a Transaction?

A transaction is a unit of work that must follow the ACID principles:

  • Atomicity → All or nothing
  • Consistency → Valid state maintained
  • Isolation → No interference between operations
  • Durability → Changes are permanently stored

✏️ Example: Data Update in GBase 

UPDATE t_employee 
SET f_employeename = 'employee_updated' 
WHERE f_employeeid = 11;
Enter fullscreen mode Exit fullscreen mode

After execution:

  • The row is updated
  • The change is recorded in logs
  • The transaction is committed

👉 GBase confirms updates with row count feedback ([GBase][1])

🧱 How GBase Logging Works

GBase uses two types of logs:

1. Physical Logs

  • Record low-level storage changes
  • Track page-level modifications

2. Logical Logs

  • Record SQL-level operations
  • Track transactions like INSERT/UPDATE/DELETE

Example command to view logs:

onstat -l
Enter fullscreen mode Exit fullscreen mode

This shows:

  • Log usage
  • Log sequence numbers
  • Space utilization

📊 Example: Log Output

From GBase:

Logical Logging:
numrecs    Log Space used
82067      12130972
Enter fullscreen mode Exit fullscreen mode

This indicates:

  • Number of records written to logs
  • Space consumed by transactions ([GBase][1])

🔄 Log Switching (Important Concept)

As transactions increase, logs fill up.

GBase automatically performs log switching:

  • Old log → archived or reused
  • New log → becomes active

After switching:

👉 Log ID changes (e.g., 18 → 19) ([GBase][1])

⚠️ Why Logging Matters

1. Crash Recovery

If the database crashes:

  • Logs are replayed
  • Data is restored to a consistent state

2. Transaction Rollback

If something fails:

ROLLBACK;
Enter fullscreen mode Exit fullscreen mode

GBase uses logs to undo changes.

3. Data Consistency

Logs ensure:

  • No partial updates
  • No corrupted state

🛠️ Monitoring Transactions in Real Time

Use onstat tools to inspect activity:

View Log Status 

onstat -l
Enter fullscreen mode Exit fullscreen mode

View System Activity 

onstat -p
Enter fullscreen mode Exit fullscreen mode

View Sessions 

onstat -g ses
Enter fullscreen mode Exit fullscreen mode

These commands help track:

  • Active transactions
  • Resource usage
  • System performance ([GBase][2])

🔬 Behind the Scenes: What Happens on UPDATE

When you run:

UPDATE users SET age = 30 WHERE id = 1;
Enter fullscreen mode Exit fullscreen mode

Internally:

  1. Transaction begins
  2. Old data is recorded in logs
  3. New data is written
  4. Logs are updated
  5. Transaction commits

⚡ Performance Considerations

Logging is essential—but it has cost.

Potential Issues:

  • Heavy log usage during large updates
  • Disk I/O pressure
  • Log space exhaustion

Optimization Tips:

  • Batch updates instead of row-by-row
  • Monitor log usage regularly
  • Allocate sufficient log space

🏢 Real-World Scenario

Case: Bulk Data Update 

UPDATE orders SET status = 'processed';
Enter fullscreen mode Exit fullscreen mode

Impact:

  • Thousands of log entries
  • Increased disk writes
  • Possible performance slowdown

👉 Solution:

  • Run in batches
  • Monitor logs with onstat -l

🔐 Safety Best Practices

✅ Always Use Transactions 

BEGIN;
UPDATE ...
COMMIT;
Enter fullscreen mode Exit fullscreen mode

✅ Monitor Log Usage 

onstat -l
Enter fullscreen mode Exit fullscreen mode

✅ Plan Log Capacity

Ensure logs don’t fill up during peak workloads.

🚀 Final Thoughts

Transactions and logging are the foundation of database reliability in GBase.

They ensure:

  • Every change is tracked
  • Every failure can be recovered
  • Every operation is consistent

Top comments (0)